Fastest way to walk through huge sqlite table ?

Fastest way to walk through huge sqlite table ?

Postby Max Terentiev » Wed 04 Jan 2017 12:14

Hi,

In my app server I implement paging to display large tables in client's grid. One of my tasks is finding record position in query result by their ID:

Code: Select all
RecCount:=0;
LiteQuery.Sql.Add('SELECT ID FROM SomeTable ORDER BY SomeField, SomeField2 DESC');
LiteQuery.Open;
while not LiteQuery.Eof do
  begin
  Inc(RecCount);
  if LiteQuery.Fields.Fields[0].AsInteger=MyID then
      break;
  LiteQuery.Next;
  end;
Result:=RecCount;


Because table can be sorted by any field(s) I can't use SELECT COUNT(*) query and I MUST scan table myself.

In my PC I can scan about 1 million recrods per second using LiteDAC. But in C++/gcc using direct sqlite api I can scan about 7.5 millions records per second !

I think LiteDAC slower in this task because copies fetched records to internal MemDataSet.

So, my question is: it's possible to use some lower-level LiteDAC objects to scan table faster ? Something before TDataSet/TMemDataSet ? If it's possible - how ? )

Thanks !
Max Terentiev
 
Posts: 10
Joined: Sun 05 Jun 2016 17:11

Return to SQLite Data Access Components