473,554 Members | 3,199 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.
Jul 20 '05 #1
3 2344
Andrew Mayo (aj****@my-deja.com) writes:
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)


The reason why the call to the UDF takes longer time is simple,
and you would have obtained the same result if you had called the
bare SQL statement with different values for 1234.

To wit, a table-valued UDF is not really a function: it's a macro, so
when you call it on your own, this is the same as invoking the naked
SQL statement.

Here, this costs you performance, but normally this works to your
advantage. You use the table-valued UDF in a query, and the optimizer
can then work with the expanded, potentially creating a better plan
than if had to first had to evaluate the UDF on its own.

A good way to illustrate this, is to write three functions, one of
each kind that all return the value of @@nestlevel.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
Erland Sommarskog <so****@algonet .se> wrote in message news:<Xn******* **************@ 127.0.0.1>...
[snip]
The reason why the call to the UDF takes longer time is simple,
and you would have obtained the same result if you had called the
bare SQL statement with different values for 1234.

Hmm. Interesting. But I did state in the original post that I had also
tried the bare SQL statement with *different* values for the column
value and that this does *not* affect the execution time. This would
appear to contradict your expected observation.

I have done quite a bit of testing with more complex queries and again
the cost of the raw query does not seem to be affected significantly
by changing the substituted argument (I have only done this for the
one argument case)

The most complex query I have tested involves several joins, has a
CASE statement, and has the argument parametrically substituted in
four places within the query. The execution time for this query is
around 8 seconds per 100 iterations running on the local server. This
figure is almost constant, regardless of the parameter value, which is
numeric.

If I wrap this into a UDF, it also takes 8 seconds for 100 iterations,
UNLESS I keep the argument the same. If I do that, execution time
drops to 0.4 seconds.

If I wrap it into an SP, execution time drops to 0.4 seconds
regardless of the passed-in argument. If I then call the UDF from the
SP, the execution time remains at 0.4 seconds regardless of the
argument.

However, if a UDF is essentially a macro (and unfortunately lacking
the source code for SQL Server I have no idea how they are
implemented), then I can see that effectively each time the UDF is
invoked, if the arguments change, then the UDF is effectively rebuilt
- presumably, this is equivalent to recompiling a stored procedure -
but if the UDF is already inside a precompiled SP, then this process
does not need to occur. That would make sense.
Jul 20 '05 #3
Andrew Mayo (aj****@my-deja.com) writes:
Hmm. Interesting. But I did state in the original post that I had also
tried the bare SQL statement with *different* values for the column
value and that this does *not* affect the execution time. This would
appear to contradict your expected observation.
It could be that the single SQL statement is autoparameteriz ed, but
when it is packaged in a UDF it is not.
However, if a UDF is essentially a macro (and unfortunately lacking
the source code for SQL Server I have no idea how they are
implemented), then I can see that effectively each time the UDF is
invoked, if the arguments change, then the UDF is effectively rebuilt
- presumably, this is equivalent to recompiling a stored procedure -
but if the UDF is already inside a precompiled SP, then this process
does not need to occur. That would make sense.


Not that this only apply to inlined table function. If you were to
put the SELECT in a multi-statement UDF, I predict that the execution
time is the same as for the stored procedure, or possibly somewhat
higher, since there is some overhead for the table variable.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
5338
by: Tim Fountain | last post by:
We've recently enabled slow query logging on a server and it's proving interesting seeing which queries are bogging things down. This one is puzzling me a little: SELECT articleid, type, authorid, authorname, text, posted FROM comments WHERE status = 'normal' ORDER BY posted DESC LIMIT 5; The purpose of this query is to list the five...
9
2756
by: Ed_No_Spam_Please_Weber | last post by:
Hello All & Thanks in advance for your help! Background: 1) tblT_Documents is the primary parent transaction table that has 10 fields and about 250,000 rows 2) There are 9 child tables with each having 3 fields each, their own PK; the FK back to the parent table; and the unique data for that table. There is a one to many relation...
6
1893
by: Steven D.Arnold | last post by:
I have a query which does not use column indexes that it should use. I have discovered some interesting behaviors of Postgres which may indicate a bug in the database's query planning. Take a look at the query below. There is a btree index on both m.account_id and a.account_id. Query (1) does not use the index on the messages table,...
7
1572
by: Rhino | last post by:
I am updating some Java UDFs from DB2GENERAL to DB2JAVA as suggested in the manuals for DB2 Version 8 but I'm having problems with setSQLstate() and setSQLmessage(). If I'm reading the manuals correctly, they are only supported in UDFs that use DB2GENERAL. Is that right? If it is, is there any equivalent to these methods for DB2JAVA UDFs?...
0
1549
by: Bruce B | last post by:
Hi group, I'm experiencing some extreme weirdness over the last week with IIS related to it's Mappings and how .NET is behaving. I can't explain the behavior as it seems very random. Our app has about 50 file extensions that we map in IIS and handle in our HTTPHandler to catch the reference and then lookup the correct content from a...
3
1572
by: serge | last post by:
How do I determine which method I should use if I want to optimize the performance of a database. I took Northwind's database to run my example. My query is I want to retrieve the Employees' First and Last Names that sold between $100,000 and $200,000. First let me create a function that takes the EmployeeID
5
1691
by: David Thielen | last post by:
Hi; I am creating png files in my ASP .NET app. When I am running under Windows 2003/IIS 6, the file is not given the security permissions it should have. It does not have any permission for several users that the directory it is in has, including IUSR_JASMINE (my system is named jasmine). Here is the weird part. In my aps .net code I...
3
4132
by: Martini | last post by:
Hello all. I have quite disturbing situation where I am not happy about the way how SQL handles the query. Situation is related to using user function in INNER JOIN select. Although the problem occured in more complex situation the query can be simplified to following example with same results: There is an user function, that can be as...
6
4843
by: Carsten | last post by:
Hello Folks, I encountered a problem with SQL server 2000 and UDFs. I have a scalar UDF and a table UDF where I would like the scalar UDF to provide the argument for the table UDF like in: SELECT * FROM
0
7783
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8019
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7542
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7873
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5143
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3534
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2007
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1115
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
825
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.