Function Vs Expressions

  • Thread starter Thread starter Jose Perdigao
  • Start date Start date
J

Jose Perdigao

I have queries where I added fields with many calculations.
My question is.
What is it faster? Create functions in vba and then use it in the queries or
using expressions in the query?

Thanks a lot,
José Perdigão
 
In Jet 4 (Access 2000 and later), user-defined functions are generally the
slowest to execute (other things being equal.)

Built-in functions are generally okay, though the domain aggregate functions
are particularly slow.

The ideal query uses only SQL rather than function calls. For example:
MyField Is Null
is better than using a VBA function:
IsNull(MyField)
for many reasons, e.g.:
a) works even with broken references
b) much more efficient if you link to a different database, or use
pass-through queries
c) probably (slightly) more efficient.
 
I don't understand why domain aggregate functions is slower than
expressions, they should be faster because is aggregated in access.
 
Jose said:
I don't understand why domain aggregate functions is slower than
expressions, they should be faster because is aggregated in access.
Click to expand...

There is overhead associated with the use of a domain aggregate function. When
used singularly in code or a form/report expression this overhead is not an
issue. When used in a loop or a query that overhead is incurred for each and
every row or loop iteration and then it becomes a significant performance
problem.
 
So, if I understood, is good idea using domain aggregate function in the
form to entry data and from the calcultaion result store it in a field. make
sense this ratiocination?

Normally, i don't store the calculations results to save memory, but I
think, I must change this ideia.

Thanks
José Perdigão
 
Don't change the idea. Storing calculated values is never a good idea. What
everyone is trying to explain is the order of preference for which method you
use.
As Allen Browne said, pure SQL provides the best performance. User defined
functions are best used when the evaluation of your data would be too complex
for an expression. I tend to use UDFs when an expression would be difficult
to understand and maintain. And the last (and slowest) choice is Domain
Aggregate Functions.

For example, just recently, I modified the code for a query that had up to
six DLookUps for each row. It took about two minutes to return a recordset
from a table with less than 7000 rows. By restructuring the query's SQL and
removing the DLookUps, the recordset now returns in under two seconds.
 
Thanks, Your answer was good to don't change my fundamental law.

I know, some time we change code, statement and the performance increase.



But, also I listen to increase the speed, store calculations results. I know
it's easier, but is not programming and the consistency is lower.

jose perdigao
 
Hi,


Hum... not always, not always. I think I should start with experimental
result:

================
TestDCount
SubQuery 1.1497770068656753748608731538
DCount 0.0300636294286107324637644911
================


which clearly shows that DCount is two orders of magnitude FASTER than the
sub-query!

Sure, you now want to see the code.It uses a performance counter to
accurately time code execution of two SQL statements, equivalent, but one
using a sub-query and the other a DCount:

====================
Private Type LARGE_INTEGER
lowpart As Long
highpart As Long
End Type

Private Declare Function QueryPerformanceCounter Lib "kernel32"
(lpPerformanceCount As LARGE_INTEGER) As Long
Private Declare Function QueryPerformanceFrequency Lib "kernel32"
(lpFrequency As LARGE_INTEGER) As Long

'-------------------------------------------
Public Sub TestDCount()
Dim freq As LARGE_INTEGER
Dim starting As LARGE_INTEGER
Dim Ending As LARGE_INTEGER
Dim dfreq As Variant

QueryPerformanceFrequency freq
dfreq = LargeToDec(freq)

QueryPerformanceCounter starting
Set rst = CurrentProject.Connection.Execute("SELECT a.*, (SELECT
COUNT(*) FROM Iotas As b WHERE b.Iota>a.Iota) FROM Iotas as a;")
QueryPerformanceCounter Ending

Debug.Print "SubQuery ", (LargeToDec(Ending) - LargeToDec(starting)) /
dfreq

QueryPerformanceCounter starting
Set rst = CurrentProject.Connection.Execute("SELECT *, DCOUNT('*',
'Iotas', 'Iota>=' & Iota) FROM Iotas ;")
QueryPerformanceCounter Ending

Debug.Print "DCount ", (LargeToDec(Ending) - LargeToDec(starting)) /
dfreq

End Sub
'---------------------------------
Public Function LargeToDec(Arg As LARGE_INTEGER) As Variant

Dim temp As Variant
temp = 4 * CDec(1073741824) ' 2 ^ 32

If Arg.lowpart > 0 Then
LargeToDec = Arg.lowpart + Arg.highpart * temp
Else
LargeToDec = temp + Arg.lowpart + Arg.highpart * temp
End If

End Function

========================



where table Iotas is a large table (1 000 000 records) with one field,
iota, the primary key. With a very small table, say with one record, you
will probably get the reverse, since the string handling will kill the
DCount (its third argument involves a string creation, which is not the most
efficient operation in itself).



Now, if you wonder why DCount is almost 40 times faster than the subquery...
I would *guess* that it is because DCount is evaluated only when required,
on demand, while the sub-query is unconditionally evaluated.



Hoping it may help,
Vanderghast, Access MVP
 
Isn't DCOUNT() evaluated once whereas (SELECT..) is evaluated n times ?

See various posts for returning random values based on using the SELECT
clause vs using the WHERE clause.

A description by the QueryOptimiser on SQL Server tables should be
definitive.

Regards John
 
Hi,


since


DCOUNT('*', 'Iotas', 'Iota>=' & Iota)


has, indeed, a variable third argument, it is not a constant. Both queries
produce the same result, as you can test by walking through the recordset,
and they count how many row have their iota, in the table, >= the iota
value for a given row. And since each row has a different value of iota, the
DCOUNT third argument would be different for each row.


I don't think MS SQL Server knows about DCOUNT, so its optimizer cannot
really be of much use about its "timing".




Vanderghast, Access MVP
 
Hi, risking carrying post beyond reasonable limits

is that
DCOUNT('*', 'Iotas', 'Iota>=' & a.Iota) or
DCOUNT('*', 'Iotas', 'Iota>=' & b.Iota) or eval to
DCOUNT('*', 'Iotas', 'Iota>=' & 0)
Click to expand...
?

;-) John
 
Hi,


It is none of them, since the FROM clause does not define any alias, a or b.
You can try on a small table,

SELECT *, DCOUNT('*', 'Iotas', 'Iota>=' & Iota) FROM Iotas ;


where you can replace Iotas, the table, and Iota, one of its field name, by
whatever you have on hands.


If you want to use an alias, it can be like:

SELECT x.*, DCOUNT('*', 'Iotas', 'Iota>=' & x.Iota) FROM Iotas as x;



or, if you want a more general construction:

SELECT x.*, DCOUNT('*', 'Iotas', 'Iota>=' & x.Iota) FROM
OtherTableWithAIotaField as x;


which DCOUNT the number of rows in Iotas (having their Iota value >=
x.Iota), for any row from x.



Vanderghast, Access MVP
 
Back
Top