Solved

RecordSet Vs Domain aggregate functions

Posted on 2006-07-04
6
513 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

744 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

18 Experts available now in Live!

Get 1:1 Help Now