Devart Forums

Posted: **Tue 07 Feb 2017 14:40**

Hi,

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

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 ?

Posted: **Wed 08 Feb 2017 14:26**

Try to use in the query the Open method :

Code: Select all

LocalQuery.Open;

Posted: **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 !

Posted: **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.

Devart Forums

Search by primary key performance

Search by primary key performance

Re: Search by primary key performance

Re: Search by primary key performance

Re: Search by primary key performance