Solved

RecordSet Vs Domain aggregate functions

Posted on 2006-07-04
6
516 Views
Last Modified: 2008-02-01
Is there a significant difference in speed by using record sets to get specific data from table rather than using Domain aggregate functions such as Dlookup or Dcount?
0
Comment
Question by:Basicfarmer
6 Comments
 
LVL 35

Accepted Solution

by:
Raynard7 earned 25 total points
ID: 17039783
It depends on how much data you wish to retreive - if it is for one piece of data ie a simple count from a compiled query (saved in the database) then a dcount would be fastest;

If you are looking at retreiving more than one piece of data (ie counts for individuals; or names) then there is a significant saving in using a recordset;

This is because most of the time is taken in opening the connection to the table - looping through the rows and comparing values;

most of this is only done once per query - so if you are looking for multiple bits of data using aggregate functions you are repeating work you have already done

But it is slightly faster to use a dcount than recordset for one piece of data
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17041559
Hi all

Well - there's an issue or two to consider I reckon.
If we're talking about just counting then there shouldn't really be anything in it.

DCount("*","TableName")
and
CurrentDB.OpenRecordset("SELECT COUNT(*) FROM TableName")(0)
should be very much the same.


However when looking up field values you have to consider if there's an index on the searched field(s).
If so then I've found the methods to be virtually identical these days.

Naturally (as stated) - if you're wanting to hit more than one value - then a recordset is generally the way to go.
But on a one hit a single value - my playings in the recent past have determined little difference (the variation fluctuates minutely between them... but never really anything in it).

However - if there is no index (and you're dealing with considerably large record sources) - then a recordset can be pretty slow.
Whereas a DLookup (despite its history of knocks) now does pretty damn well (on local or linked tables).
The difference seemingly again becomes insignificant on smaller numbers of records.

So - is there a moral to this story?
Yep - index your tables appropriately :-D
0
 

Author Comment

by:Basicfarmer
ID: 17063265
Ok here is what I am looking at doing:

SQL = "SELECT PartNumber,Price,Discount,Warehouse,Desciption FROM PartTable WHERE ID = " & me.partList & ""

set rs = currentdb.OpenRecordSet(SQL, dbOpenDynaset)

me.partNumber = rs.fields("PartNumer")
"
"
"
"

Rather than

Dlookup("PartNumber", "PartTable", "ID = " & me.expenseList & "")
"
"
"
"

Are  there any advantages or disadvantages either way?

What about ADO?

I am trying to wean myself from Domain aggregate functions as I am learning VB and started with VBA in Access. I would like to eventually convert my app into VB.

Any Advice?

0
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 25 total points
ID: 17063328
The advice reamins much the same as you've already had.
It's perfetly valid to continue using the Domain Aggregate functions in code - but if your eventual target is moving to VB then yes - you'd generally start looking at moving over. (No choice of course once you're over there - other than to start referencing an Access app object in code which would be mad)

Technically, if you're wanting to work with Jet data, then DAO would be the technology of choice (generally being that little bit faster with Jet data).
But from a platform like VB - *many* would choose ADO I reckon.
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 17258931
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
    Split: Raynard7 {http:#17039783} & LPurvis {http:#17041559}

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

jjafferr
EE Cleanup Volunteer
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now