Select Count very slow in Access 2003

[RayProud]

I've converted an Access 97 database to Access 2003 and the performance when using Select Count is dreadful. I've had to revert to FindFirst which is much much faster.

I don't understand this because in Access 97 it was just the opposite. I'm holding data in a local table on the front-end and before I write each record to the back-end (on the server) I was using Select Count to determine if a record already existed. If it did I asked the user if they wanted to replace the current record. If the select Count returned zero I just wrote a new record.

That worked fine in Access 97 and was 7 times faster than FindFirst but in Access 2003 Select Count is dreadfully slow and reverting to FindFirst has restored the speed.

Can anyone explain why Select Count should be so much slower than FindFirst which I always understood should be avoided like the plague!

Thanks.

 

[mp9]

If you're not already, try using SELECT COUNT(*) rather than SELECT COUNT(fieldName) - it's faster.

Also, for general performance problems following an upgrade from A97 to A2003, try these tips:

1. Turn off AutoCorrect features (Tools, Options, General - then uncheck "Track name AutoCorrect info"
2. For all tables, set the Subdatasheet Name property to [None] rather than the [Auto] default.
3. Recompile all your VBA.

Whilst these 3 tips may not address your specific problem with this query, you should find they help out in other areas of yuor db.

Cheers,
mp9

 

[RayProud]

Hi mp9,

Thanks for those tips. I've already got those set and it's not helped. I've changed to Select Count(*) but the improvement was minimal.

Here's a snippet from my code...

strSQLFind = "SELECT Count(*) as X FROM tblRedLiab WHERE Staff_No = '" & rst!PayNo & "' AND CaseNumber = " & lngCaseNo
Set rsx = dbs.OpenRecordset(strSQLFind)
If rsx!x = 0 Then
.AddNew

I can't see why that would be so slow to execute.

 

[RayProud]

I've discovered why I thought FindFirst was quicker than select count(*). In short it isn't. I was comparing the performance of two databases and the one with FindFirst was empty whereas mine had 40,000 existing records. When I cleared mine down FindFirst was very quick (logical as there was nothing to find!) but on a populated database FindFirst becomes more and more inefficient as the number of records grows.

So I'm back with Select Count(*). Sorry for wasting folks time.

 

[PHV]

Are Staff_No or CaseNumber indexed ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mp9]

If they're not already, you could try indexing both Staff_No and CaseNumber fields in tblRedLiab, to make the WHERE clause a bit mroe efficient.

An alternative to the code you posted, which probably won't be any faster but might be worth trying, is:

If DCount("*", "tblRedLiab", "Staff_No = '" & rst!PayNo & "' AND CaseNumber = " & lngCaseNo) = 0 Then
    .AddNew

 

[RayProud]

Thanks PHV and mp9. Both should have been indexed but with all the converting and renaming for the testing CaseNumber wasn't indexed. They're both indexed now but I really need to put a timer routine in to accurately measure performance. I'll do that and also change to DCount and see what difference that makes.

Thanks!

 

[RayProud]

mp9,

I've tried DCount now and the difference is marginal. 263 secs vs 266 secs with Select Count(*).

All this with XP / Access 2003 and an empty database. NT4 / Access 97 did it in 166 secs on a populated one! The price of progress eh!

Cheers.

 

[abaldwin]

Instead of making a count which forces SQL to read all the records why not do this

strSQLFind = "SELECT Staff_NO FROM tblRedLiab WHERE Staff_No = '" & rst!PayNo & "' AND CaseNumber = " & lngCaseNo
Set rsx = dbs.OpenRecordset(strSQLFind)
If rsx.eof and rsx.bof then Then
.AddNew


Andy

Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[RayProud]

Andy,

Why would I need to check that EOF and BOF are both TRUE? Would they ever return different values? Up to now I've always used EOF=False to mean that a record was present.

I don't need to return the record - just determine if it exists - hence the Select Count(*) which I guess would be quicker.

 

[mp9]

It may help to open rsx as a snapshot recordset, e.g.

Set rsx = dbs.OpenRecordset(strSQLFind, dbOpenSnapshot)

 

[abaldwin]

Ray,
Just a force of a habbit. As for the speed, why don't you test it on your large data set and see if it is faster.

Andy

Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[abaldwin]

Sorry did not finish. I had a really weird occurance of this same problem. Selecting one field and checking for eof ran faster than a count of the same where statement. This post caught my eye for that reason. My solve was to go the route that I posted. Did not seem to make sense. Is the data native to Access or is your data connected to some other data source through ODBC. Mine was native.

Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[RayProud]

mp9,

<<It may help to open rsx as a snapshot recordset, e.g.>>

Tried that but the diference was minimal. Looks like I've got it as efficient as it's going to get.

I should add one thing to this. Despite XP SP2 being installed it has not enabled LongFilenameCaching. I'm awaiting a Hotfix from Microsoft. This fix speeds things up quite a bit. Check 834350 on the KB.