Performance with multiple inserts

[miguelenguica]

Hi. I'm porting some code into Delphi. I'm using UniDAC components to interface SQLite. At some point I have to perform an insert query for each item in a TStringList. The list can grow to several hundred thousand entries. The performance is poor if I need to perform ExecSQL for each line. Can anyone tell me how to do some kind of bulk insert or transaction for this. This is my code now:

Code: Select all

var
  myQuery : TUniQuery;
  (...)
begin
  myQuery:= TUniQuery.Create(nil);
  myQuery.Connection:= my_db;
  //
  for i := 0 to origin_2SL.Count-1 do
  begin
        myQuery.SQL.Text:= 'insert into mytable(a,b,c,d) values(:a,:b,:c,:d)');
        (...)//fill the params
        //execute query
        myQuery.ExecSQL
  end;
  //
  myQuery.Free;
end;

Can anyone tell me how to improve the performance?

Thanks!

[bork]

Hello

You can use the TUniLoader component for loading a big amount of data to the database. TUniLoader provides the functionality for loading with maximum speed. You can find more detailed information about using TUniLoader in the "TUniLoader class" article of the UniDAC documentation. In addition, you can take a look at UniDAC demo project that demonstrates how TUniLoader works. The UniDAC demo project are located in the %UniDAC_Install_Directory%\Demos\UniDacDemo directory.

Also, we had a similar request about slow DB update (here: http://forums.devart.com/viewtopic.php?f=28&t=17621). You can try the solution that was suggested there.