Fastest way to walk through huge sqlite table ?

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

Fastest way to walk through huge sqlite table ?

Post by 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 !

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

Re: Fastest way to walk through huge sqlite table ?

Post by MaximG » Tue 24 Jan 2017 11:02

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
Posts: 19
Joined: Sun 05 Jun 2016 17:11

Re: Fastest way to walk through huge sqlite table ?

Post by Max Terentiev » Tue 24 Jan 2017 17:11

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
Devart Team
Posts: 890
Joined: Mon 06 Jul 2015 11:34

Re: Fastest way to walk through huge sqlite table ?

Post by MaximG » Mon 27 Feb 2017 08:45

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

Post Reply