DCount or OpenRecordset what is better (1 Viewer)

[smig]

what is better to count the number of records, using DCount or OpenRecordset ?
and what is better when I only need to check if there are any records in the query? for this I now use If .RecordCount > 0

 

[the_net_2.0]

to use .recordcount, you have to have a recordset open. it's a property of that object.

dcount should always be faster, and with only one line of code. there is a special case with NULL values though with dcount. see the help menu for that. I think it counts those as well.

 

[smig]

thanks
I looked at he help. it won't count nulls if this is the field I'm counting.

but what is better when I only need to check if there are any records in the query?
isn't DCount require to go all over the query to count, while opening a recordsets only to check if the RecordCount > 0 don't ?

 

[the_net_2.0]

the reason I told you dcount is because it works MUCH faster than anything else you've mentioned if you DO check a field that is required. see what i mean?

thus, if the return of it is more than 0, there are records.

 

[Galaxiom]

I would like to see timed comparisons. I was under the impression that Domain functions opened a recordset to perform the task.

 

[gemma-the-husky]

if its a large recordset, then both dcount and a recordset open will take time

can you not just try and read a single example. ie dlookup - that would be quicker, surely

 

[boblarson]

if its a large recordset, then both dcount and a recordset open will take time

can you not just try and read a single example. ie dlookup - that would be quicker, surely

I don't have specific numbers to give but I am sure that I heard that DCount is much more efficient than DLookup.

As for Recordset, it is a quick check and even Allen Browne has the ELookup substitution for DLookup which uses a recordset and works faster than the DLookup.

So DCount or Recordset - basically your choice. I've used both, depending on my need.

But the If rst.RecordCount > 0 is a good check because even if it is a table type that requires you to move last to get the full count, it will always be either > 0 or = 0 when you have opened it before moving last to get the full count. So if you only need to know if it is greater than zero, there you go.


EDIT: Oh, and if you use DCount, don't provide a field, just use:

If DCount("*","TableOrQueryNameHere", "AnyCriteriaHere") > 0 Then

or without criteria:

If DCount("*","TableOrQueryNameHere") > 0 Then

 

[the_net_2.0]

I would like to see timed comparisons. I was under the impression that Domain functions opened a recordset to perform the task.

for a newb post, they don't need to know this. it's simply irrelevant at this level of skill and desire.

 

[Galaxiom]

for a newb post, they don't need to know this. it's simply irrelevant at this level of skill and desire.

I guess I should have made it clear that I was actually commenting on your post that claimed the DCount was "MUCH faster" than the recordset, rather than answering the original question.

I posted my comment because implicit in your post was the idea that "a single line of code" would automatically be faster, which is not necessarily the case. DCount is a function. Functions call other code and I strongly suspect the DCount function opens a recordset.

Inexperienced developers often confuse the simplicity and convenience of the domain functions with processing efficiency. Comments that suggest a single line of code would undoubtely be faster contribute to that misunderstanding. How often have we seen posters asking why their form takes too long to open only to discover they have used a plethora of domain functions to populate individual textboxes?

In any case I was not aware of any policy here that suggests the discussion in a thread be strictly limited to precisely the original question. Some of the best stuff I have learned here is from the extra comments.

Indeed I believe the comment was completely appropriate. I know I am not alone in thinking this is the way Domain functions work and if we are right then the claim of the superior speed of the DCount would be incorrect. Hence it is a reasonable and intelligent comment that timed results would be interesting. This is just the place where someone could pop up and say they have made such a comparative measurement.

Moreover I don't think it is up to anyone other than the poster to determine (or rather, make assumptions) about their "level of skill and desire". They can take or leave the comments as they wish.

Remember this site is not only used to answer the question but is an ongoing resource used by many beyond our own membership.

When someone makes a claim it is fair, reasonable and appropriate to comment that it might not be as cut and dried as they suggest. I would advise those who cannot bear others questioning their received wisdom should avoid posting here.

 

[MarkK]

This routine times two loops. One uses DCount() and the other opens a recordset and uses Count(). You have to run it to find out. Replace the first line Const TABLE with a table in your database...

Sub DCountVsRecordset()
   Const TABLE = "yourtablehere"
   Const MAX = 400
   Dim clock As Single
   Dim tmp As String
   Dim i As Integer
   
   clock = Timer
   For i = 1 To MAX
      tmp = DCount("*", TABLE)
   Next
   Debug.Print "DCount(): " & Round(Timer - clock, 4)
   
   clock = Timer
   For i = 1 To MAX
      tmp = CurrentDb.OpenRecordset("SELECT Count(*) FROM " & TABLE).Fields(0)
   Next
   Debug.Print "Recordset: " & Round(Timer - clock, 4)
   
   Beep
   Debug.Print "***"
   
End Sub

 

[JANR]

tested it.

DCount(): 0,1875
Recordset: 0,2012

JR

 

[gemma-the-husky]

I see - I thought dcount would have to evaluate a whole domain, toi get a result

if all you wanted to know was whether a domain was empty or not, then if you could use a dlookup to get a (any) single item, that must be quicker. than evaluateing the whole domain.


SO - I just did a test with a very large recordset

total domain 650000 records - a complex subset including a date filter extracts about 3000 records. These results


dcount("*", complex query) took about 10secs

rst.recordcount with a .movelast took about exactly the same time

testing rst.eof (without a move last) took about 3 secs

dlookup ("fieldname",complexquery) took about 3 secs

these results make sense to me - if you just open a large query to review the items, you get an initial buffer of records fairly quickly - but it doesnt fill the whole set unless you choose to go to the last item.


I still struggle to believe that a single dlookup can be slower than alternatives (ANY alternative) - I am sure the code within dlookup must be very efficient. Indeed, if opening a recordset to retrieve a value is more efficient - then I am sure they would have implemented dlookup in that way, and I will continue to use dlookups, as the most simple way to retrieve a single "vectored" value.

however I do see that to retrieve a number of values from a single rst row is going to be quicker than multiple dlookups to the same record.

 

[DCrake]

One comment to make on this subject. When building a recordset to retrieve data or obtain a record count it is far quicker to Select * from table instead of selecting specific fields even if there are a lot of fields in the table. Access has to filter out selected fields prior to building the recordset. Whereas selecting all records this operation is excluded and as such becomes more efficient.

 

[smig]

I guess I should have made it clear that I was actually commenting on your post that claimed the DCount was "MUCH faster" than the recordset, rather than answering the original question.

I posted my comment because implicit in your post was the idea that "a single line of code" would automatically be faster, which is not necessarily the case. DCount is a function. Functions call other code and I strongly suspect the DCount function opens a recordset.

Inexperienced developers often confuse the simplicity and convenience of the domain functions with processing efficiency. Comments that suggest a single line of code would undoubtely be faster contribute to that misunderstanding. How often have we seen posters asking why their form takes too long to open only to discover they have used a plethora of domain functions to populate individual textboxes?

In any case I was not aware of any policy here that suggests the discussion in a thread be strictly limited to precisely the original question. Some of the best stuff I have learned here is from the extra comments.

Indeed I believe the comment was completely appropriate. I know I am not alone in thinking this is the way Domain functions work and if we are right then the claim of the superior speed of the DCount would be incorrect. Hence it is a reasonable and intelligent comment that timed results would be interesting. This is just the place where someone could pop up and say they have made such a comparative measurement.

Moreover I don't think it is up to anyone other than the poster to determine (or rather, make assumptions) about their "level of skill and desire". They can take or leave the comments as they wish.

Remember this site is not only used to answer the question but is an ongoing resource used by many beyond our own membership.

When someone makes a claim it is fair, reasonable and appropriate to comment that it might not be as cut and dried as they suggest. I would advise those who cannot bear others questioning their received wisdom should avoid posting here.

That's was exactly my question
I didn't ask for simplicity, but for faster performance.

One comment to make on this subject. When building a recordset to retrieve data or obtain a record count it is far quicker to Select * from table instead of selecting specific fields even if there are a lot of fields in the table. Access has to filter out selected fields prior to building the recordset. Whereas selecting all records this operation is excluded and as such becomes more efficient.

Another piece of importent information

for a newb post, they don't need to know this. it's simply irrelevant at this level of skill and desire.

just to let you know - I'm using Access for the last 15 years, since Access 2 though my programming level is not as good as many other members here, I'm larning fast
I asked this question because I do know how to use both techniques.
I'm happy to open a thread that make all of us do some thinking

sure, DCount will give a cleaner code.

@Gamma - can you do the same test with rst.recordcount only (not going to the last record)

 

[Galaxiom]

When building a recordset to retrieve data or obtain a record count it is far quicker to Select * from table instead of selecting specific fields even if there are a lot of fields in the table. Access has to filter out selected fields prior to building the recordset. Whereas selecting all records this operation is excluded and as such becomes more efficient.

That seems potentially counter intuitive. I love this site for exactly that kind of surprise.

I wonder if it makes a difference if the recordset source is a table with an index. The index could potentially know the record count better than the table.

Perhaps Microsoft could even use some undocumented interaction with the index for the domain functions.

 

[vbaInet]

I guess we could refine the tests a little.

what is better to count the number of records, using DCount or OpenRecordset ?

For a large domain I will go for Recordset as long as you use Count(*) in the sql of the OpenRecordset command. If you were going to loop through the recordset and count each line then this would be slower than a DCount().

and what is better when I only need to check if there are any records in the query? for this I now use If .RecordCount > 0

Again, I will go for Recordset as it doesn't get populated until you MoveLast. Which is why it is advised that to get a full record count you must MoveLast. Hence, if we don't move last, it buffers only the first couple of records (depending on the size of the domain). I think it buffers up to a max of 2000 (without MoveLast).

I would imagine that a DLookup() is somewhat equivalent to SELECT TOP 1 from a domain. I believe that TOP 1 is slow when compared to how the Recordset buffers. So when comparing DLookup() to Recordset, Recordset wins (in my opinion).

Try these out.

Count comparison:

Function DCountVsRecordset()
   Const iTABLE = "yourtablehere"
   Const iMAX = 8000
   
   Dim db As DAO.Database
   Dim clock As Single
   Dim tmp As String
   Dim i As Integer
   
   clock = Timer
   For i = 1 To iMAX
      tmp = DCount("*", iTABLE)
   Next
   Debug.Print "DCount(): " & Round(Timer - clock, 4)
   
   clock = Timer
  [COLOR=Red][B] Set db = CurrentDb[/B][/COLOR]
   For i = 1 To iMAX
      tmp = db.OpenRecordset("SELECT Count(*) As CountOf FROM " & iTABLE[COLOR=Red][B], dbOpenSnapshot[/B][/COLOR]).Fields(0)
   Next
   Debug.Print "Recordset: " & Round(Timer - clock, 4)
   
   Beep
   Debug.Print "***"
   
End Function

DLookup() vs Recordset

Function DCountVsRecordset()
   Const iTABLE = "yourtablehere"
   CONST iFIELD = "yourfieldhere"
   Const iMAX = 8000
   
   Dim db As DAO.Database
   Dim clock As Single
   Dim tmp As String
   Dim i As Integer
   
   clock = Timer
   For i = 1 To iMAX
      tmp = DLookup(iFIELD, iTABLE)
   Next
   Debug.Print "DLookup(): " & Round(Timer - clock, 4)
   
   clock = Timer
   Set db = CurrentDb
   For i = 1 To iMAX
      tmp = db.OpenRecordset("SELECT " & iFIELD & " FROM " & iTABLE, dbOpenSnapshot).Fields(0)
   Next
   Debug.Print "Recordset: " & Round(Timer - clock, 4)
   
   Beep
   Debug.Print "***"
   
End Function

 

[gemma-the-husky]

That seems potentially counter intuitive. I love this site for exactly that kind of surprise.

I wonder if it makes a difference if the recordset source is a table with an index. The index could potentially know the record count better than the table.

Perhaps Microsoft could even use some undocumented interaction with the index for the domain functions.

thats a good point

given a unique index, idx.distinctcount returns the number of items. That is probably quicker than building a recordset - just tried it.

INSTANT 648224 records!

[mind you - so was dcount("*","sametable") - so dcounts possibly do use indexes if they can!]

 

[smig]

I imagine something like DLookup(TblKey, Tbl) should be fast as it only need the first record it find.

though using DLookup doesn't seems to be intuitive

 

[smig]

Dcount give me an error of Size of field is too small for a long QRYstring
so it's not good for complicate QRYstring.
never tested but I guess DLookup will have the same issue

 

[vbaInet]

I imagine something like DLookup(TblKey, Tbl) should be fast as it only need the first record it find.

though using DLookup doesn't seems to be intuitive

Are you basing that on the test in post #16?