Delphi Database, Delphi Components from ComponentAce
Products Download Order Contact us
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)  

        © 2003 - 2017 ComponentAce  | .net zip component | barcode for .net | delphi zip component | delphi database Dec 16, 2017