Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: FireDAC SQLite "limit 1" query returns 1 record but RecordCount massive


This question is answered. Helpful answers available: 1. Correct answers available: 1.


Permlink Replies: 4 - Last Post: Sep 2, 2015 10:57 PM Last Post By: Dmitry Arefiev
Tom Roberts

Posts: 102
Registered: 6/21/05
FireDAC SQLite "limit 1" query returns 1 record but RecordCount massive  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Aug 29, 2015 4:20 AM
Table Tbl has int64 primary key ID and has 2.4 million records (ID>0 for all ID's).

Query->SQL->Text = "select ID from Tbl where ID>0 order by ID limit 1"

takes ~660 ms to return 1 record but RecordCount property = 2.4 million.

Query->SQL->Text = "select ID from Tbl where ID>0 limit 1" (i.e. "order by ID" omitted)

takes ~1 ms to return the same record and RecordCount correctly returns 1.

I've got the FetchOptions Mode=fmAll and RecordCountMode=cmTotal although I doubt that's of any significance.

Both queries return the same 'explain query plan' and both execute in the same time in SQLite expert.

I'm using c++ builder xe6

Edited by: Tom Roberts on Aug 29, 2015 4:23 AM
Tom Roberts

Posts: 102
Registered: 6/21/05
Re: FireDAC SQLite "limit 1" query returns 1 record but RecordCount massive  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Aug 30, 2015 2:00 AM   in response to: Tom Roberts in response to: Tom Roberts
Tom Roberts wrote:
Table Tbl has int64 primary key ID and has 2.4 million records (ID>0 for all ID's).

Query->SQL->Text = "select ID from Tbl where ID>0 order by ID limit 1"

takes ~660 ms to return 1 record but RecordCount property = 2.4 million.

Query->SQL->Text = "select ID from Tbl where ID>0 limit 1" (i.e. "order by ID" omitted)

takes ~1 ms to return the same record and RecordCount correctly returns 1.

I've got the FetchOptions Mode=fmAll and RecordCountMode=cmTotal although I doubt that's of any significance.

Both queries return the same 'explain query plan' and both execute in the same time in SQLite expert.

I'm using c++ builder xe 6

If RecordCountMode is set to cmVisible both queries execute in ~1 ms and RecordCount returns 1 in both cases (even if Mode==fmAll). Since the query only returns 1 record regardless of what RecordCountMode is set to I can only assume it's a bug.
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: FireDAC SQLite "limit 1" query returns 1 record but RecordCount massive
Helpful
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 1, 2015 10:35 PM   in response to: Tom Roberts in response to: Tom Roberts
If RecordCountMode is set to cmVisible both queries execute in ~1 ms and RecordCount returns 1 in both cases (even if Mode==fmAll). Since the query only returns 1 record regardless of what RecordCountMode is set to I can only assume it's a bug.

It is rather limitation.

RecordCountMode==cmTotal builds SELECT COUNT(*) FROM (<subquery>)
query and then executes it. ORDER BY and LIMIT phrases allowed only at the
end of the top level SELECT. So, FireDAC just strips ORDER BY and LIMIT.
This leads to a time consuming query. You should be aware of that and use
RecordCountMode==cmTotal carefully.

Also, instead of LIMIT keyword you can use FetchOptions.RecsMax and
RecsSkip options.

--
With best regards,
Dmitry Arefiev / FireDAC Architect
Tom Roberts

Posts: 102
Registered: 6/21/05
Re: FireDAC SQLite "limit 1" query returns 1 record but RecordCount massive  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 2, 2015 7:42 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
If RecordCountMode is set to cmVisible both queries execute in ~1 ms and RecordCount returns 1 in both cases (even if Mode==fmAll). Since the query only returns 1 record regardless of what RecordCountMode is set to I can only assume it's a bug.

It is rather limitation.

RecordCountMode==cmTotal builds SELECT COUNT(*) FROM (<subquery>)
query and then executes it. ORDER BY and LIMIT phrases allowed only at the
end of the top level SELECT. So, FireDAC just strips ORDER BY and LIMIT.
This leads to a time consuming query. You should be aware of that and use
RecordCountMode==cmTotal carefully.

Also, instead of LIMIT keyword you can use FetchOptions.RecsMax and
RecsSkip options.

--
With best regards,
Dmitry Arefiev / FireDAC Architect

Thanks Dimitry, you're correct. The cmTotal has been the root of most of my problems (see other thread). In this particular case setting the RecordCountMode to cmFetched (when I've got fetch mode = fmAll) gives me the correct answer and firedac doesn't mess with the SQL.

PS Since changing RecordCountMode to cmFetched I've noticed several queries with disappointing execution speeds that are now much better.

Edited by: Tom Roberts on Sep 2, 2015 8:43 AM to add PS
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: FireDAC SQLite "limit 1" query returns 1 record but RecordCount massive  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 2, 2015 10:57 PM   in response to: Tom Roberts in response to: Tom Roberts
PS Since changing RecordCountMode to cmFetched I've noticed several queries with disappointing execution speeds that are now much better.

That is right, because FireDAC will not more issue "SELECT COUNT(*) ..." behind the scene.

--
With best regards,
Dmitry Arefiev / FireDAC Architect
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02