Save query count to variable (1 Viewer)

sumdumgai

Registered User.
Local time
Today, 01:11
Joined
Jul 19, 2007
Messages
453
How can I save the record count from query results without rerunning a count query?


Thanks.
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 22:11
Joined
Aug 30, 2003
Messages
36,118
VariableName = DCount("*", "QueryName")

though that will run the query behind the scenes.
 

sumdumgai

Registered User.
Local time
Today, 01:11
Joined
Jul 19, 2007
Messages
453
'Behind the scenes'. Might that add to the query response time; i.e., to the time it takes for query results to be returned?
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 22:11
Joined
Aug 30, 2003
Messages
36,118
Well, it won't add to the time it takes the query to run. I'm saying that running the DCount() will run the query. If the query takes 2 seconds to run, the DCount() will likely take 2 seconds to return the result. I don't know of any way to know how many records a query returns without running the query.

Perhaps if you describe your situation and what you're trying to accomplish overall, we can come up with an effective solution.
 

sumdumgai

Registered User.
Local time
Today, 01:11
Joined
Jul 19, 2007
Messages
453
I've been talking with a couple of other people on this forum to get help on improving query response time. That problem has been solved. I thought it might be beneficial to display the returning record count. I was hoping there was a way for the query to give record count as soon as the search results were available; i.e., without having to re-query. Response time is critical.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 05:11
Joined
Feb 19, 2013
Messages
16,553
if you open it as a recordset, you need to movelast before you will get a valid recordcount - but at least you are not running it twice.

The access tabledef has a recordcount property which returns the number of records in a table (but only works on local tables)

also in VBA you have the recordsaffected property which can be applied after they have executed (using .execute) to return how many were deleted/appended/updated
 

sumdumgai

Registered User.
Local time
Today, 01:11
Joined
Jul 19, 2007
Messages
453
Doing 'DoCmd' select query so 'recordsaffected' won't work. Thanks anyway.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:11
Joined
May 21, 2018
Messages
8,463
I would not think you would want to return the recordset of all records and count them. I would think that would be slow. You would want to return a single record that is the count of the records in the query.

set RS = currentdb.openrecordset ("select * as TotalCount from yourQuery", dbOpenForwardOnly)
x = rs!TotalCount

I doubt that is any different than PBaldy's solution. In the old days the D aggregates functions were slow and you could get better performance rolling your own. Not certain anymore.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 05:11
Joined
Feb 19, 2013
Messages
16,553
Doing 'DoCmd' select query so 'recordsaffected' won't work
won't work with docmd anyway. And I did say it only works with action queries (using execute). You haven't specifically said what type of queries you are running so I was just trying to cover all the bases
 

isladogs

MVP / VIP
Local time
Today, 05:11
Joined
Jan 14, 2017
Messages
18,186
As I've done a lot of speed tests recently I thought I'd run a comparison of DCount vs recordset.count

Unfortunately, using this code causes error 3141: The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

Code:
set RS = currentdb.openrecordset ("select * as TotalCount from yourQuery", dbOpenForwardOnly)
x = rs!TotalCount

I tried various variations but couldn't get it to work.
So I replaced it with
Code:
Set rst = CurrentDb.OpenRecordset("QueryName", dbOpenDynaset)
    rst.MoveLast
    rst.MoveFirst
    N = rst.RecordCount

I ran the comparison several times on a query based on a large table of 2.6 million records. The average times were as follows:

DCount: N = 2616838 ; Time taken 0.05 seconds
RecordsetCount: N = 2616838 ; Time taken 0.22 seconds

Both are fast but DCount took less than 25% of the time using a recordset.
Whether using dbOpenForwardOnly would be much faster I can't tell as it defeated my efforts to solve the error
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 22:11
Joined
Aug 30, 2003
Messages
36,118
Code:
set RS = currentdb.openrecordset ("select * as TotalCount from yourQuery", dbOpenForwardOnly)[/QUOTE]

Wouldn't it need to be:

select [COLOR="red"]Count([/COLOR]*[COLOR="Red"])[/COLOR] as TotalCount from yourQuery
 

isladogs

MVP / VIP
Local time
Today, 05:11
Joined
Jan 14, 2017
Messages
18,186
Thanks Paul

I thought I'd tried that in my various attempts but obviously not!
That brought the recordset count time down as you would expect

On running each once the times were almost identical 0.05 & 0.06 seconds
So I run each 100 times in a loop and measured the total time:

Number of loops 100; DCount : N = 2616838 ; Time taken 5.28 seconds (range 3.9 => 5.67s)
Number of loops 100; RecordsetCount: N = 2616838 ; Time taken 2.57 seconds (range 2.54 => 4.55s)

As you can see this time the recordset approach typically took around half the time
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:11
Joined
May 21, 2018
Messages
8,463
select Count(*) as TotalCount from yourQuery
That is what I meant. Thanks for the catch.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 16:11
Joined
Jan 20, 2009
Messages
12,849
Was the DCount applied to a query that returned an indexed field?

DCounts against tables with indexed fields are vastly faster than those without indexes. Wondering how it affects it if a query is in between.
 

isladogs

MVP / VIP
Local time
Today, 05:11
Joined
Jan 14, 2017
Messages
18,186
I ran both tests on queries as well as tables
The queries did include an indexed field.

Just for info I redid both sets of tests on the PK field itself.

That had the effect of speeding up the DCount & slowing down the recordset methods with typical results as follows:
Number of loops 100; DCount : N = 2616838 ; Time taken 3.66 seconds
Number of loops 100; RecordsetCount: N = 2616838 ; Time taken 3.77 seconds

Not a lot in it but DCount slightly faster each time I tested

Next I ran the count on an unindexed field which was inevitably going to be much slower. In fact it was so slow that running each 100 times would have taken a ridiculous time.
The following is for 10 loops
Number of loops 10; DCount : N = 2175016 ; Time taken 213.32 seconds
Number of loops 10; RecordsetCount: N = 2175016 ; Time taken 214.65 seconds

So multiplying up by 10 for a likely 100 loop outcome, both times would be around 2130 seconds instead of around 3.7 seconds! Almost 600 times larger!!!!!
 
Last edited:

Users who are viewing this thread

Top Bottom