June 23, 2023 at 1:32 am
I am sure some of you have already experienced this but not having use Functions all that much, I recently learned that T-SQL's Scalar Functions always break Parallelism. Thus while conceptually a nice idea for compartmentalizing code it is basically useless unless you do not have parallelism available to you for one of the other reasons it might not exists in your Sequel Server databases.
That being said, I was wondering if there were some rather comprehensive, deep dives into this phenomenon as well as if there are any actual quality ways to implementing streamlining and compartmentalizing of SQL code? Basically, what Scalar Functions are supposed to do but without breaking parallelism in the process.
Lastly my apologies if this is not the correct forum for this type of question. I read through all the other forum descriptions and this is the only one seemed even close. So as a side comment, I think the forum for SQL related questions ought to be more clearly denoted as such so folks do not errantly post a question in the wrong forum.
June 23, 2023 at 6:32 am
This post by Brent Ozar is a starting point
June 23, 2023 at 1:32 pm
In general, T-SQL just doesn't lend itself to code reuse. I've seen lots of attempts at using functions as a way to turn T-SQL into more of a development language. Inevitably, they fail because the optimizer does a better job with straight T-SQL than when you cram it into UDFs. It is a pain, without a doubt. However, it's also reality. Trying to fight it is a waste of time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 23, 2023 at 3:50 pm
I write functions as an "ISF", a term that MS created a very long time ago in some now extinct documentation because they never created an "Inline Scalar Function". As you know, they have made it so that many scalar UDFs are auto-magically inlined but you also have a huge number of rules to follow for that to happen.
So, I write code as Inline Table Valued Functions (iTVF), use them in an APPLY, and they can basically provide scalar-like functionality in the rest of a statement.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 24, 2023 at 4:34 pm
Scalar functions are still a big NoNO ! ( Inlining doesn't work on all SVFs !! )
Write your SVFs as InlineTableValueFunctions and things will perform again
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 24, 2023 at 8:08 pm
Scalar functions are still a big NoNO ! ( Inlining doesn't work on all SVFs !! ) Write your SVFs as InlineTableValueFunctions and things will perform again
To that end, here's a link that contains a collection of posts on the Brent Ozar site for all known pitfalls. It can be summarized with "Don't use Scalar UDFs for anything". You might be able to get away with it in an Indexed View using WITH (NOEXPAND) but it's just not worth it because, if the Scalar UDF exists, someone will use the damned thing somewhere else.
https://www.brentozar.com/?s=scalar+functions
I've not tried it because I don't use them but I'd bet that an mTVF (multi-statement Table Valued Function) would have the same problem. Use only iTVFs (inline Table Valued Functions). Even the auto-inlining of SCALAR UDFs isn't guaranteed (go look at the "CANNOT" list for them).
In other words, if you write a function that contains the word "BEGIN", you're in deep Kimchi and all the "magic" in 2019/2022 is no guarantee of coming out of that smelling like a Rose. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply