[AccessD] Strange but true

Shamil Salakhetdinov shamil at users.mns.ru
Fri Dec 16 14:46:26 CST 2005


<<<
What in the heck is going on?
>>>
My guess:

iif is interpreted using VBA optimized library and doesn't need to "talk"
to MS Access runtime engine (maybe they even have iif(....) and some other
VBA functions' interpreter inside Jet Engine...

When your function is used they(Jet Engine) "pipe" some data through MS
Access runtime engine to call your function and get back its return value...

It will fly if you manage to not use nor VBA nor your own functions in
queries but that's clear for you of course...

Shamil


----- Original Message ----- 
From: "John Colby" <jwcolby at ColbyConsulting.com>
To: "'Access Developers discussion and problem solving'"
<accessd at databaseadvisors.com>
Sent: Friday, December 16, 2005 10:28 PM
Subject: [AccessD] Strange but true


> I am working on this ... Less than well organized database.  They do a lot
> of outer joins, then use iif(somevar is null,0,Somevar) to get rid of the
> nulls where no record is returned.
>
> I have always read that iifs are a pig so I wrote a simple function to
> return a 0 if the value passed in is null.  I carefully tested this in a
> test stub, timing it with my timer class.  100000 iterations of passing in
a
> null or a 2 caused the iif to take:
>
>  621
>  630
>
> Milliseconds respectively,
>
> Using my function took
>
>  385
>  403
>
> Milliseconds respectively.
>
> So, confident that I could speed up this messy function I went in and
> replaced about 4 different iifs with my function.  Holy crap batman, it
took
> FOREVER to finish, probably 5 times as long as with the original iifs.
>
> The result set returns about 6000 records so I expected it to take almost
> nothing to run the functions themselves.  What in the heck is going on?
>
> This query is a base query with about 6 others outer joined on a text
field.
> The text field itself is indexed, and that of course has nothing to do
with
> why the performance tanked when I replaced the iif with my function.
>
> John W. Colby
> www.ColbyConsulting.com
>
> Contribute your unused CPU cycles to a good cause:
> http://folding.stanford.edu/
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com




More information about the AccessD mailing list