Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Scalar UDF Performance Issue Expand / Collapse
Author
Message
Posted Saturday, June 20, 2009 10:58 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:53 PM
Points: 9,928, Visits: 11,194
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #738980
Posted Sunday, June 21, 2009 1:10 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:43 PM
Points: 35,575, Visits: 32,167
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #738997
Posted Sunday, June 21, 2009 3:32 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:53 PM
Points: 9,928, Visits: 11,194
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #739016
Posted Sunday, June 21, 2009 10:28 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, September 28, 2014 1:41 PM
Points: 959, Visits: 2,888
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
Post #739076
Posted Sunday, June 21, 2009 12:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:43 PM
Points: 35,575, Visits: 32,167
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #739088
Posted Sunday, June 21, 2009 12:15 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:43 PM
Points: 35,575, Visits: 32,167
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 ... only disproved half the myth. 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #739090
Posted Monday, June 22, 2009 11:42 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, September 28, 2014 1:41 PM
Points: 959, Visits: 2,888
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
Post #739622
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse