Major Query Optimiser Weirdness With UDFs And SPs On SQL 2000

There is something very strange going on here. Tested with ADO 2.7 and
MSDE/2000. At first, things look quite sensible.

You have a simple SQL query, let's say

select * from mytab where col1 = 1234

Now, let's write a simple VB program to do this query back to an
MSDE/2000 database on our local machine. Effectively, we'll

rs.open sSQL
rs.close

and do that 1,000 times. We wont bother fetching the result set, it
isn't important in this example.

No problem. On my machine this takes around 1.6 seconds and modifying
the code so that the column value in the where clause changes each
time (i.e col1 = nnnn), doesn't make a substantial difference to this
time. Well, that all seems reasonable, so moving right along...

Now we do it with a stored procedure

create procedure proctest(@id int)
as
select * from mytab where col1 = @id

and we now find that executing

proctest nnnn

1,000 times takes around 1.6 seconds whether or not the argument
changes. So far so good. No obvious saving, but then we wouldn't
expect any. The query is very simple, after all.

Well, get to the point!

Now create a table-returning UDF

create function functest(@id int) returns table as

return
(
select * from mytab where col1 = @id
)

try calling that 1,000 times as

select * from functest(nnnn)

and we get around 5.5 seconds on my machine if the argument changes,
otherwise 1.6 seconds if it remains the same for each call.

Hmm, looks like the query plan is discarded if the argument changes.
Well, that's fair enough I guess. UDFs might well be more expensive...
gotta be careful about using them. It's odd that discarding the query
plan seems to be SO expensive, but hey, waddya expect?. (perhaps the
UDF is completely rebuilt, who knows)

last test, then. Create an SP that calls the UDF

create procedure proctest1(@id int)
as
select * from functest(@id)


Ok, here's the $64,000 question. How long will this take if @id
changes each time. The raw UDF took 5.5 seconds, remember, so this
should be slightly slower.

But... IT IS NOT.. It takes 1.6 seconds whether or not @id changes.
Somehow, the UDF becomes FOUR TIMES more efficient when wrapped in an
SP.

My theory, which I stress is not entirely scientific, goes something
like this:-

I deduce that SQL Server decides to reuse the query plan in this
circumstance but does NOT when the UDF is called directly. This is
counter-intuitive but it may be because SQL Server's query parser is
tuned for conventional SQL i.e it can say

well, I've got

select * from mytab WHERE [something or other]

and now I've got

select * from mytab WHERE [something else]

so I can probably re-use the query plan from last time. (I don't know
if it is this clever, but it does seem to know when two
textually-different queries have some degree of commonality)

Whereas with

select * from UDF(arg1)

and

select * from UDF(arg2)

it goes... hmm, mebbe not.... I better not risk it.

But with

sp_something arg1

and

sp_something arg2

it goes... yup, i'll just go call it... and because the SP was already
compiled, the internal call to the UDF already has a query plan.

Anyway, that's the theory. For more complex UDFs, by the way, the
performance increase can be a lot more substantial. On a big complex
UDF with a bunch of joins, I measured a tenfold increase in
performance just by wrapping it in an SP, as above.

Obviously, wrapping a UDF in an SP isn't generally a good thing; the
idea of UDFs is to allow the column list and where clause to filter
the rowset of the UDF, but if you are repeatedly calling the UDF with
the same where clause and column list, this will make it a *lot*
faster.