Which is faster, Max() or Order By?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to work out the fastest replacement for a DMax()
Does anyone know which is faster?
strSQL = "SELECT TOP 1 " & strField & " FROM " & strDomain & _
" WHERE " & Criteria & " ORDER BY " & strField & " DESC;"
Or:
strSQL = "SELECT Max(" & strField & ") FROM " & strDomain & _
" WHERE " & Criteria & ";"

Here again, which is faster?
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
Or:
Set rst = db.OpenRecordset(strSQL, dbOpenForwardOnly)
 
Max and order by - dont think a lot of difference. i would use Max

dbOpenForwardOnly - is a fastest (but limited capabilities)
 
Thanks.
Do you think I can change the code in the
“Domain Aggregate Functions Replacements†Module
http://www.mvps.org/access/modules/mdl0012.htm

The code reads:
Set rstLookup = dbLookup.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)

strSQL can contain a Max(), Min(),Sum() etc. on a single field.

Can I replace it with:
Set rstLookup = dbLookup.OpenRecordset(strSQL, dbOpenForwardOnly)
 
Hi Joanthan,
i think you can, but for one-row recordset there is no difference how you
open it.
dbOpenForwardOnly brings advantage when you open a recordeset with lot of
records.
 
Thank you for your help !

Alex Dybenko said:
Hi Joanthan,
i think you can, but for one-row recordset there is no difference how you
open it.
dbOpenForwardOnly brings advantage when you open a recordeset with lot of
records.
Click to expand...
 
Back
Top