UDF's vs Procs

Question

UDF's vs Procs

Viewing 8 posts - 31 through 37 (of 37 total)

You must be logged in to reply to this topic. Login to reply

well0549 SSCrazy Points: 2259 More actions · Answer 1

Does this mean that for some more complicated queries a SPROC is faster and for the very simple basic selact an UDF is faster or is a SProc ALWAYS faster ???????

Antares686 SSC Guru Points: 125444 More actions · Answer 2

Well, found last night something I forgot I had. I haven't had time to read thru it yet but I found the UDF whitepaper doc that I downloaded from the MS website. Anyway for those interested it does have a bit on what SPs are good canidates to be rewritten as UDFs however doesn't go into the nuts and bolts of what is going on and how to determine which is better option. So for those of you interested here is the whitepaper (hopefully they won't move again)

http://support.microsoft.com/default.aspx?scid=/support/sql/content/2000papers/User_defined_functions.asp

well0549 SSCrazy Points: 2259 More actions · Answer 3

Thanks for the tip Antares.

But beware, the guy who wrote the article is probably the guy who programmed them in SQL..... (That's why he is so happy about it)

Me as a trouble shooter immediately took an example and executed it in the Query analyzer..... (this is the example from the article)

The example was :

use pubs

go

create function MyDateFormat(@indate datetime, @Separator char(1)='-')

returns nchar(20)

as

begin

return

convert(nvarchar(20),datepart(dd, @indate))

+ @Separator

+ convert(nvarchar(20), datepart(mm, @indate))

+ @Separator

+ convert(nvarchar(20), datepart(yy, @indate))

end

go

-- Invocation

select ord_num, dbo.MyDateFormat(ord_date, ':')

from sales

go

My queries were :

select ord_num, dbo.MyDateFormat(ord_date, ':')

from sales

go

select ord_num,convert(nvarchar(20),datepart(dd, ord_date))

+ ':'

+ convert(nvarchar(20), datepart(mm, ord_date))

+ ':'

+ convert(nvarchar(20), datepart(yy, ord_date))

from sales

go

First I noticed no difference at all in speed ... well true.... the UDF was actually faster....... ( But this was with a 'cheech and chong' datababase with only 21 records)

so I added a couple of records.

After adding 2000 records I could see the Function used more processor time but was faster (???)) but never mind that

Next I Added 20000 records

And the statistcis were .....

_______CPU___Reads___Duration

UDF____741___254_____4036

SPROC__180___282_____3216

So the sproc was much faster and less resource intensive. Meaning more scalable.

Then I Added 40000 records....

And the statistcis were .....

_______CPU____Reads______Duration

UDF____1722____617________9733

SPROC__421_____615________7480

So the SPROC outperforms the UDF by far.

making my opinion about a UDF stronger.

Whenever I see a UDF now I am really convinced it is a 'cheech and chong' solution.

Meaning not programmed by anyone who had serious performance considerations in mind when programming the damn thing..

And also the article however VERY interesting covers functionality we should not use .....( but thanks for the tip anyway...)

Edited by - well0549 on 02/11/2003 05:57:36 AM

Edited by - well0549 on 02/11/2003 06:00:22 AM

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Thanks for testing the examples. I just breifly scanned. This of course has been the only real documentation on the MS site about UDFs. Otherwise it is still lacking and thus it is up to all of us to beat it into the ground and see what comes out of it.

Crispin Proctor SSCoach Points: 18161 More actions · Answer 5

Well0549,

If you call a function as part of a select, does the function not get called once for each record? ie: You called the function 20000 times?

This would be slow. Try adding the proc into a while loop and call it 20000 times. I am sure it would be slower than the UDF. This is the situation in which I am using functions.

I have a while loop with lots of code and execs in it. When I change them to functions, the loop does run faster.

Am I missing the point of your test?

Surly you could make anything run slowly if you used it in the wrong context.

Cheers,

Crispin

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

well0549 SSCrazy Points: 2259 More actions · Answer 6

Crappy,

You are right it is called for every record.

But this is how I see them all the time.

If your function is different and is not used in a select at all I guess

it would not harm your application. I guess you are right there.

But when you do use a UDF combined with a select it will slow down

every query.

Crispin Proctor SSCoach Points: 18161 More actions · Answer 7

ya, this all makes sense now. This is why people say that UDFs are slow. I am sure in their tests, they use it as part of a select.

In all honesty, you call anything 20000 times it'll slow down. 🙂

Where I am going to use it it shouldn't (Crossed fingures)

Wait and see.....

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

paulhumphris Hall of Fame Points: 3496 More actions · Answer 8

There is a good article on http://www.sqlmag.com regarding UDF performance.

InstantDoc #25630

....but you have to subscribe to the magazine to view the article.