asked on

RecordSet Vs Domain aggregate functions

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?

ASKER CERTIFIED SOLUTION

Raynard7

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Leigh Purvis

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

Basicfarmer

ASKER

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?

SOLUTION

Leigh Purvis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jjafferr

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