How to Make Scalar UDFs Run Faster (SQL Spackle)

Question

How to Make Scalar UDFs Run Faster (SQL Spackle)

Viewing 15 posts - 31 through 45 (of 102 total)

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

peter-757102 SSCertifiable Points: 6877 More actions · Answer 1

I did some timings on my own using the code in the article and as expected the time the baseline and the iTVF take are identical. Not surprisingly I get the same exact query plan from both methods. Even when adding a second result column to the function (not used by the main query), everything stays identical. This behavior is completely in line with iTVF behaving like parameterised views, removing all unnececary code in the optimization process.

GSquared SSC Guru Points: 260824 More actions · Answer 2

Usman Butt (8/2/2012)
GSquared (8/1/2012)
Here's a function I tested, and the test I used....
@GSquared Any reason for not turning it into iTVF/iSF? I guess then the it could have been much performant? Also, fixing TOP(8000) ROW_NUMBER in the function could be causing the major slowdown while comparing shorter strings. I will try convert it into iTVF/iSF with LEN check in my testing to see the difference.
For Bill O'Malley kind of stuff, another approach could have been to leave the post-letter/alphabet after the single quote as it is? But I am always impressed by the detailed analysis you come up with. (I wish I could analyze things with such details and ease)
Last but not least knowing your data pattern is a must but sometimes a difficult task itself 😉

I specifically kept it as a scalar UDF so that I'd be comparing apples-apples when compared to the iterative string function. Not a fair comparison otherwise.

Leaving pieces of the string alone will work in some circumstances, not in others.

BIG BOB'S CAR MART

Bill O'Malley's fancy pants

Those two business names, one will end up right, the other wrong, if you leave pieces after single-quotes alone.

Even if you can guarantee that it will always produce the correct output by controlling the input, you're still going to be adding conditional logic to the thing, which means more CPU cycles to run each time, which means less performance.

This is one of the reasons commercial mail is more commonly addressed to BILL O'MALLEY than to anything processed with an algorithm. All-caps is less personal, but it's also less likely to offend.

As for the analysis, that's a major part of my education. I skipped college and spent a couple of years in a specialized school that teaches that kind of thing (and a lot else). I have more hours on management theory, executive function, workflow analysis and establishment, metrics, personnel management (training, ethics, morale), and so on, than an MBA does. Looking at things from a million viewpoints at once is a key part of that training. That's where that particular skill comes from.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Usman Butt Hall of Fame Points: 3186 More actions · Answer 3

peter-757102 (8/2/2012)
I did some timings on my own using the code in the article and as expected the time the baseline and the iTVF take are identical. Not surprisingly I get the same exact query plan from both methods. Even when adding a second result column to the function (not used by the main query), everything stays identical. This behavior is completely in line with iTVF behaving like parameterised views, removing all unnececary code in the optimization process.

Can you please share the testing harness? But Scalar function for smaller strings especially with longer / proper cased words could beat the iTVF solution easily. But as GSquared said knowing your data is the key.

I am sure one would prefer a solution which can handle strings with different lengths and does not escalate the execution timings drastically for extreme cases (Talking about it generally. Have not proved yet that iTVF solution is better in most cases. May be your testing proves Scalar function better)

Usman Butt Hall of Fame Points: 3186 More actions · Answer 4

GSquared (8/2/2012)
Usman Butt (8/2/2012)
GSquared (8/1/2012)
Here's a function I tested, and the test I used....
@GSquared Any reason for not turning it into iTVF/iSF? I guess then the it could have been much performant? Also, fixing TOP(8000) ROW_NUMBER in the function could be causing the major slowdown while comparing shorter strings. I will try convert it into iTVF/iSF with LEN check in my testing to see the difference.
For Bill O'Malley kind of stuff, another approach could have been to leave the post-letter/alphabet after the single quote as it is? But I am always impressed by the detailed analysis you come up with. (I wish I could analyze things with such details and ease)
Last but not least knowing your data pattern is a must but sometimes a difficult task itself 😉
I specifically kept it as a scalar UDF so that I'd be comparing apples-apples when compared to the iterative string function. Not a fair comparison otherwise.
Leaving pieces of the string alone will work in some circumstances, not in others.
BIG BOB'S CAR MART
Bill O'Malley's fancy pants
Those two business names, one will end up right, the other wrong, if you leave pieces after single-quotes alone.
Even if you can guarantee that it will always produce the correct output by controlling the input, you're still going to be adding conditional logic to the thing, which means more CPU cycles to run each time, which means less performance.
This is one of the reasons commercial mail is more commonly addressed to BILL O'MALLEY than to anything processed with an algorithm. All-caps is less personal, but it's also less likely to offend.
As for the analysis, that's a major part of my education. I skipped college and spent a couple of years in a specialized school that teaches that kind of thing (and a lot else). I have more hours on management theory, executive function, workflow analysis and establishment, metrics, personnel management (training, ethics, morale), and so on, than an MBA does. Looking at things from a million viewpoints at once is a key part of that training. That's where that particular skill comes from.

I was already impressed 🙂 But skipping college in your case worth its weight in gold 😉

peter-757102 SSCertifiable Points: 6877 More actions · Answer 5

Usman Butt (8/3/2012)
peter-757102 (8/2/2012)
I did some timings on my own using the code in the article and as expected the time the baseline and the iTVF take are identical. Not surprisingly I get the same exact query plan from both methods. Even when adding a second result column to the function (not used by the main query), everything stays identical. This behavior is completely in line with iTVF behaving like parameterised views, removing all unnececary code in the optimization process.
Can you please share the testing harness? But Scalar function for smaller strings especially with longer / proper cased words could beat the iTVF solution easily. But as GSquared said knowing your data is the key.
I am sure one would prefer a solution which can handle strings with different lengths and does not escalate the execution timings drastically for extreme cases (Talking about it generally. Have not proved yet that iTVF solution is better in most cases. May be your testing proves Scalar function better)

I believe you misunderstood me as I was posting about the test code from the article that demonstrated the most basic use of inline scalar funtions".

All I did was:

1. Run the test code form the article on my own server.

2. Modify the demo function dbo.TimesTwo, to include a second calculated column to see if that affects anything.

I did so just to see if iSF is really a special case of iTVF or not. Given identical timings, query plan and query plan costs I can do nothing but conclude iSF as a concept does not really exist in SQL Server.

Usman Butt Hall of Fame Points: 3186 More actions · Answer 6

@peter-757102 Sorry for the misunderstanding.

Just re-read the article and realize that I have missed some points. Must be sleepy while reading it the first time :crazy:

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 7

dbishop (8/1/2012)
Great article. I hate it when someone says, "Never...." You've proved that there is always more to the picture than what the eye can see (or the statistics tell us). Thanks.

Thanks for the feedback. I agree... "Never" is one of those words like "Always". There are very few times where either is true.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 8

milldyce (8/1/2012)
Hmm, it sounds like: "Never use Scalar UDF, unless you're doing iterative string manipulation".
In which case, I recommend the CLR :o)
Also, thanks very much for the information regarding "SET STATISTICS ON"! That was extremely illuminating. Does the "Include Client Statistics" feature have an implicit STATISTICS TIME ON?
It's prompted me to have a deeper look into what this button actually does, and similarly the "Show Actual Execution Plan" button.
A bit of a tangent, but I remember a number of queries for which response times exploded when trying to view the Execution Plan.

Actually, that's a good tangent because I've had the same thing happen. It's a shame that I didn't record the events so that I could have included them into this article.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 9

RichB (8/1/2012)
Interesting, well done.
Very concerned about that stats time issue.
Have you tried looking at these with a server side trace? I'd be very curious to know whether that can have a similar problem.
Equally - are you sure it hadn't just cached a dodgy query plan for the changed session settings?

It's real easy to prove whether or not it was a dodgy query plan or not. Use DBCC FREEPROCCACHE.

To answer your other question, I've not ever experienced such a thing with a server side trace but I'm not going to say that is will "never" happen. 😀

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 10

peter-757102 (8/1/2012)
Considering this, what then makes an iSF, what really sets it apart and makes it scalar or being processed as such?

The only thing that makes it an iSF is when it's used to return a scalar value. If nothing else, "iSF" is good shorthand for "A table valued function that returns only a single value". 🙂

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 11

mmilodragovich (8/1/2012)
Thanks for the detailed and METHODICAL analysis. I once was troubleshooting a performance problem in a large, complicated application and it came down to a "set quoted identifer on" statement in a scalar function. The statement was not needed, was probably generated by a developer who used a right-click-on-object to generate the "create" statement. The function was being called massive number of times so even the slightest performance difference was hugely magnified. I've been wary ever since but know that knowledge is power, so thanks for the article.

You're welcome and thanks for the feedback. I've had similar not-easy-to-explain things happen in this wonderful world of T-SQL. For example, I discover quite by accident about a week ago that the current rendition of the DelimitedSplit8K and 4K functions from the "Tally OH!" article actually run faster if you select both of its outputs (ItemNumber and Item) rather than just the Item.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 12

WayneS (8/1/2012)
Nice article Jeff. And you're right... it is a long spackle article 😀 - but sometimes you need that to fully explain things.

Heh... it was a big "crack" and it needed lots and lots of spackle. 😀

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 13

BowlOfCereal (8/1/2012)
Awesome article, Jeff, thanks! There are so many great takeaways here. Very interesting comments in the forum so far as well; I'm looking forward to following this one.
G Bryant McClellan (8/1/2012)
While every testing method negatively affects the performance of the thing being tested by making the tester part of the experiment, some are obviously worse than others. I'd be interested to hear from some of the SQL Internals authors if they have some insight into how SET STATISTICS TIME ON works and how its use may or may not affect other situations where it is used. Paul? Kalen? Are you thinking about this yet?
I'd like to echo this. I'm really interested in better understanding what's happening here with SET STATISTICS TIME ON, and when it should/shouldn't be used. That could be a spackle article in itself.

That would be interesting, indeed. That level of internals is a bit beyond what I can do.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 14

Sean Smith-776614 (8/1/2012)
Excellent article. Best read of the week. 🙂

Thanks, Sean. I really appreciate that.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 15

RBarryYoung (8/1/2012)
You can confirm this with the following query:
SELECT name
FROM master..spt_values
WHERE type = 'O9T'
AND name LIKE '%function%'
So as far back as SQL 2005, they actually had the object-type for it, however, AFAIK, none exist (not even in the hidden [mssqlsystemresource] database) and there's no syntax to create one. So it appears that this is something that Microsoft must have anticipated in the run-up to SQL Server 2005 by adding a type for it (and doc!), but never actually implemented for some reason. Even odder, they never took it out of the doc, it's still there in the 2012 version.(!)

Now that's interesting. I didn't think of looking for such a thing there. Maybe there's some special form that can be used that will actually use the "IS" object type notation even if it isn't documented.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)