How to avoid using scalar User Defined Functions?

Question

How to avoid using scalar User Defined Functions?

Viewing 3 posts - 31 through 32 (of 32 total)

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

x SSC-Insane Points: 23660 More actions · Answer 1

Kim Crosser (9/30/2015)
BTW - that demonstration of the iTVF approach (much earlier above) was very helpful and interesting. 🙂
Honestly, I never considered using an iTVF in that type of situation, but I will now.
It does sort of raise the question of why SQL Server is so much slower to return one scalar value than to do a table join, when the logic (and SQL expression) is identical in both.

I have a theory that ALL of T-SQL is slow to begin executing. Each individual line gets processed, and when a plan is either built or reused for it, only then is it handed over to the part that does the processing.

With set oriented coding (which iTVF is), we still see the slow processing of the T-SQL, but we only see it ONCE before it gets executed, and therefore the slow processing of the T-SQL is dwarfed by the processing time of the resulting execution plan, ie., the interpretation and generation of a new plan or linkage to an existing plan is amortized over a much bigger chunk of work.

With scalar functions, this initial processing has to happen for each invocation of the function. Same for cursors and other iterative looping processes.

Thats my current working theory, but I would be happy to see it disproved!

WayneS SSC Guru Points: 95461 More actions · Answer 2

Ed Wagner (9/29/2015)
[I know everyone's heard about queries that run too slowly and need to be tuned. However, I've never heard of anyone being told that their queries run too fast and they need to be slowed down.

Guess I have a story that you need to hear sometime...

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Ed Wagner SSC Guru Points: 287026 More actions · Answer 3

WayneS (10/1/2015)
Ed Wagner (9/29/2015)
[I know everyone's heard about queries that run too slowly and need to be tuned. However, I've never heard of anyone being told that their queries run too fast and they need to be slowed down.
Guess I have a story that you need to hear sometime...

That's a story I'd love to hear the next time we see each other.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions