Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

RecordSet Vs Domain aggregate functions

Posted on 2006-07-04
6
Medium Priority
?
526 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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.
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…

718 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