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.AsInteger=MyID then break; LiteQuery.Next; end; Result:=RecCount;
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 ? )