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

Re: Fastest way to walk through huge sqlite table ?

Postby 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/?devart.dac.tcustomdadataset.unidirectional.htm ) and SmartFetch properties ( https://www.devart.com/litedac/docs/?devart.sqlitedac.tcustomlitedataset.smartfetch.htm )
MaximG
Devart Team
 
Posts: 603
Joined: Mon 06 Jul 2015 11:34

Re: Fastest way to walk through huge sqlite table ?

Postby 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/?devart.dac.tcustomdadataset.unidirectional.htm ) and SmartFetch properties ( https://www.devart.com/litedac/docs/?devart.sqlitedac.tcustomlitedataset.smartfetch.htm )


I already set UniDirectional=true. And I also use FetchRows=65535.
Is SmartFetch is more efficient than FetchRows=65535 ?
Max Terentiev
 
Posts: 16
Joined: Sun 05 Jun 2016 17:11

Re: Fastest way to walk through huge sqlite table ?

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


Return to SQLite Data Access Components