SQLite performance

[jodyatfsec]

I have been changing the local database in an existing large Delphi project from Paradox to Sqlite using Devart's LiteDac components. Things are going pretty well using the TLiteConnection and TLiteTables except that the project with SQLIte is now running much slower than the old Paradox project. I am not dealing with a huge amount of data. Most of our tables have less than 100 records. We have often used FieldByName to access table data. I have read that FieldbyName can be slow, but we had no performance problems with Paradox and rewriting the project to eliminate FiedlByName is probably not feasible. I am not a database expert. Are there settings for TLiteConnection and TLiteTable that would speed up data access? Thanks.

[AlexP]

Hello,

Please describe the operations (SELECT, INSERT, etc.) and data (types and amount), which pass on SQLite slower than on Paradox, or send the project (and table creating and filling scripts), on which you checked performance, to alexp*devart*com

[jodyatfsec]

I am not using a lot of queries with SELECT or INSERT SQL statements.
I am not allowed to send the code for the project to anyone outside.
After some work, the project is running with SQLite instead of Paradox; however, it is running 5 to 10 times slower than it ran using Paradox. The database is on the local PC and is never used by any other program. There are about 400 tables in the database. The individual tables rarely have over 20 records. Individual records rarely have over 50 fields. The field types are mostly string, integer or numeric. We use almost no graphic data.

The project has a lot of code like the following segment. I suspect that rewriting the following using a query would improve performance. In a few cases where I had a table with many more records, I have already successfully rewritten the code using queries which resulted in a huge improvement in performance. My questions is 'Are there settings or ways to speed up the performance without rewriting tens of thousands of lines of code? Thanks.

MyLiteTable.active := true;
MyLiteTable.first;
while not MyLiteTable.eof do
begin
if MyLiteTable.FieldByName('TYPE').asstring = SomevalueA then
begin
MyLiteTable.edit;
MyLiteTable.FieldByName('SOMEFIELDA').asFloat = SomeNumberA;
MyLiteTable.FieldByName('SOMEFIELDB').asFloat = SomeNumberB;
MyLiteTable.post;
end
else if MyLiteTable.FieldByName('TYPE').asstring = SomevalueB then
begin
MyLiteTable.edit;
MyLiteTable.FieldByName('SOMEFIELDA').asFloat = SomeNumberC;
MyLiteTable.FieldByName('SOMEFIELDB').asFloat = SomeNumberD;
MyLiteTable.post;
end;
MyLiteTable.Next;
end;
MyLiteTable.active := false;
end;

[AlexP]

Hello,

To increase speed of insertion to a SQLite DB, you can use transactions, i.e., in the loop, in which you insert data, start and commit a new transaction every N records (e.g., 100), the optimum number of records should be chosen experimentally.

[MrAndini]

I have the same issue with regards slow bulk updates, only around 60 -70 per second.

How do you use transactions with UniTable?

I am developing under C++ Builder XE4

[AlexP]

If you are inserting records in a loop, then you can use the following code to increase insertion speed:

Code: Select all

n := 1000; //the number of inserted records, after which Commit is called;
LiteConnection.StartTransaction;
for i := 1 to X do begin
  LiteTable.Append;
  ........
  LiteTable.Post;

  if (i mod n) = 0 then begin
      if LiteConnection.GetInTransaction then
        LiteConnection.Commit;
      LiteConnection.StartTransaction;
    end;

end;