A Little About Function Rewrites In SQL Server
Thanks for watching!
Video Summary
In this video, I delve into the fascinating world of function rewrites in SQL Server, specifically focusing on scalar UDFs and inline table-valued functions. I share my experience from years of performance tuning work, highlighting common mistakes and providing practical advice to avoid them. By comparing the use of `CROSS APPLY` versus `OUTER APPLY`, and ultimately demonstrating why placing the function call directly in the select list can yield better performance, I aim to help you optimize your queries effectively. Whether you’re a seasoned SQL Server professional or just starting out, this video offers valuable insights that could save you time and headaches down the line.
Full Transcript
Erik Darling here with Darling Data, and in this video we are going to be talking about one of my favorite subjects, because it’s one that people run into a lot of weird issues with, and that is with function rewrites. If you’ve been working with SQL Server long enough, especially doing performance tuning work, you have no doubt taken a scalar UDF and rewritten it as an inline table valued function. What you did is that you’ve the same thing that you’ve been doing, and you’ve been doing, and you’ve been doing this. What you did next, though, may have been either great or a disaster. It all depends on how smart you are. You might be smarter than me and not make this mistake. I learned from this mistake from making it a long time ago. You know. You gotta learn somehow. So, before we talk about that, if you like this channel and you would like to say thank you to me for spending countless hours producing this content for free, you can give me four four bucks a month. There’s a link to do that in the video description. If four bucks a month is just a bridge too far for your financial situation, liking and commenting and subscribing is equally as heartwarming to me, because I don’t get out much and talk to people. So, talking to people in YouTube comments is, you know, well, not a full-fledged substitute for a social life. It’s better than nothing. Eh?
Better than nothing. Darling data. Better than nothing. If you need help with SQL Server, and you would like something better than the nothing you’re getting right now, my rates are reasonable, and I do all this stuff really well. So, you should hire me to do that. Buy my time. If you would like to purchase some time that I’ve already spent, you can get 24 hours of high-quality, low-cost SQL Server content for the rest of your life. You know, I don’t know how long your life is going to be, but you can figure that out with me, together. You can get for about $150 US dollars. If you would like to see me live and in person, doing my song and dance, live and in person, I think I already said that, you can catch Kendra Little and I, me and Kendra, Kendra and me, however that works. I don’t, um, sometimes I forget English. Uh, November 4th and 5th in Seattle, Washington at Past Data Summit, delivering two, uh, wild stallion days of query tuning.
So, that’ll be fun. Uh, and aside from that, uh, we are ready to begin our, our query tuning fiesta for this video. So, all right, let’s get down to it. Now, I’ve got some indexes in place. They’re good enough. Now, let’s pretend that this function used to be a scalar UDF, right? Uh, it’ll grab a start date thing. It’ll grab some information from the users table for a specific user, and it’ll find their total posts, and then it will calculate their average posts per day for, uh, the span of time that we care about here. Right? So, cool. Uh, that’s what the function does. You can think what you want of its utility. That’s not really the point. The point is that when it used to be a scalar UDF, we could call it right in the select list like this.
Right? Average posts per day, stick a user ID in there, and we were good. Right? It would just, I mean, we weren’t good. We were slow. Um, you know, this query, this, that scalar UDF would force our query, uh, to not be able to use a parallel execution. Plan would be disallowed because of that. Uh, and, uh, that query, sorry, that function would be called once per row, not once per query. So that would put us in a bad way. And because of that stuff. The thing is, when, whenever you rewrite a scalar UDF as an inline table valued function, the first thing that occurs to a lot of people to do with it is to cross apply it to their data.
The thing is, that’s not logically correct. Cross apply is immediately out because cross apply will restrict rows if nothing, if we don’t find matches in there. Right? And we don’t want that. That’s not going to be the case for this query based on the logic of it because we’re looking for high reputation people. And we’re not going to like miss any, we’re not going to find anyone who has a high reputation that doesn’t have like the, the average post per day thing that we’re looking at.
Uh, so that’s, that’s kind of out here, but in general, you can’t safely do that because you might restrict rows using cross apply that you’re not allowed to. So logically, if you want to bake this into a query, the only thing that you can do is use outer apply. The thing with using outer apply is SQL Server is free to take that, that outer application and make it part of join reordering and all that other kind of crazy kooky stuff that it does.
And it could put that crop, that outer apply in a really bad, bad spot in the query plan. Um, that doesn’t happen here because my indexes are very good. SQL Server pretty much has one choice, but that’s, that’s okay.
I’m not going to, we’re not going to show you any tremendous plan differences, but even without any tremendous plan differences, there is a bit of a performance difference where you want to put things. Is in the select list. So even though it’s not quite as intuitive as sticking just the name of the scalar UDF and the select list, like we were able to up here, uh, you can put this, you can put the inline table valued function in the select list.
Just like that is a funny kind of funny looking little sub query type thing. And this will get you both the logical correctness of the query by not, by not restricting any rows. Uh, and the nice thing about queries in the select list is that SQL Server does tend to, uh, save them for later.
Uh, and by save them for later, I mean, after you’ve done all your joins and your where clause filtering and all the stuff that you relationally do with your data to restrict rows and return a result. Uh, SQL Server usually sends, uh, saves stuff that the little scalar sub queries like this in the select list for like the minimum amount of rows that will be projected out of the query through the select list. So you can actually end up with things being a little bit quicker.
And that does end up being the case here, not by a ton, but you know, by just enough for me to say, Hey, it’s an okay demo that shows how this thing can be better. So in the outer apply version of the query, this thing takes 6.2 seconds. And in the, uh, sub query in the select list version of the query, it takes about five seconds.
So we save about 1.2, 1.3 seconds, uh, just by putting this, the function in the right part of the query. Now, I don’t understand why that’s not terribly intuitive to everyone. Um, I think some people may be biased against, uh, sub queries in the select list.
Um, probably they got some bad advice from someone on LinkedIn about that. Uh, like never use sub queries, always use CTE. Everyone on LinkedIn who is a data engineer and posts stuff like that should probably quit LinkedIn, quit data engineering.
There are plenty of good trade jobs out there that you could get where you could actually, I don’t think I want them in the trades. They’d probably get someone killed. I don’t know.
Uh, I haven’t quite figured that out. Maybe join your local pickleball team. Maybe that’s a safer outcome for you because, uh, the amount of just hilariously bad advice that these people dish out on LinkedIn is, uh, I, sometimes I can’t comprehend. Sometimes the LinkedIn tab closes as quickly as it opens.
But anyway, uh, for some reason, it’s not terribly intuitive or some people have a bias against putting, um, sub queries in a select list. But using apply, uh, in this, in this case, even though it would have been logically correct because the outer apply won’t restrict any rows where cross apply would have. SQL Server still comes up with a slightly better, faster query plan, uh, with the skate, with the sub query in the select list rather than outer applied to the query.
So, uh, whenever you’re rewriting scalar UDFs is in line table valued functions, which you should be doing, uh, do try to keep the, uh, the function call in the same place that you found it. You just have to replace, you just have to do some slight manipulations with the query syntax in order to get it working. So in this case, it was just putting the select in here like this, which was exactly where it was, which is exactly where it got called as a scalar UDF.
It was just a little simpler to do because we don’t need to, you know, treat it like a, uh, select because this is treat, this inline table valued function just has to be called a little bit differently. So anyway, thank you for watching. I hope you enjoyed yourselves.
I hope you learned something. I hope you see that I only have one tab left after this and then I get to go the hell to bed. Uh, perhaps, perhaps my, my words earlier were just because I’m a little bit cranky, but that that’s okay. Where you’re, you’re, you’ll love me when I’m cranky.
You can’t handle me at my well-rested self. You don’t deserve me at my unrested self. I think that’s, that’s what, that’s what they say, right?
Okay. Anyway, thank you for watching. Thank you. Thank you. Thank you. Thank you. Thank you. Thank you.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.