How to improve data insert/update performance?

Question

I need to improve the performance of data loading. The current algorythm makes a full select from a table:

select Field1, Field2,...,FieldN from Table1 order by FieldM

The new data is read from a text file (say, textfile line per datatable row). The table has a primary key, containing two fields. For each line of a textfile it locates the necessary row by these two fields (i.e. the primary key).

query.Locate('Field1;Field2',VarArrayOf([Value1,Value2]),[]);

If Locate returns True, it edits the row, otherwise it appends a new one.

So, as far as the table consists of about 200000 rows, each Locate operation takes certain amount of time...so it manages to update about 5-6 rows per second.

What things should I consider to improve it?

Probably replace locating through this great select with separate queries?

Answer 1

DON'T use Locate(). If you use locate() then Delphi searches row on the client side just scanning row set from your query it takes a LOT of time.

If you have access to MSSQL to create stored procedures then create following procedure and just run it for each line from your TEXT file without any conditions (Use TAdoStoredProc.ExecProc in Delphi). So in this case your don't need first select and Locate procedure. It updates record if Filed1 and Field2 are found and insert if don't.

CREATE PROCEDURE dbo.update_table1 
@Field1 int, --key1
@Field2 int, --key2
@Field3 int, -- data fileds
@Field4 int

AS

SET NOCOUNT ON
update table1 set Field3=@Field3,Field4=@Field4 
        where Field1=@Field1 and Field2=@Field2;
IF(@@Rowcount=0)
BEGIN
     insert into table1(Field1,Field2,Field3,Field4) 
                values (@Field1,@Field2,@Field3,@Field4);
END
GO

Here is Delphi code to invoke this stored procedure with ADO:

......
var 
     ADOStoredP: TADOStoredProc;

  ......
begin

........
    ADOStoredP:=TADOStoredProc.Create(nil);
   try
      ADOStoredP.Connection:=DataMod.SQL_ADOConnection; //Your ADO Connection instance here
      ADOStoredP.ProcedureName:='Update_table1';
      ADOStoredP.Parameters.CreateParameter('@Field1', ftInteger, pdInput, 0, 0);
      ADOStoredP.Parameters.CreateParameter('@Field2', ftInteger, pdInput, 0, 0);
      ADOStoredP.Parameters.CreateParameter('@Field3', ftInteger, pdInput, 0, 0);
      ADOStoredP.Parameters.CreateParameter('@Field4', ftInteger, pdInput, 0, 0);

      While () -- Your text file loop here
      begin

      ADOStoredP.Parameters.ParamByName('@Field1').Value:=Field1 value from text file here;
      ADOStoredP.Parameters.ParamByName('@Field2').Value:=Field2 value from text file here;
      ADOStoredP.Parameters.ParamByName('@Field3').Value:=Field3 value from text file here;
      ADOStoredP.Parameters.ParamByName('@Field4').Value:=Field4 value from text file here;

      ADOStoredP.ExecProc;

      end

    finally
      if Assigned(ADOStoredP) then
        begin
         ADOStoredP.Free;
        end;
    end;

........
end;