Why Is It Slower?

[Chaplain Doug]

I thought that I would make my code more efficient by
using built-in functions. However, in this case shorter
code using built-in function was slower why?

*****
It Was
*****
If TN = "F" Then
TableName = "[Female Cycles]"
Else
TableName = "[Male Cycles]"
End If
Criteria = "[SAPCycle]= " & Str(CN)
strSQL = "SELECT * FROM " + TableName + " WHERE " &
Criteria
Set rst = dbs.OpenRecordset(strSQL)
If rst.RecordCount = 1 Then 'Found a matching record
rst.MoveFirst
With rst
CycleStart = ![StartDate]
End With
Else
CycleStart = "1/1/2000"
End If

*******
I changed it to (which was slower)
*******
If TN = "F" Then
TableName = "[Female Cycles]"
Else
TableName = "[Male Cycles]"
End If
Criteria = "[SAPCycle]= " & Str(CN)
CycleStart=Nz(DLookup
("[CycleStart]",TableName,Criteria),#1/1/2000#)

 

[Marshall Barton]

I thought that I would make my code more efficient by
using built-in functions. However, in this case shorter
code using built-in function was slower why?

*****
It Was
*****
If TN = "F" Then
TableName = "[Female Cycles]"
Else
TableName = "[Male Cycles]"
End If
Criteria = "[SAPCycle]= " & Str(CN)
strSQL = "SELECT * FROM " + TableName + " WHERE " &
Criteria
Set rst = dbs.OpenRecordset(strSQL)
If rst.RecordCount = 1 Then 'Found a matching record
rst.MoveFirst
With rst
CycleStart = ![StartDate]
End With
Else
CycleStart = "1/1/2000"
End If

*******
I changed it to (which was slower)
*******
If TN = "F" Then
TableName = "[Female Cycles]"
Else
TableName = "[Male Cycles]"
End If
Criteria = "[SAPCycle]= " & Str(CN)
CycleStart=Nz(DLookup
("[CycleStart]",TableName,Criteria),#1/1/2000#)

The knee jerk response to this question is that the Domain
Aggregate functions are always slower. However, several
tests in the later versions of Access have demonstrated that
it isn't true all the time. Even when they are slower, it's
typically not noticable.

Maybe we need to analyze why you say it is slower, how much
slower and what else is going on beyond the code that you
posted. Is that code executed inside a loop? or as part of
a continuous (sub)form activity?

 

[Chaplain Doug]

I ran tests using the Timer function just to see the time
difference. In each case I called the functions (in a
loop) 100 times and compared the net time required. The
code with DLookup or DCount was consistently 0.3 seconds
longer in execution. This test is valid as in my real
program these functions will be called for each inmate I
process each morning (1,500 inmates). Hence there will be
a cummulative savings or loss in the time required to do
the daily update. This is not real critical, as the
operation is only performed once per day.

P.S. I am still haunted by the time a graduate assistant
turned to me and said, "I rewrote your program and it runs
ten times faster!" Of course my engineering partner
said, "Yeah, but his faster program bombs half of the
time."

-----Original Message-----

I thought that I would make my code more efficient by
using built-in functions. However, in this case shorter
code using built-in function was slower why?

*****
It Was
*****
If TN = "F" Then
TableName = "[Female Cycles]"
Else
TableName = "[Male Cycles]"
End If
Criteria = "[SAPCycle]= " & Str(CN)
strSQL = "SELECT * FROM " + TableName + " WHERE " &
Criteria
Set rst = dbs.OpenRecordset(strSQL)
If rst.RecordCount = 1 Then 'Found a matching record
rst.MoveFirst
With rst
CycleStart = ![StartDate]
End With
Else
CycleStart = "1/1/2000"
End If

*******
I changed it to (which was slower)
*******
If TN = "F" Then
TableName = "[Female Cycles]"
Else
TableName = "[Male Cycles]"
End If
Criteria = "[SAPCycle]= " & Str(CN)
CycleStart=Nz(DLookup
("[CycleStart]",TableName,Criteria),#1/1/2000#)

The knee jerk response to this question is that the Domain
Aggregate functions are always slower. However, several
tests in the later versions of Access have demonstrated that
it isn't true all the time. Even when they are slower, it's
typically not noticable.

Maybe we need to analyze why you say it is slower, how much
slower and what else is going on beyond the code that you
posted. Is that code executed inside a loop? or as part of
a continuous (sub)form activity?

 

[Tim Ferguson]

P.S. I am still haunted by the time a graduate assistant
turned to me and said, "I rewrote your program and it runs
ten times faster!" Of course my engineering partner
said, "Yeah, but his faster program bombs half of the
time."

This is the fastest square root algorithm in the world:

Public Function FastSquareRoot(SomeNumber As Double) _
As Double

FastSquareRoot = 2.5943784

End Function

It is not very accurate for some values of SomeNumber, but you should just
see those benchmark figures!

All the best

Tim F

 

[Marshall Barton]

I ran tests using the Timer function just to see the time
difference. In each case I called the functions (in a
loop) 100 times and compared the net time required. The
code with DLookup or DCount was consistently 0.3 seconds
longer in execution. This test is valid as in my real
program these functions will be called for each inmate I
process each morning (1,500 inmates). Hence there will be
a cummulative savings or loss in the time required to do
the daily update. This is not real critical, as the
operation is only performed once per day.

I'd be real careful drawing any conclusions from the Timer
function. Its resolution may not be good enough to tell the
difference on any test that takes less than a few minutes.
And, don't forget that the order of the tests can make a
difference because the first time the data is accessed, it
will be in one cache or another for the remainder of the
texts.

P.S. I am still haunted by the time a graduate assistant
turned to me and said, "I rewrote your program and it runs
ten times faster!" Of course my engineering partner
said, "Yeah, but his faster program bombs half of the
time."

Definitly something to keep in mind, but a few hundred
milliseconds on a once a day activity is hardly going to
make a difference.
--
Marsh
MVP [MS Access]

-----Original Message-----

I thought that I would make my code more efficient by
using built-in functions. However, in this case shorter
code using built-in function was slower why?

*****
It Was
*****
If TN = "F" Then
TableName = "[Female Cycles]"
Else
TableName = "[Male Cycles]"
End If
Criteria = "[SAPCycle]= " & Str(CN)
strSQL = "SELECT * FROM " + TableName + " WHERE " &
Criteria
Set rst = dbs.OpenRecordset(strSQL)
If rst.RecordCount = 1 Then 'Found a matching record
rst.MoveFirst
With rst
CycleStart = ![StartDate]
End With
Else
CycleStart = "1/1/2000"
End If

*******
I changed it to (which was slower)
*******
If TN = "F" Then
TableName = "[Female Cycles]"
Else
TableName = "[Male Cycles]"
End If
Criteria = "[SAPCycle]= " & Str(CN)
CycleStart=Nz(DLookup
("[CycleStart]",TableName,Criteria),#1/1/2000#)

The knee jerk response to this question is that the Domain
Aggregate functions are always slower. However, several
tests in the later versions of Access have demonstrated that
it isn't true all the time. Even when they are slower, it's
typically not noticable.

Maybe we need to analyze why you say it is slower, how much
slower and what else is going on beyond the code that you
posted. Is that code executed inside a loop? or as part of
a continuous (sub)form activity?

 

[Dirk Goldgar]

I thought that I would make my code more efficient by
using built-in functions. However, in this case shorter
code using built-in function was slower why?

*****
It Was
*****
If TN = "F" Then
TableName = "[Female Cycles]"
Else
TableName = "[Male Cycles]"
End If
Criteria = "[SAPCycle]= " & Str(CN)
strSQL = "SELECT * FROM " + TableName + " WHERE " &
Criteria
Set rst = dbs.OpenRecordset(strSQL)
If rst.RecordCount = 1 Then 'Found a matching record
rst.MoveFirst
With rst
CycleStart = ![StartDate]
End With
Else
CycleStart = "1/1/2000"
End If

*******
I changed it to (which was slower)
*******
If TN = "F" Then
TableName = "[Female Cycles]"
Else
TableName = "[Male Cycles]"
End If
Criteria = "[SAPCycle]= " & Str(CN)
CycleStart=Nz(DLookup
("[CycleStart]",TableName,Criteria),#1/1/2000#)

One possibility is that, at least as you posted it, your "was" code is
using an already created and initialized database object, dbs, to run
its query against. I can only speculate about the internal
implementation of DLookup, but conceivably it does the equivalent of
"Set dbs = CurrentDb()" each time it is called. In your posted code,
this step has already been done.

Of course, it's possible that you actually are including the "Set dbs ="
step in your time trials, in which case this theory of mine is all wet.

 

[Larry Linson]

. . . In each case I called the functions
(in a loop) 100 times and compared
the net time required. The code with
DLookup or DCount was consistently
0.3 seconds longer in execution. . . .

0.3 seconds longer than _what_? 0.4 compared to 0.1 might be significant if
the timer were that accurate, which it likely is not. 10 minutes 0 seconds
versus 10 minutes 0.3 seconds is likely within the timer error. But, as you
were already warned, the timer is not very accurate, and it is difficult to
determine whether something else may be running in Windows.

In the past, some of us might have said, "avoid the Domain Aggregate
Functions because they don't make use of indexes". But, over time, there's
been evidence that they do, somewhere between rarely and often, appear to
take advantage of the indexes. So, currently, those same some of us might
say, "the Domain Aggregate functions don't always make the best use of
indexes".

They really were not intended to be used repetitively, and the more
experienced among us may tend to "intuitively" avoid them, even if we are
aware that the performance difference is not as great as it might have been
back in Version 2.0, or not as great as we _thought it would be_ back then.

Larry Linson
Microsoft Access MVP

 

[TC]

You need to be careful before you conclude too much from benchmarks like
that. The first loop or two of the first method that you try, might get the
relevant data directly from disk, but all following loops (for that or any
other method) might get it from a windows system buffer - completely
bypassing the time intensive physical disk reads.

For that reason, if I am testing method A versus method B, I never just run
method A, then method B then compare the results. I always run method A,
then B, >then A again, then B again<. And if it is really important to get
it right, I sometimes run other combinations, and/or, reboot the PC between
each run. Only then can you eliminate the effect of system disk buffering,
on the results.

HTH,
TC

I ran tests using the Timer function just to see the time
difference. In each case I called the functions (in a
loop) 100 times and compared the net time required. The
code with DLookup or DCount was consistently 0.3 seconds
longer in execution. This test is valid as in my real
program these functions will be called for each inmate I
process each morning (1,500 inmates). Hence there will be
a cummulative savings or loss in the time required to do
the daily update. This is not real critical, as the
operation is only performed once per day.

P.S. I am still haunted by the time a graduate assistant
turned to me and said, "I rewrote your program and it runs
ten times faster!" Of course my engineering partner
said, "Yeah, but his faster program bombs half of the
time."

-----Original Message-----

I thought that I would make my code more efficient by
using built-in functions. However, in this case shorter
code using built-in function was slower why?

*****
It Was
*****
If TN = "F" Then
TableName = "[Female Cycles]"
Else
TableName = "[Male Cycles]"
End If
Criteria = "[SAPCycle]= " & Str(CN)
strSQL = "SELECT * FROM " + TableName + " WHERE " &
Criteria
Set rst = dbs.OpenRecordset(strSQL)
If rst.RecordCount = 1 Then 'Found a matching record
rst.MoveFirst
With rst
CycleStart = ![StartDate]
End With
Else
CycleStart = "1/1/2000"
End If

*******
I changed it to (which was slower)
*******
If TN = "F" Then
TableName = "[Female Cycles]"
Else
TableName = "[Male Cycles]"
End If
Criteria = "[SAPCycle]= " & Str(CN)
CycleStart=Nz(DLookup
("[CycleStart]",TableName,Criteria),#1/1/2000#)

The knee jerk response to this question is that the Domain
Aggregate functions are always slower. However, several
tests in the later versions of Access have demonstrated that
it isn't true all the time. Even when they are slower, it's
typically not noticable.

Maybe we need to analyze why you say it is slower, how much
slower and what else is going on beyond the code that you
posted. Is that code executed inside a loop? or as part of
a continuous (sub)form activity?