Jet tables, indexes and expressions

Michel Walsh, Access MVP, have posted an excellent example, which helps to understand how Jet deals with indexes and evaluates expressions:

Have a table, Ds, one field, D, with values for 0 to 9, and a primary key. Have, in a standard module, have:

Public Function ShowMe(Arg As Variant) As Boolean
    MsgBox "me"
    ShowMe = True
End Function

Now, make the query

SELECT d
FROM Ds
WHERE d > 100 AND ShowMe(d)

And you won't get any prompt form ShowMe.

Change the 100 to 8, and you get one prompt.

Change the 100 (or now 8) to 6, and you get three prompts (and, in A2003, a bug in the display of the result, but that is another story).

So, that is conclusive that there is a matter of short cut evaluation in the criteria and it is technically wrong to say that a function call will necessary produce a table scan. If it was true, the ShowMe would run 10 times in all the previous examples.

I have been surprised to see that

SELECT Ds.d
FROM Ds
WHERE showMe(d) AND d > 6

produces the same behavior (I was on the impression that Jet was not very optimized and did not try to re-order the WHERE parts, I was wrong).

So, indexed fields seems to be used first and if other parts are to be evaluated, then the values from these records are brought through the network, unless there are not already in the local cache.