>>>There can be situations where a UDF can simplify what would otherwise be a very complex query.
>
>>>If it looks like a UDF has merit, one tests it with a representative data set; if it performs satisfactorily (considering the alternatives), then one incorporates it.
>
>I've never seen a situation where I couldn't come up with a set of SQL queries (i.e. the base query and a subsequent SELECT or UPDATE) that didn't out perform a UDF in a SQL statement. I not a fan of absolute statements, but in my experience with Fox, this one stands.
>
>I'd be interested in seeing any examples of a UDF in a SQL SELECT that *is* fast.

I could probably give you an example but including the 450MB of production data to prove my point is not practical. And most queries don't have the luxury of being repeated a couple of times so that at some point all the data "may" be in memory. It's the first pass that says it all.

Frankly, "Rushmore" is pretty dumb; in many cases, a SEEK and SCAN ... WHILE is also preferable.

If Rushmore maintained Query statistics like most sophisticated engines do (including JET) there "might" be an outside chance that one day it could beat "my UDF", but while Rushmore spends most of it's time "thinking" about an access strategy before it even attempts to carry one out (and then decides "wrong"), the UDFs and SCANs will continue to have a place.

A UDF can be as simple as a "SEEK"; in the same way that compound IF's can short-circuit, so can UDF's short-circuit a Query ... something that Rushmore is not very good at. And whereas Rushmore frequently has to load entire indexes and/or data sets, again, that is NOT the case with SEEK type UDF's. You might be able to identify a "class" of UDF's that are not (generally) efficient for Queries, but it's reaching to say ALL UDF's are inefficient (IMO).