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?

Gelatinate

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;

Saldivar

This solution reduced the loading time from 4 hours to less than 4 minutes. I really appreciate your help! Thank you! – Gelatinate

Answer 2

1. If it is possible, then you should send the text file to the server running SQL Server. Then use OPENROWSET(BULK) to open the text file (see "E. Using the OPENROWSET BULK provider with a format file to retrieve rows from a text file").
2. If you cannot send the text file to the server, then create a temporary or persistent DB table and use INSERT to insert all text file rows into the table.
3. If you are using SQL Server 2008, then you should use MERGE operator. If more old SQL Server version, then you can use two SQL commands: UPDATE and INSERT. And as a data source use (1) OPENROWSET or (2) DB table.

Highflown

Though it was much easier to apply the answer by valex to my situation, thank you for help. The target sql server is even 2000. A pretty old one. Remaking the whole algorythm to load files into some temporary table (and so on..) is not worth any performance gain (if any) comparing with what I achieved (and how much time I spent for it) thanks to the idea given by valex. Anyway, thanks! – Gelatinate