4 ms·
A fun example from SQL Server that bit me the other day. Observe this simple proc: create procedure SearchJackets @buttonCount int as select * from
by jasonkester 7y ago
A fun example from SQL Server that bit me the other day. Observe this simple proc:
create procedure SearchJackets
@buttonCount int
as
select *
from jackets
where @buttonCount is null or buttonCount = @buttonCount
It's a common pattern you use for multi-param search queries that lets you avoid building your query as a string. Normally there are lots of little filter params in there, but we'll just show one for now. "Computer: List me the jackets, and maybe just show the ones with a certain number of buttons."
That version takes 60 seconds to run, whereas this version takes less than a second:
create procedure SearchJackets
@buttonCount int
as
declare @buttonCountCopy int
set @buttonCountCopy = @buttonCount
select *
from jackets
where @buttonCountCopy is null or buttonCount = @buttonCountCopy
... because it kicks the optimizer upside the head and convinces it to reconfigure the query plan in an efficient way. There are hints you can use at CREATE time that are supposed to do the same thing, but they don't actually work in this particular case. So now one of my codebases has this hack sprinkled about in a couple "Advanced Search" pages.
Fun stuff.
- SkyBelow 7y agoIn your second proc, should set @buttonCountCopy = buttonCount be set @buttonCountCopy = @buttonCount ?
- jasonkester 7y agoYes. Fixed.
- manifestsilence 7y agoAnnoying that that should be necessary, but good to know! Out of curiosity, what does that particular inefficiency look like in the execution plan? Does it do a full table scan when @buttonCount is passed in null, or was it inefficient for any value?
- hackinthebochs 7y ago>There are hints you can use at CREATE time that are supposed to do the same thing, but they don't actually work in this particular case. Are you saying that "option(recompile)" doesn't work for you in this case? It works just fine for me when using that 'X is null or' pattern for optional filters.
- jasonkester 7y agoIndeed, that's the option I was testing that had no effect. From what I've read, it does seem to work in some cases. But not mine, sadly.
- hackinthebochs 7y agoWell that's interesting. I'll have to keep your little hack in the back of my mind. Thanks!