How to Make Scalar UDFs Run Faster (SQL Spackle)

Question

How to Make Scalar UDFs Run Faster (SQL Spackle)

Viewing 13 posts - 91 through 102 (of 102 total)

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

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

Great question! I've never tried it, Stefan. I'm not even going to hazard a guess on that but I'll give it a try tonight after work.

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

Paul White SSC Guru Points: 150468 More actions · Answer 2

Stefan Krzywicki (6/24/2014)
Hi Jeff,
I know this is an old article reprinted and you might not see this, but I thought I'd ask anyway.
If you're using a Scalar UDF to define a Computed Column, can you still use a iTVF?

Nope. Sad isn't it.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Dwain Camps SSC Guru Points: 86978 More actions · Answer 3

Even though I haven't had much time of late to devote to SQL stuff, I did find time to go back and re-read this article.

Excellent new information in there Jeff! Thanks for the update.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

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

dwain.c (6/25/2014)
Even though I haven't had much time of late to devote to SQL stuff, I did find time to go back and re-read this article.
Excellent new information in there Jeff! Thanks for the update.

I'm glad to hear that you're OK, Dwain. Rumor has it that there was a wee bit of "political excitement" in you part of the world.

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

Dwain Camps SSC Guru Points: 86978 More actions · Answer 5

Jeff Moden (6/25/2014)
dwain.c (6/25/2014)
Even though I haven't had much time of late to devote to SQL stuff, I did find time to go back and re-read this article.
Excellent new information in there Jeff! Thanks for the update.
I'm glad to hear that you're OK, Dwain. Rumor has it that there was a wee bit of "political excitement" in you part of the world.

The reports you're hearing in the press of a "repressive junta quashing dissent" are greatly overblown. <rant>Removed due to concerns expressed by a good friend and not cowardice!</rant>

Now that I could finally find a suitably covert place to say that, I feel much better.

I went to PNG last month and came back with a shed-load of project work. Coincidentally, the coup occurred while I was abroad, resulting in my only worry over the whole affair. That being that the imposed curfew might impact my return to Thailand.

They want us to build a Customs clearing module for the application we implemented for them last year. So most of my work at this time is business analysis. I also managed to land an Android mobile application from them. These projects have kept me pretty busy to the exclusion of all else.

Glad to hear someone was thinking of me when they read the news about Thailand.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

TomThomson SSC Guru Points: 104773 More actions · Answer 6

I read this again because I saw Jeff's reference to it in the "Find the Brackets" QotD discussion, and seeing that the measurements were all run on a fairly ancient SQL Server version (and presumably an oldish windows version) I decided to see what happens with more modern stuff. So I ran the stuff on SQL Server 2016 running on Windows 10.

Windows 10 does present some problems, because I set up my machine to phone home to MS rather often, so finding a quiet patch to do measurement in is not terribly easy. So I imagine my results aren't precise.

But is does seem clear that using SET STATISTICS TIME ON and OFF has less misleading impact than before: instead of the 13% elapsed time difference reported by Jeff for the baseline (no UDF) code with and without SET STATISTICS I'm seeing about 2% difference - less than a sixth of the impact in Jeff's runs with SQL Server 2005. Also, the elapsed time for the scalar UDF found using SET STATISTICS is only 123% of the elapsed time for the Scalar UDF using getdate/datediff, which is vastly different from the 4016% found by Jeff on the older software. And the difference beteen elapsed times for UDF and baseline (both measured using gatdate/datediff) is a factor of 19, as against the factor of about 9 in Jeff's results.

So it looks to me as if things have changed rather a lot since Jeff wrote the paper, and it would probably be useful if someone could do measurements on the latest SQL Server release and on a modern operating system (preferably an operating system and hardware more likey to be relevant to serious SQL Server installations than my Windows 10 Home on a fairly old laptop).

Tom

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

TomThomson (11/18/2016)
I read this again because I saw Jeff's reference to it in the "Find the Brackets" QotD discussion, and seeing that the measurements were all run on a fairly ancient SQL Server version (and presumably an oldish windows version) I decided to see what happens with more modern stuff. So I ran the stuff on SQL Server 2016 running on Windows 10.
Windows 10 does present some problems, because I set up my machine to phone home to MS rather often, so finding a quiet patch to do measurement in is not terribly easy. So I imagine my results aren't precise.
But is does seem clear that using SET STATISTICS TIME ON and OFF has less misleading impact than before: instead of the 13% elapsed time difference reported by Jeff for the baseline (no UDF) code with and without SET STATISTICS I'm seeing about 2% difference - less than a sixth of the impact in Jeff's runs with SQL Server 2005. Also, the elapsed time for the scalar UDF found using SET STATISTICS is only 123% of the elapsed time for the Scalar UDF using getdate/datediff, which is vastly different from the 4016% found by Jeff on the older software. And the difference beteen elapsed times for UDF and baseline (both measured using gatdate/datediff) is a factor of 19, as against the factor of about 9 in Jeff's results.
So it looks to me as if things have changed rather a lot since Jeff wrote the paper, and it would probably be useful if someone could do measurements on the latest SQL Server release and on a modern operating system (preferably an operating system and hardware more likey to be relevant to serious SQL Server installations than my Windows 10 Home on a fairly old laptop).

Oh my. Thanks for checking, Tom. I hope your findings are incorrect but I know how you are with this type of thing. I don't have 2016 loaded anywhere (nor Windows 10) so I hope someone will step up and confirm or deny with the kind of testing I know you did.

As a bit of a sidebar, the best thing I liked about Windows 10 was how easy they made it to go back to Windows 7. 😀

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

mickyT SSChampion Points: 10360 More actions · Answer 8

I did a quick test on an SQL Express 2016 instance and can confirm the differences are not as drastic as they use to be.

SQL Server 13.0.2151 (express)

16 Gb Memory

Windows 10 enterprise (reported as windows NT 6.3 10586)

3.6 GHz 4 core, 8 logical

The timing results were:

CPUElapsed

109102Statistics Time Baseline

13121470Statistics Time Function

1300Measured Time Function

110Measure Time iTVF

104Measured Time Baseline

4.81%-1.92%Baseline Difference

0.92%13.08%Function Difference

I've the the differences between both the CPU and Elapsed measurements.

david.edwards 76768 Ten Centuries Points: 1246 More actions · Answer 9

"Cracking" (groan) article. One for the briefcase, thank you.

"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?

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

TomThomson wrote:

So it looks to me as if things have changed rather a lot since Jeff wrote the paper, and it would probably be useful if someone could do measurements on the latest SQL Server release and on a modern operating system (preferably an operating system and hardware more likey to be relevant to serious SQL Server installations than my Windows 10 Home on a fairly old laptop).

It HAS been a while since this article came out and even more so now after your good post. We've got SQL Server 2016 at work (have had for quite a while) and I've got a relatively new laptop with Windows 10 and SQL Server 2017 DE. I'll see if I can find a little time to redo tests from this article and post them in this discussion.

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

david.edwards 76768 wrote:

"Cracking" (groan) article. One for the briefcase, thank you.

Thanks for the feedback, David. Really appreciate it.

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

david.edwards 76768 Ten Centuries Points: 1246 More actions · Answer 12

You're welcome. I think your articles, and keeping RBAR in mind, have helped me on my SQL journey over last few years, and improved my set-based approach to weird problems, helping the developers and speeding things up, more than any other single source.

"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?

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

I could not think of a higher compliment and I'm humbled by your words. Thank you again, good Sir!

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