Fastest way to walk through huge sqlite table ?

[Max Terentiev]

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 !

[MaximG]

You are right. Solutions of using a low level API when working with SQLite DB will be more efficient. After all, LiteQuery is a descendant of the TDataSet component. So it has all its possibilities, but it also inherits all its limitations. To speed up the operations of fetching data, you can try to use the Unidirectional ( https://www.devart.com/litedac/docs/?de ... tional.htm ) and SmartFetch properties ( https://www.devart.com/litedac/docs/?de ... tfetch.htm )

[Max Terentiev]

To speed up the operations of fetching data, you can try to use the Unidirectional ( https://www.devart.com/litedac/docs/?de ... tional.htm ) and SmartFetch properties ( https://www.devart.com/litedac/docs/?de ... tfetch.htm )

I already set UniDirectional=true. And I also use FetchRows=65535.
Is SmartFetch is more efficient than FetchRows=65535 ?

[MaximG]

SmartFetch work efficiency when fetching any table data depends on the used in this table fields types. The greatest benefit when working SmartFetch you will receive when using larger sizes data types (long strings, BLOBs, etc.). The optimal value of the FetchRows parameter will be determined by business logic of your application