[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/