Solved

RecordSet Vs Domain aggregate functions

Posted on 2006-07-04
6
517 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Some sers suddenly getting error popup msg 28 88
Access Update Query 1 20
Create macro from runcode 30 24
Part 2 to aggregate query solved qtn 5 17
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

776 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