how to avoid slow count function

[Guest]

I have recently purchased total access analyzer – a great tool. When I ran
it, I got a message about a slow Count function.
I would like to know how to change the query below so it doesn’t use the
slow count function, but I haven’t a clue how to.
The query is used to display the total number of each item on a summary form.
Here is the SQL:

SELECT tblRecords.TrID, tblRecords.Item, tblRecords.Rating,
Count(tblRecords.Item) AS CountOfItem, tblRecords.Updated
FROM tblRecords
ORDER BY tblRecords.TrID, tblRecords.Item, tblRecords.Rating;

 

[Gary Walter]

I have recently purchased total access analyzer - a great tool. When I ran
it, I got a message about a slow Count function.
I would like to know how to change the query below so it doesn't use the
slow count function, but I haven't a clue how to.
The query is used to display the total number of each item on a summary
form.
Here is the SQL:

SELECT tblRecords.TrID, tblRecords.Item, tblRecords.Rating,
Count(tblRecords.Item) AS CountOfItem, tblRecords.Updated
FROM tblRecords
ORDER BY tblRecords.TrID, tblRecords.Item, tblRecords.Rating;

Hi Nugimac,

Aren't you missing a GROUP BY clause?

If this was just a typo, then you might try
making sure you have an index on field
"Item" (in table design of table "tblRecords").

good luck,

gary

 

[John Vinson]

I have recently purchased total access analyzer – a great tool. When I ran
it, I got a message about a slow Count function.
I would like to know how to change the query below so it doesn’t use the
slow count function, but I haven’t a clue how to.
The query is used to display the total number of each item on a summary form.
Here is the SQL:

SELECT tblRecords.TrID, tblRecords.Item, tblRecords.Rating,
Count(tblRecords.Item) AS CountOfItem, tblRecords.Updated
FROM tblRecords
ORDER BY tblRecords.TrID, tblRecords.Item, tblRecords.Rating;

Well, if you want to display the total number of items, then you must
count the items. TAA is warning you that counting items is slow, which
is quite true; but if that is the result that you need, you're stuck
with it. It's just a warning not to use Count if some less
time-consuming alternative is available (for instance a DISTINCT query
to just return one example of duplicate records).


John W. Vinson[MVP]

 

[nugi]

Thanks John, I am glad to know that and I can stop worrying about it.

 

[Brendan Reynolds]

I believe that what Total Access Analyzer is warning you about here is the
use of Count([FieldName]) rather than Count(*). Count(*) is significantly
faster, and will return the same results if the field in question does not
contain any Null values. If the field does contain Null values,
Count([FieldName]) will return the count of non-Null values, while Count(*)
will return the count of all values, including Null values. In other words,
if tblRecords.Item is a required field and can not contain Null values, you
can get the same result more efficiently using Count(tblRecords.*) instead
of Count(tblRecords.Item).

Here's a link to the on-line help topic on the Count function. Note
especially what it says under the heading 'Remarks'.

http://office.microsoft.com/assistance/hfws.aspx?AssetID=HP010322151033