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;
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 !