Hey You Should Do This
Video Summary
In this video, I dive into the world of multi-statement table-valued functions and why they can be a performance nightmare in SQL Server. I share my experience of rewriting these functions to inline table-valued functions using startup expression predicates, demonstrating how to handle complex logic within them without compromising performance. I walk you through the process step-by-step, showing that even if your function has conditional blocks like `IF` statements, there are ways to refactor it into a more efficient form. By leveraging Common Table Expressions (CTEs) and careful predicate handling, we can achieve significant improvements in execution plans and overall query performance. So, whether you’re dealing with simple or complex logic within these functions, this video will equip you with the knowledge to tackle them head-on!
Full Transcript
I’m so ready. So ready to leave. I just have two videos left. After this one, there’s one video, but I have succumbed to my weaknesses, and it is champagne time at Shea Darling. And so, because it is champagne time, we should get this before it’s too much champagne time. So I wanted to talk about rewriting multi-statement table-valued functions, because they are generally bad for performance, even if the query that populates them is fairly simple. You just have like the goofy overhead of the table variable. On versions prior to SQL Server 2017, there was like almost no hope for these things. 2017 got something called interleaved execution, which gave you table cardinality for the, for like, cardinality estimates coming out of the function. Prior to that it was 100 rows for 2014 and one row for that. It was like a bad time. It was a bad time across the board.
The thing is when I talk to a lot of people, they’re like, I can’t rewrite this. It’s too complicated. There are if blocks. I’m like, yes, you can. Don’t be ridiculous. We can do this together. Hold your hand. Run through the, run, run through the field. Pick some flowers. I don’t know. It’ll be fun. Be romantic. So this is our multi-statement table-valued function. We have, it takes some stuff in here, user ID and a post type ID, and we declare a table variable with some columns in it. They’re all very, very useful, necessary columns. And then based on which post type ID we pass in, we will execute a block depending on, on that. And this is where people are often like, well, there’s just no way to do this. There’s no way to rewrite this.
I’m like, I’m very silly. Now, no, you, if, if you were to rewrite this, you couldn’t just do this, right? You couldn’t say this return table as, and then if blah, blah, do this, blah, blah, do this, blah, blah, do this. That will not work. There are red squiggles everywhere. We can see from the red squiggles that this will not work. This will not compile. If I try to create this, we’ll get, we’ll get some funk thrown at us. Incorrect syntax near the keyword if. Sorry to hear that. But what you can do inside of an inline table-valued function that does not require an if branch is take advantage of startup expression predicates.
And what I mean by that is we can stack some CTE. Now, this is a good use for CTE. I mean, granted, we could have used derived tables here or something too, but whatever. So what we’ll do is we’ll still pass in a user ID and a post type ID.
And what we’ll do is we’ll have a CTE called questions, and that’ll take care of the post type ID equals one stuff up here. And we’ll add a filter inside of here and say you only fire off if post type ID equals one. And we’ll do the same thing for answers and say you only fire off if post type ID equals two.
And we’ll do the same thing for other where if post type ID is greater than two, then we’ll fire this off. And then we’ll have a third CTE that is a union all of those three, right? So we have questions, answers. I don’t know why I put that in the kind of funny order, but questions, answers, and other.
And then we’ll just select the top one from that order by score descending. And this will work just fine, and it will work just wonderfully. And something that I think is nice about this is that even if we don’t need data from one of them, they won’t fire.
So the execution plan will be complicated. But if we look at it kind of closely, we’ll see that we only fired off and hit the post table the once. The rest of the time we got constant scans from the other two accesses there.
Now, oh rather, sorry, we have to quote this one out. That’s what I was doing wrong. Ha ha ha. If we quote this out, ooh la la, and we zoom out a little bit, we can see that we only touched the post table once.
We did not touch it three times because of the startup expression predicates. The three other times we got, or the two other times, rather, we got these constant scan operators. What I messed up before is that, you know, I ran the cross-apply part.
So even with this quoted out, that did happen. But if I flip things around and I bring this in and I bring this out and I get rid of you, and actually I should probably get rid of you too. There we go.
And we run this, the same thing will happen where SQL Server will look at what was going on and say, oh, I don’t need that first one. I’m only going to use that second one this time. And then if I need both, of course, then we’ll run both and we’ll get stuff from both, I guess. Lucky us.
All right. We got all that good stuff there. Yep. So we hit things twice because we had the two applies run. But anyway, that’s not really the point. The point is that there are ways to write more complicated multi-statement table-valued functions as inline table-valued functions.
It does take, you know, some practice. It does take some getting used to. But you can do it.
And you can often get much better performance from them in general. So that’s that. I don’t know. If you don’t like it, you don’t have to. Just do whatever you want.
It’s your life. Anyway, I’ll drink to that. I will see you over in the next video. We will talk about where filtered indexes are still broken.
See you there. Thanks for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.