User Defined Functions Investigation

Question

Post reply

User Defined Functions Investigation

Viewing 8 posts - 46 through 52 (of 52 total)

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

Sergiy SSC Guru Points: 110206 More actions · Answer 1

I take your point.

I built another test:

CREATE TABLE #Names (OName sysname COLLATE SQL_Latin1_General_CP1_CI_AS)

INSERT INTO #Names

select o.name from sysobjects o, sysobjects o1

-- (839056 row(s) affected)

SELECT Object_ID(OName) as [Id] from #Names

-- run time 41 sec.

select Id from sysobjects inner join #Names on OName = Name

-- run time 12 sec.

--DROP TABLE #Names

Shoul we now avoid to use function Object_Id() ?

_____________
Code for TallyGenerator

Greg Grow SSCrazy Points: 2386 More actions · Answer 2

I understand these tests that we are performing, and I agree that it is good to do these. But I am still having a hard time understanding the basics of UDFs vs. inline SQL with regards to performance. What I do understand is that SQL Server uses an internal cursor with UDFs and inline SQL does not. Also, SQL Server cannot use parallelism with a UDF and inline SQL can. Most of these tests have been on single CPU machines. When you have complex queries that use parallelism and are faster using parallelism and you force SQL Server to use ONLY ONE processor by adding a UDF and the performance is degraded, why would anyone want that behavior. In my earlier post, I recommended to use Scalar UDFs carefully and strategically. Until the creators of SQL Server tell me or I see differences in how the optimizer works with Scalar UDFs, I will probably shy away from them when I can.

I also asked some the of MS SQL Server Developers during the SQL Pass 04 conference if this was going to be fixed, and they replied, "NO." If you want parallelism with Scalar UDFs, then build a CLR Function.

This has been fun and interesting as I have had performance issues as far back as I remember with Scalar UDFs. I am not against UDFs, I just like to use common sense when it comes to scalability and performance.

Thanks

~ Greg

Sergiy SSC Guru Points: 110206 More actions · Answer 3

You are clearly wrong about UDF and parallelism.

Here was a test where I compared using of 4 CPU and single CPU servers by UDF and inline SQL .

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=238981&p=3

_____________
Code for TallyGenerator

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

I guess we're saying the same thing. It's like trying to prove that a cursor is slower than a set based approach. We all know that, no need to add another proof.

Sergiy SSC Guru Points: 110206 More actions · Answer 5

But are you ready to declare:

"Don't ever use function Object_Id()"

based on this test?

That's what Carl is trying to do.

_____________
Code for TallyGenerator

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

I'm ready to say never try to formulate an unilateral rule in sql server without making sure it applies in that situation .

rhunt SSCertifiable Points: 5868 More actions · Answer 7

Sergiy,

Are you referring to this result...

SELECT dbo.TrimZeros (col1)

from #Var

1. 06 s

2. 24 s

...as an indication that parrallelism was working? Any chance the performance advantage was due to 3 times more RAM and RAID HD(s)?

Your debate with Gregg sparked my interest because it could be a very good example of a way to improve SQL by using the CLR.

Greg Grow SSCrazy Points: 2386 More actions · Answer 8

"You are clearly wrong about UDF and parallelism."

Sergiy,

I didn't see your article discussing Parallelism in the least. All I know is, when I add an User Defined Function (e.g.: NOT Built-In Function) I lose parallelism on every Intel 2way, 4way, 8way, and 12way machine that I tested. Write a query that uses parallelism with no UDF, run it, examine the Execution Plan and find your Parallelism. Also, look at PerfMon CPU Usage. Next, run it with a Scalar User Defined Function, doesn't matter how complex the UDF is, hell, it could return 1. Do you see parallelism? What is Perfmon doing? What is weird about looking at Performanc Monitor, is when the UDF is executed on a long running query, the CPUs do not look as the should. On a 2way machine, instead of both CPUs running and showing usage, the CPUs perform what I call a mirror of each other. As one cpu spikes up, the other cpu spikes down at the same place. Extremely weird behavior.

I will try and send some code along to see if I can show an example later.

Thanks

Greg