Spell It Out
Back in October, I had written a couple posts about how parameterizing TOP can cause performance issues:
Anyway, I got back to thinking about it recently because a couple things had jogged in my foggy brain around table valued functions and parameter sniffing.
One technique you could use to avoid this would be to use an inline table valued function, like so:
CREATE OR ALTER FUNCTION dbo.TopParam(@Top bigint) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT TOP (@Top) u.DisplayName, b.Name FROM dbo.Users AS u CROSS APPLY ( SELECT TOP (1) b.Name FROM dbo.Badges AS b WHERE b.UserId = u.Id ORDER BY b.Date DESC ) AS b WHERE u.Reputation > 10000 ORDER BY u.Reputation DESC; GO
When we select from the function, the top parameter is interpreted as a literal.
SELECT tp.* FROM dbo.TopParam(1) AS tp; SELECT tp.* FROM dbo.TopParam(38) AS tp;
Performance is “fine” for both in that neither one takes over a minute to run. Good good.
This is, of course, not what happens in a stored procedure or parameterized dynamic SQL.
EXEC dbo.ParameterTop @Top = 1;
Keen observers will note that this query runs for 1.2 seconds, just like the plan for the function above.
That is, of course, because this is the stored procedure’s first execution. The
@Top parameter has been sniffed, and things have been optimized for the sniffed value.
If we turn around and execute it for 38 rows right after, we’ll get the “fine” performance noted above.
EXEC dbo.ParameterTop @Top = 38;
Looking at the plan in a slightly different way, here’s what the Top operator is telling us, along with what the compile and runtime values in the plan are:
It may make sense to make an effort to cache a plan with
@Top = 1 initially to get the “fine” performance. That estimate is good enough to get us back to sending the buffers quickly.
Unfortunately, putting the inline table valued function inside the stored procedure doesn’t offer us any benefit.
Without belaboring the point too much:
CREATE PROCEDURE dbo.ParameterTopItvf(@Top BIGINT) AS BEGIN SET NOCOUNT, XACT_ABORT ON; SELECT tp.* FROM dbo.TopParam(@Top) AS tp; END; GO EXEC dbo.ParameterTopItvf @Top = 1; EXEC dbo.ParameterTopItvf @Top = 38; EXEC sp_recompile 'dbo.ParameterTopItvf'; EXEC dbo.ParameterTopItvf @Top = 38; EXEC dbo.ParameterTopItvf @Top = 1;
If we do this, running for 1 first gives us “fine” performance, but running for 38 first gives us the much worse performance.
Thanks for reading!
A Word From Our Sponsors
First, a huge thank you to everyone who has bought my training so far. You all are incredible, and I owe all of you a drink.
Your support means a lot to me, and allows me to do nice stuff for other people, like give training away for free.
So far, I’ve donated $45k (!!!) worth of training to folks in need, no questions asked.
Next year, I’d like to keep doing the same thing. I’d also like to produce a whole lot more training to add value to the money you spend. In order to do that, I need to take time off from consulting, which isn’t easy to do. I’m not crying poor, but saying no to work for chunks of time isn’t easy for a one-person party.
I’m hoping that I can make enough in training bucks to make that possible.
Because this sale is extra sale-y, I’ve decided to name it after the blackest black known to man.
From today until December 31st, you can get all 25 hours of my recorded training content for just $100.00. If you click the link below to add everything to your cart, and use the discount code AllFor100 to apply a discount to your cart.
Some fine print: It only works if you add EVERYTHING. It’s a fixed amount discount code that you need to spend a certain amount to have kick in.
Thank for reading, and for your support.