rs.recordcount (inaccurate) 3

[philcon]

Hi there,

I know this is going to be a no-brainer, but could anyone tell me why the following code produces a record count of 1 rather than 4 (the number or records in the table)

Many thks in advance


Phil.

Private Sub Command141_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_MailpackConsolidate", dbOpenDynaset)
Debug.Print rs.RecordCount

End Sub

 

[Malchik]

It depends what kind of cursor you are using. In your case, you need to refresh the recordset before calling the count:

Set rs = db.OpenRecordset("tbl_MailpackConsolidate", dbOpenDynaset)
if not rs.eof then
rs.movelast
rs.movefirst
endif

Debug.Print rs.RecordCount

Mal'chik

 

[philcon]

Thanks Mal'chik

I was playing around and realised it had something to do with the cursor type as the answer was correct if I used dbOpenTable

I need to learn a little more about this.

Many thanks for the response.

Philcon

 

[trystanhuwwilliams1]

Your recordcount procedure returns a 1 because there are records in your recordset, i.e the first record exits - to count the rest of the records in the recordset you need to move to the last record in the recordset.


T

 

[GHolden]

FYI if you simply want to count the number of records in a recordset, it's more efficient to use DCount...

eg.

DCount("ID", "tblTable&quot'Count all ID's in "tblTable"
DCount("ID", "tblTable", "Status = 1&quot'Count all ID's in "tblTable" where field Status = 1
etc...






There are two ways to write error-free programs; only the third one works.

 

[JeremyNYC]

More efficient? Well, if you're counting the number of key strokes to type it, yes. But if you're looking for speed, the DCount and related functions are _extrememly_ slow.

I just did some test, so if you want to read about it, here goes...(warning, this will be a very long post)...

I wrote four different subs to test out different ways of counting all the records in a table, and one tiny sub to run them all, one after the other. Try this on your machine and see what results you get, but I guarantee that the DCount will be far slower than the others...


This one uses DCount. It's a bunch less code than the others, so it's not surprising that people think of it as fast.

Sub test1()
Dim dtmStart As Date
Dim intCount As Integer
Dim lngDiscreps As Long

dtmStart = Now

Do Until intCount = 50
lngDiscreps = DCount("DiscrepancyID", "tblDiscrepancy&quot
intCount = intCount + 1
Loop
Debug.Print "Test 1: " & DateDiff("s", dtmStart, Now)

End Sub

This one uses a recordset. Here I create the database and recordset each time through the loop. It's not a very real-world test, because I always create a database variable once, when the mdb opens and close it only when the mdb quits.
Sub test2()
Dim dtmStart As Date
Dim intCount As Integer
Dim lngDiscreps As Long
Dim db As dao.Database
Dim rst As dao.Recordset
Dim strsql As String

dtmStart = Now
Do Until intCount = 50
strsql = "SELECT Count(*) as DiscrepCount FROM tblDiscrepancy"
Set db = CurrentDb
Set rst = db.OpenRecordset(strsql, dbOpenSnapshot)
lngDiscreps = rst!discrepcount
intCount = intCount + 1
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
Loop
Debug.Print "Test 2: " & DateDiff("s", dtmStart, Now)

End Sub

This one's fairly real--the database variable only gets created and killed once.
Sub test3()
Dim dtmStart As Date
Dim intCount As Integer
Dim lngDiscreps As Long
Dim db As dao.Database
Dim rst As dao.Recordset
Dim strsql As String

dtmStart = Now
Set db = CurrentDb
strsql = "SELECT Count(*) as DiscrepCount FROM tblDiscrepancy"
Do Until intCount = 50
Set rst = db.OpenRecordset(strsql, dbOpenSnapshot)
lngDiscreps = rst!discrepcount
intCount = intCount + 1
rst.Close
Set rst = Nothing
Loop
db.Close
Set db = Nothing
Debug.Print "Test 3: " & DateDiff("s", dtmStart, Now)

End Sub

This one is also quite real for me--I often have code that uses a recordset variable more than once in a single sub or function.
Sub test4()
Dim dtmStart As Date
Dim intCount As Integer
Dim lngDiscreps As Long
Dim db As dao.Database
Dim rst As dao.Recordset
Dim strsql As String

dtmStart = Now
Set db = CurrentDb
strsql = "SELECT Count(*) as DiscrepCount FROM tblDiscrepancy"
Set rst = db.OpenRecordset(strsql, dbOpenSnapshot)
Do Until intCount = 50
lngDiscreps = rst!discrepcount
intCount = intCount + 1
Loop
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
Debug.Print "Test 4: " & DateDiff("s", dtmStart, Now)

End Sub

This just runs the test on all of them, so I don't have to keep starting it over.
Sub TestThemAll()
Call test1
Call test2
Call test3
Call test4
End Sub

Here are some test results. Quite a difference!
Test 1: 98
Test 2: 48
Test 3: 39
Test 4: 0
Test 1: 66
Test 2: 35
Test 3: 40
Test 4: 1
Test 1: 64
Test 2: 36
Test 3: 37
Test 4: 1
Test 1: 76
Test 2: 45
Test 3: 35
Test 4: 1
Test 1: 67
Test 2: 46
Test 3: 49
Test 4: 1

I HIGHLY recommend people take a close look at their use of the Dynamic functions. They staink, speedwise.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[GHolden]

Jeremy, not questioning your testing at all. However, I always used to code using RecordCount (being primarily a VB programmer not Access VBA) I was advised to change some code to use DCount.

The following code...

Function mCreatedOrders(ByVal vintScheme As Integer, vlngContRef As Long) As Double

On Error GoTo ErrorHandler

Set db = CurrentDb

If vlngContRef = 0 Then
Set StatusRst = db.OpenRecordset("SELECT CustRef FROM qryStats WHERE OrderCreated IS NOT NULL AND SchemeRef = " & vintScheme, dbOpenDynaset)
Else
Set StatusRst = db.OpenRecordset("SELECT CustRef FROM qryStats WHERE OrderCreated IS NOT NULL AND SchemeRef = " & vintScheme & " AND ContractorRef = " & vintContRef, dbOpenDynaset)
End If

If Not StatusRst.EOF Then
StatusRst.MoveLast
mCreatedOrders = StatusRst.RecordCount
End If

StatusRst.Close
db.Close
Set StatusRst = Nothing
Set db = Nothing

Exit Function

ErrorHandler:

MsgBox "Error in 'Created Orders'." & vbCrLf & Err.Description, , "Error"

End Function


Was change to the format..

Function mCountProperties(ByVal vlngContRef As Long, ByVal vintPaymentNo As Integer, ByVal vintScheme As Integer) As Long
On Error GoTo ErrorHandler

If vlngContRef > 0 Then
mCountProperties = DCount("ValNo", "qryPropertyCount", "ValNo = " & vintPaymentNo & "AND ContRef = " & vlngContRef & " AND SchemeRef = " & vintScheme)
Else
If vintScheme > 0 Then
mCountProperties = DCount("ValNo", "qryPropertyCount", "ValNo = " & vintPaymentNo & " AND SchemeRef = " & vintScheme)
Else
mCountProperties = DCount("ValNo", "qryPropertyCount", "ValNo = " & vintPaymentNo)
End If
End If

Exit Function

ErrorHandler:

MsgBox "Error in 'Count Properties'." & vbCrLf & Err.Description, , "Error"

End Function

(I know these are not identical, I haven't got matching old and new copies of the same function, but you get the idea)

In this case the DCount was significantly faster.

Again, not doubting your testing. Just posting for interest.



There are two ways to write error-free programs; only the third one works.

 

[GHolden]

Just to further bore every one...

Private Function mlngDCount() As Long

'mlngDCount = DCount("Hit", "tblHit&quot
'mlngDCount = DCount("Hit", "tblHit", "One = True&quot
'mlngDCount = DCount("Hit", "tblHit", "One = True AND Two = True&quot
'mlngDCount = DCount("Hit", "tblHit", "One = True AND Two = True AND Three = True&quot

End Function

Private Function mlngRecCount() As Long
Dim db As Database
Dim rstCount As DAO.Recordset

Set db = CurrentDb
'Set rstCount = db.OpenRecordset("SELECT Hit FROM tblHit", dbOpenDynaset)
'Set rstCount = db.OpenRecordset("SELECT Hit FROM tblHit WHERE One = True", dbOpenDynaset)
'Set rstCount = db.OpenRecordset("SELECT Hit FROM tblHit WHERE One = True AND Two = True", dbOpenDynaset)
'Set rstCount = db.OpenRecordset("SELECT Hit FROM tblHit WHERE One = True AND Two = True AND Three = True", dbOpenDynaset)

rstCount.MoveLast
mlngRecCount = rstCount.RecordCount
rstCount.Close
db.Close
Set rstCount = Nothing
Set db = Nothing

End Function

I ran these two functions against each other (un-reming the appropriate line in each). The table has 250000 records, 125000 records in fields One, Two and Three are set to True.
As the number of conditions in each statement increased, the DCount was performing better and better against the RecordSet. 100% faster by the time 3 conditions were added.

Not conclusive I know !! But something to consider.....



There are two ways to write error-free programs; only the third one works.

 

[Hap007]

Whoa there,

Regarding DCount and DLookup:

These are quick little functions that work fine when your backend is Access, but will not work if the Backend is SQL.

So, remember, all the DLookup and DCount functions will need to be re-written if you ever upsize to SQL.

An alternative might be to use a 'Totals' Query, grouped by a certain field. When you open this recordset, it will only return 1 record (if you prepare it correctly) with your desired totals.

Enjoy,
Hap



Access Developer

http://www.fcs-software.com/fcsaccessdev.html

-
Specializing in Access based Add-on Solutions for the Developer

 

[RickSpr]

Jeremy,

I'm afraid your methodology is flawed, which doesn't prove that DCount() is more efficient, but invalidates your conclusion that it is less efficient.

Your TestThemAll procedure calls the four test functions one after the other. When writing benchmark code, you never do that, because you need to test the alternatives against identical starting conditions (as nearly as possible) in order to get comparable results. When you run them back to back like this, the ending conditions of the first test become the starting conditions of the second test, etc.

Specifically, in this case, DLookup() took the performance hit because it had to load all the data from the database. But at the end of the DLookup test, many of those database pages may have been cached by Jet or even Windows, so subsequent tests may have gotten away without doing as much I/O.

As a somewhat more reliable test, you might run, prior to each test routine, a procedure that simply reads the table from beginning to end. That should much improve the consistency of the starting conditions, though I'd have more confidence if you exited Access and restarted it for each test, and even more confidence if you restarted Windows each time.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[RickSpr]

Jeremy,

I thought of this right after I posted. A quick and easy test of how much the unequal starting conditions influenced the measurement is to run your tests again, but with the order of the tests reversed. I'll be DCount() winds up looking pretty good, then!

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[RickSpr]

Oh, man.... <whining about my failure to see everything at once>

I just realized something else. Tests 2 through 4 are using Count(*) with no restriction. I think Jet has an optimization for that. It keeps a record count in the table header, so for Count(*) it doesn't even have to read the table at all. DCount(*) on the other hand may not be able to take advantage of this. If it can't, then it would be fair to conclude that DCount(*) performs more poorly than Count(*), but since that's based on a special optimization, it doesn't extend to the general proposition that DCount() performs more poorly than Count().

A more general conclusion could be drawn by using DCount(&quot;*&quot;, tblDiscrepancy, &quot;DiscrepancyID > -1&quot and &quot;SELECT Count(*) FROM tblDiscrepancy WHERE DiscrepancyID > -1&quot;.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[RickSpr]

Hey Hap,

Good point about the problem of upsizing. However, I think if I upsized I'd probably just write my own domain aggregate functions that build a SQL query and run it against the SQL Server database. By using the same names as the built-in functions, I should be able to get away without revising any code. And the replacement functions should be easy.

Of course, there might be a problem with this if I have a mixture of SQL Server and Jet tables and want to use the domain aggregate functions on both, but for the Jet tables I could just prefix DLookup with 'VBA.' and I still don't have to rewrite anything.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[psemianonymous]

The bottom line is that whereas DCount/DLookup performs comparably with other methods when you're running on Access tables, it is definitely *not* to be used when running anything BUT Access tables. So one of you decides &quot;I will never use DLookup again so I never have to change it upon upsizing&quot;, the other decides &quot;I don't need to upsize, so I will stick with the default Dlookup instead of bothering with doing something else&quot;.

The performance gain, as long as it is some reasonable factor of similarity (i.e. one is three times as fast as the other)--this performance difference is not terribly bad when we're dealing with Access-only. But upsize to a server-based database and you will see a remarkable performance difference. DLookup would then take an order of magnitude more time than the equivalent &quot;fDLookup&quot;.

So you can agree to disagree.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[RickSpr]

Well, I finally got down to running a benchmark myself (I'd been hoping to avoid it).

Methodology
===========
The code I used is reproduced at the end of this post. I used Jeremy's test1, test2, and test3 routines, with modifications to let me control the iteration count and with a restriction of &quot;DiscrepancyID > -1&quot;. I didn't use test4, because it merely fetches the same value repeatedly from an open recordset. It seems to me that a competent programmer wouldn't even consider using DCount() if he has the value at hand like this--though he should move it to a variable rather than search the rst.Fields collection repeatedly.

My PC is a 400 MHz Pentium III, 128 MB RAM, running Windows ME and Access 97 for this test. Background processes were allowed to run during the test, but System Monitor reports a static background CPU utilization of 1%, so I don't think this introduced any significant error.

My table initially consisted of 32 rows with two columns:
DiscrepancyID, Long Integer, set to a number from 1 to 32
Test1, Text(255), set to String(255, &quot;x&quot
The table had no index defined on it.

For successive benchmarks, I kept enlarging the table (using Copy and Paste Append) to produce row counts an order of magnitude higher, and reduced the iteration counts to keep the test timings in the same general range. I recompiled and saved the code after each iteration count change. I did not compact the database. I then ran the TestThemAll procedure once in the hopes of preloading everything possible into memory, to eliminate aberrations caused by disk I/O overhead. Finally, I ran the TestThemAll procedure (forward order) twice, followed immediately by the TestThemAllReverse procedure (backward order) twice.

As in Jeremy's tests, the timing function consisted of the VBA Now() function, which has a 1 second resolution. This yields an error margin of + or - just under 1 second, which is introduced by the absence of synchronization between the function value changing and me hitting the F5 key to run the test.

Other than keeping my hands off the mouse and keyboard while the tests were running, nothing was done to control for other error sources. However, the benchmark results, as you will see below, were remarkably consistent across all tests, so it appears that there were no significant sources of error.

Benchmark Results
=================

Table record count = 32, Iterations = 5000
                        Test1   Test2   Test3
Run #1, forward order     25      36      18
Run #2, forward order     25      36      18
Run #3, backward order    24      37      18
Run #4, backward order    25      36      18

Table record count = 320, Iterations = 3000
                        Test1   Test2   Test3
Run #1, forward order     18      25      14
Run #2, forward order     18      25      14
Run #3, backward order    18      25      14
Run #4, backward order    19      25      14

Table record count = 3,200, Iterations = 1500
                        Test1   Test2   Test3
Run #1, forward order     27      31      25
Run #2, forward order     27      31      26
Run #3, backward order    27      31      25
Run #4, backward order    27      31      25

Table record count = 32,000, Iterations = 300
                        Test1   Test2   Test3
Run #1, forward order     44      45      44
Run #2, forward order     43      45      44
Run #3, backward order    44      45      44
Run #4, backward order    43      45      44

(At this point I set the Text1 column of the table to Null, compacted the database, ran TestThemAll to reestablish starting conditions, and ran all tests again.)

Table record count = 32,000, Iterations = 300
                        Test1   Test2   Test3
Run #1, forward order     35      35      35
Run #2, forward order     35      35      35
Run #3, backward order    35      35      35
Run #4, backward order    35      35      35

Error Analysis
==============
The results were highly consistent for any given algorithm and benchmark. Since all test results were within the predicted margin of error, there are no statistically significant deviations. This yields a very high level of confidence in the measurements.

Conclusions
===========
1. For large numbers of queries against tiny tables, running DCount() is about 30% faster than creating both a Database object and a Recordset object using a Count() query.
2. For large numbers of queries against tiny tables, creating a Recordset object using a Count() query and an already existing Database object is about 25% faster than running DCount().
3. As the table row count increases, the algorithms tend to converge toward the same speed.

Corollary conclusion:
CurrentDb() is a relatively slow function. If you would use it often, it's better to create a global Database variable at application startup and reference it instead.

Additional notes:
1. In my first preconditioning run, I had accidentally left the restriction out of Test3 (which takes advantage of an already open Database object). The timing for that test was 16 seconds, 2 seconds less than the runs with the restriction. This suggests that either Jet has no optimization for Count(*), or it's only minimally effective, at least on a small table.
2. I noticed during my test runs that my HD light barely flickered. I think it's safe to conclude that Jet does, in fact, cache database pages.
3. The last benchmark, with shorter rows, suggests that as the row size decreases, all three algorithms get faster, and also that the algorithms tend to converge toward the same speed. However, more tests with additional row sizes would be needed to establish a confidence level in these hypotheses.


Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[RickSpr]

Forgot the code:

Const Iterations = 3000

Sub test1()
Dim dtmStart As Date
Dim intCount As Integer
Dim lngDiscreps As Long

dtmStart = Now

Do Until intCount = Iterations
    lngDiscreps = DCount("DiscrepancyID", "tblDiscrepancy", "DiscrepancyID > -1")
    intCount = intCount + 1
Loop
Debug.Print "Test 1: " & DateDiff("s", dtmStart, Now)

End Sub

'This one uses a recordset. Here I create the database and recordset each time through the loop. It's not a very real-world test, because I always create a database variable once, when the mdb opens and close it only when the mdb quits.
Sub test2()
Dim dtmStart As Date
Dim intCount As Integer
Dim lngDiscreps As Long
Dim db As dao.Database
Dim rst As dao.Recordset
Dim strsql As String

dtmStart = Now
strsql = "SELECT Count(*) as DiscrepCount FROM tblDiscrepancy WHERE DiscrepancyID > -1"
Do Until intCount = Iterations
    Set db = CurrentDb
    Set rst = db.OpenRecordset(strsql, dbOpenSnapshot)
    lngDiscreps = rst!discrepcount
    intCount = intCount + 1
    rst.Close
    Set rst = Nothing
    db.Close
    Set db = Nothing
Loop
Debug.Print "Test 2: " & DateDiff("s", dtmStart, Now)

End Sub

'This one's fairly real--the database variable only gets created and killed once.
Sub test3()
Dim dtmStart As Date
Dim intCount As Integer
Dim lngDiscreps As Long
Dim db As dao.Database
Dim rst As dao.Recordset
Dim strsql As String

dtmStart = Now
Set db = CurrentDb
strsql = "SELECT Count(*) as DiscrepCount FROM tblDiscrepancy WHERE DiscrepancyID > -1"
Do Until intCount = Iterations
    Set rst = db.OpenRecordset(strsql, dbOpenSnapshot)
    lngDiscreps = rst!discrepcount
    intCount = intCount + 1
    rst.Close
    Set rst = Nothing
Loop
db.Close
Set db = Nothing
Debug.Print "Test 3: " & DateDiff("s", dtmStart, Now)

End Sub

'This just runs the test on all of them, so I don't have to keep starting it over.
Sub TestThemAll()
Call test1
Call test2
Call test3
End Sub

Sub TestThemAllReversed()
Call test3
Call test2
Call test1
End Sub

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[JeremyNYC]

Rick,

All points well taken. I've definitely never done any formal testing or benchmarking, or really read much about them, and I should have posted that disclaimer along with that huge post. On the other hand, I guess that was pretty apparent to you!

In any case, I don't know if I'll be able to get to it any time soon, but I would definitely like to get back to this and do some more rigorous tests. When I get to them, I'll be sure to post back here.

Thanks for pointing out some of the errors of my ways.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[RickSpr]

Jeremy,

&quot;Pointing out the errors of my ways&quot; - I hope you didn't see this as anything personal. If you did, please accept my sincere apology. I have an unfortunate tendency to get rather robot-like and insensitive when I put on my science hat (as you might guess from the mind-numbing &quot;research paper&quot; above).

To be honest, when I first read your warning about domain aggregate functions, I was dismayed since I've used them so often. I was anxious to find out just how bad they were, and that's what led me to take all day doing a benchmark. I certainly wasn't out to discredit you. You're one of the most valued contributors here, and I've learned to put a lot of confidence in what you say.

In fact, I learned in my benchmark that my criticism of your inconsistent starting conditions was largely unjustified. As I explained in the long post, I ran TestThemAll once to precondition for each benchmark. I didn't publish the results of this preconditioning run, but I'll tell you now that they sometimes ran an additional second longer (over and above the error margin). So by and large, initial conditions don't seem terribly important and your measurements probably weren't far off the mark. Where your conclusions are different from mine, it is probably due to differences in our platforms or table contents (you didn't describe these, so I'm guessing).

Again, I hope I didn't offend. I have great respect for your opinions.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[JeremyNYC]

Rick,

No, absolutely no offense taken. I didn't think you were attacking me, only that you were pointing out some things I'd not taken into account. Without getting too far into the mutual admiration club, I do want to say that I have the utmost respect for you and your contributions here.

It's interesting to me that my post showed up _after_ your benchmark posts--because I didn't see them before putting that up.

In any case, thanks for putting in the time to run these tests--there's lots of good stuff in here. It'll take me a little time to digest it all, but it looks like your conclusions shed some good light on when to use which method.

Thanks again, and cheers to ya for all the work.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[markphsd]

Hey, I just thought i'd say this...

I use msde and dlookup works with, though I do use it sparingly, on finite tables, and not for counting.

Maybe it works because I'm just special.

Mark P.

Bleh