Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

RecordSet Vs Domain aggregate functions

Posted on 2006-07-04
6
Medium Priority
?
530 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
5 Comments
 
LVL 35

Accepted Solution

by:
Raynard7 earned 100 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 100 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

877 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