Increasing Inserts and Updates Speed

Previous  Top  Next

The fastest way of inserting and updating

The fastest way of batch inserting / updating / deleting is a buffered transaction. We recommend to call TABSDatabase.StartTransaction before bulk inserts and TABSDatabase.Commit(False) after the end of the batch operation. The use of transaction can significantly increase performance of the batch operation.

The following example illustrates the fastest way of inserting 2000 records:

// insert data by portions of 2000 records

ABSDatabase1.StartTransaction;

for i:=1 to 2000 do

with ABSTable1 do

begin

Insert;

FieldByName('Name').AsString := 'John';

Post;

end;

ABSDatabase1.Commit(False);

How to speed up an UPDATE query

If you are using several subqueries in an UPDATE query, you could try to transfomr your query like in the example below:

Before optimization:

UPDATE Orders SET ShipToAddr1=(SELECT Addr1 FROM Customer WHERE CustNo=Orders.CustNo), ShipToAddr2= (SELECT Addr2 FROM Customer WHERE CustNo=Orders.CustNo) WHERE CustNo IN (1221, 2156)

After optimization:

UPDATE Orders SET (ShipToAddr1,ShipToAddr2) = (SELECT Addr1, Addr2 FROM Customer WHERE CustNo=Orders.CustNo) WHERE CustNo IN (1221, 2156)