SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Scalar UDF Performance Issue


Scalar UDF Performance Issue

Author
Message
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15948 Visits: 11355
Just to add: I don't want to give the impression the SCHEMABINDING only produces benefits in UPDATE plans - it doesn't.

The key point is that the QO can apply many more optimizations to deterministic functions - that may be important in more complex SELECT statements, where the value can be evaluated once and a table-spool used to 'remember' the result of the function for later executions with the same parameters.

The caveat there is that the QO doesn't make any attempt to estimate the true cost of a scalar function - it uses a fixed cost. This may mean that it fails to apply worthwhile optimizations for relatively expensive functions.

Another advantage of SCHEMABINDING is the ability to persist and index computed columns:

ALTER TABLE dbo.TestTable ADD C AS dbo.ComputeNum(B) PERSISTED -- Fails
ALTER TABLE dbo.TestTable ADD C AS dbo.ComputeNumSB(B) PERSISTED -- Succeeds

The final 'win' is that even a non-persisted computed column based on a deterministic function can have statistics created on it:

ALTER TABLE dbo.TestTable ADD D AS dbo.ComputeNum(B) -- Not persisted, so succeeds

CREATE STATISTICS s on dbo.TestTable (C) -- Succeeds
CREATE STATISTICS s on dbo.TestTable (D) -- Fails

Paul



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87316 Visits: 41113
Heh... well done, Paul. You should have written the article. Thanks for taking the time.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15948 Visits: 11355
Jeff Moden (6/21/2009)
Heh... well done, Paul. You should have written the article. Thanks for taking the time.

Wow- coming from you that's awesome!
And you're welcome, of course - I enjoyed doing it.
Paul



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
tfifield
tfifield
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1299 Visits: 2890
Paul,
Thanks for the full explanation. It makes complete sense now.

Jeff,
Thanks for your time and full test. You do a fine job of myth busting. I've used your reference on SELECT INTO #Temp not actually locking sysobjects in TempDB several times to resolve arguments with DBA's
Todd Fifield
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87316 Visits: 41113
Paul White (6/21/2009)
Jeff Moden (6/21/2009)
Heh... well done, Paul. You should have written the article. Thanks for taking the time.

Wow- coming from you that's awesome!
And you're welcome, of course - I enjoyed doing it.
Paul


What I really enjoy is the fact that you laid it out all so nicely. Not my intent to pull a code review on you but I have to say something when I see some quality code especially when it proves such a valuable point. The code is nicely formatted (even though the forum slammed all your stuff to the left), well documented, and follows most best practices like using 2 part names for objects and using a consistent format. Adding to that, you actually tested for something more than a small handful of rows, included the results in your post, and added a complete additional explanation... this is some awsome stuff. I'd recommend that you actually DO turn it into an article for SSC especially since it trashes one side of a myth and proves the other.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87316 Visits: 41113
tfifield (6/21/2009)
Jeff,
Thanks for your time and full test. You do a fine job of myth busting. I've used your reference on SELECT INTO #Temp not actually locking sysobjects in TempDB several times to resolve arguments with DBA's
Todd Fifield


Thanks Todd... I blew it this time, though Blush... only disproved half the myth. Hehe I was getting tired and didn't read the whole article that Paul cited... just scanned it. As a result, I missed the fact that it wasn't just about SELECTs. The really cool part of it is that good folks like Paul politely recognize that something was missed and take the time to show what that was... and Paul did it very well and with a great amount of class. Heh... and I love when I learn something new.

Thanks for the reminder on the SELECT/INTO thing. I don't trust much of anything on the Internet (even from Microsoft) so I actually did a full blown test on it. I've been meaning to write an article about all that because it comes up so much but I keep forgetting about it. I've put some reminders on my calendar thanks to your reminder. :-)

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
tfifield
tfifield
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1299 Visits: 2890
Jeff,
I'm awaiting that article. It would be nice to have something that looks pretty to resolve these arguments I get into.
Todd Fifield
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search