Board index » delphi » Local Interbase slows down when adding records

Local Interbase slows down when adding records

Quote
Chua Chee Wee wrote:

> Anyone got any idea why Local Interbase for Win95/NT slows down
> as I add more and more records into a local database of .GDB type?
> This file has ONLY ONE table inside. Anything else inside the file is added by Interbase
> automatically.

> Currently, I have fields declared as
> Field1 VARCHAR(50)
> Field 2 VARCHAR(150)
> Field3 SmallInt
> Field4 VARCHAR(7)
> Field5 Integer
> Field6 Integer NOT NULL UNIQUE

> When the database is empty, it could add about 3 or 4 records per second or maybe more.
> When records start to go near about 3000 or 6000(forgot where's the actual point), the
> database slows down to like 3 or 4 seconds per record!!! and as it goes on, even worst!
> Something like 10 secs per record!

> I am thinking of adding about 300,000(about 20MB or 40MB) records programmatically in one
> go, thinking it could finish in 3 or 4 hours(or maybe 8 hours). Now it looks dead
> impossible. Any ideas?  I'm willing to suspend the index and build one only when the
> records are added totally.  But then Interbase doesn't allow me to erase a primary index.
> Darn. Something about a  trigger. What can I do? If I wanna cache the database so that
> it'll write say 10 records  when a CommitCache or something is done, how do I go about
> it? Speed is of the utmost  essence in creating this database. How can I make adding
> records to the database fast(or  faster), as the database grows in size?

> Help!!!

I can extract 1400 records (roughly 60 charactors total per record) from
an external text table and insert into a Delphi table (without deleting
or disabling the indexes) in only a few seconds, so something is wrong.

I have found when running on NT 3.51, the local interbase server runs
much faster using TCP/IP protocol, and runs very much faster when it is
ran from a dos partition.  You may try moving the database to a dos
partition, thou I cannot remember if that helped much.

Also, what is the trigger doing. Is it complex?  I also have a trigger on
the above insert, so a simple trigger should not affect anything.

Let me know what you found.

I would be interested in hearing how the performance differs if you
create your database so the Unique Not Null field, field 6 is the first
field in the table.

Try the following advice extracted from
http://www.borland.com/techsupport/interbase/tech/performance.html

"But to minimize the performance hit during INSERT, consider temporarily
disabling indices
      during high-volume INSERTs. This will "turn off" the indices,
making them unavailable to
      help speed up queries, but also making them not be updated by data
INSERTs. Then
      re-enable the indices after INSERTing data. This will update and
rebalance the indices
      once for all the inserted data.

      Periodically, rebuild indices by turning them off and on (ALTER
INDEX name INACTIVE;
      followed by ALTER INDEX name ACTIVE;). Recalculate the index
selectivity (SET STATISTICS
      INDEX name;) if a change to the table affects the average
distribution of data values."

 

Re:Local Interbase slows down when adding records


Hi! I dont know is anyone else has answered to your Q yet.
If y must write the records from within delphi do it as transactions for a
number of records at the time.
also remeber to let windows processmessages
.
If you can use isql to do insert from a sequential file(I think there's a
example somewhere in interbase sql help.
Finaly how is your primary key constructed. Is it varying from left to
right?
a index like aaaa1, aaaa2, aaaa3 etc... does'nt work well. the nodethree is
likely to be deep.
Hope this help's some.
Per H.
Chua Chee Wee <chu...@singnet.com.sg> wrote in article
<3263fb94.1872...@news.singnet.com.sg>...

Quote
> Anyone got any idea why Local Interbase for Win95/NT slows down

Re:Local Interbase slows down when adding records


Quote
Chua Chee Wee wrote:

> Anyone got any idea why Local Interbase for Win95/NT slows down
> as I add more and more records into a local database of .GDB type?
> This file has ONLY ONE table inside. Anything else inside the file is added by Interbase
> automatically.

> Currently, I have fields declared as
> Field1 VARCHAR(50)
> Field 2 VARCHAR(150)
> Field3 SmallInt
> Field4 VARCHAR(7)
> Field5 Integer
> Field6 Integer NOT NULL UNIQUE

Try playing around with the database page size in ISQL.  You may
want to try making the tuple size a power of 2 such as 256 bytes.
You can even go so far as making your VARCHAR's power of 2 such
as 64 bytes.  Also, if you're going to do bulk loads, it's best to
disable all indexes  and build them after the load.  Choose some commit
threshold such as commit every 1,000 records.

You don't describe the trigger, so it's difficult to know if it's
a performance problem.  And finally, how would you characterize
the domain for your unique index?  Is it a sequential counter?

Matt
==================================================
Matt Houseman                   Arbor Software
mhouse...@arborsoft.com           408.541.4408
Senior Software Engineer        Data Storage Group

"...honest to the point of recklessness,
    self-centered to the extreme..."
==================================================

Re:Local Interbase slows down when adding records


Quote
Chua Chee Wee wrote:

> Anyone got any idea why Local Interbase for Win95/NT slows down
> as I add more and more records into a local database of .GDB type?

I found that turning of the screen saver speeded up one of my programs
by 5x (not that I am saying this is the problem you have).  I was rather
shocked at the result.

Oliver

Re:Local Interbase slows down when adding records


I have this problem too. I Use TQuery with Live Results to simulate a
insert SQL declaration, like:

var Q: TQuery;
begin
   Q := TQuery.Create(Self);
   try
      with Q do
      begin
         SQL.Add('Select Field1, Field2, Field3, Field4, Field5,
Field6');
         SQL.Add('From TableX';
         SQL.Add('Where Field1 = "ZYXSSASQEIR"); { force to none results }
         RequestLive := True;
         Open;
         Insert;
         FieldByName('Field1').AsString := 'TEST';
         FieldByName('Field2').AsString := 'TEST';
         .
         .
         Post;
      end;
   finally
      Q.free;
   end;
end;    

To perform more quickly, mantains Q opened and executes multiples
Insert/Post events.

Re:Local Interbase slows down when adding records


chu...@singnet.com.sg,Usenet disse :
<<

Quote
>Field6 Integer NOT NULL UNIQUE

Really, you're creating a secondary index with the UNIQUE in the
declaration, and, when a new record is added this index is checked
against duplicate value. To improve performance, I suggest you remove
the UNIQUE and use  Cached Updates.

Walter

--
+---------------------------------------
| wal...@wim.com.br
| WIM Informtica Ltda. - www.wim.com.br
+---------------------------------------

Other Threads