Search by primary key performance

Discussion of open issues, suggestions and bugs regarding LiteDAC (SQLite Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Max Terentiev
Posts: 19
Joined: Sun 05 Jun 2016 17:11

Search by primary key performance

Post by Max Terentiev » Tue 07 Feb 2017 14:40

Hi,

I need to perform many searches by primary key field of table:

Code: Select all

CREATE TABLE List1 (
    ID       INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
    Name   VARCHAR 
);
Table contains about 7 millions records. I need to check some huge CSV file against this table to detect is some IDs already exists in them:

I try to search like this:

Code: Select all

var
NewIDsFound:Integer;
begin
LocalQuery.SQL.Add('SELECT Name FROM List1 WHERE ID=:myid');
LocalQuery.Prepare;
LocalQuery.DisableControls;
NewIDsFound:=0;
for i := 0 to InList.Count-1 do
  begin
  LocalQuery.Params[0].AsInteger:=InList.IntValue[i];
  LocalQuery.ExecSQL;
  if LocalQuery.eof then
       Inc(NewIDsFound);
  end;
LocalQuery.Unprepare;
end;
But I can reach max 1200 searches per second (on Core i7 3.3 ghz and SSD drive) ! I thinks it's terrible slow. Maybe I miss some LiteDAC settings ? What settings is optimal for above task ?

MaximG
Devart Team
Posts: 1030
Joined: Mon 06 Jul 2015 11:34

Re: Search by primary key performance

Post by MaximG » Wed 08 Feb 2017 14:26

Try to use in the query the Open method :

Code: Select all

LocalQuery.Open;

Max Terentiev
Posts: 19
Joined: Sun 05 Jun 2016 17:11

Re: Search by primary key performance

Post by Max Terentiev » Wed 08 Feb 2017 19:02

I was try.

If I call:
for i := 0 to InList.Count-1 do
begin
LocalQuery.Close;
LocalQuery.Params[0].AsInteger:=InList.IntValue;
LocalQuery.Open;
if LocalQuery.eof then
Inc(NewIDsFound);
end;


I got same results (slow)

If I write

for i := 0 to InList.Count-1 do
begin
LocalQuery.Params[0].AsInteger:=InList.IntValue;
LocalQuery.Open;
if LocalQuery.eof then
Inc(NewIDsFound);
end;


It's runs fast but actually perform only first query and skip all remaining.

I try mORMot Sqlite wrapper, they perform about 100k searches per second (same database, same query) - 50 times faster !

MaximG
Devart Team
Posts: 1030
Joined: Mon 06 Jul 2015 11:34

Re: Search by primary key performance

Post by MaximG » Thu 09 Feb 2017 14:49

To speed up the work according to the sent scenario, you can try to enable metadata retrieving for the used table with the help of the ExtendedFieldsInfo property:

Code: Select all

LiteQuery.Options.ExtendedFieldsInfo := False;
However, even in this case, it is impossible to obtain performance comparable to that provided by low-level APIs. After all, LiteQuery is a descendant of the TDataSet component. So it has all its abilities, but it also inherits all its restrictions.

Post Reply