I read couple small tips from a SQL Server MVP that I had to try out. I was surprised this is the first I’d ever heard of them. The tips were concerning optional parameters – basically, when you pass a default to match everything or a specific value to match. Like all things programming, there’s plenty of ways to accomplish the same results. The difference is how well they perform.
Take an example where you are optionally filtering a table by VendorID, NULL means match everything. The two ways I’d done this previously are:
and (@vendorid is null or vendorid=@Vendorid)
and
and vendorid=isnull(@Vendorid,vendorid)
Only subtly different. I had always worried about the second option because I’d read that using functions ruins chances of optimization using indexes, but it turns out to not be the case. The new suggestion by the MVP was to use BETWEEN and COALESCE the NULL default value into an upper and lower bound. So, storing a high value number like 999999999 in a variable called @MaxInt, the clause looks like
and vendorid between coalesce(@VendorID,0) and coalesce(@VendorID,@MaxInt)
But how does it perform? Interestingly well. When using an ID the query plans were:
57% of batch using the OR construct
vs. 53% of batch using ISNULL
vs. 26% of batch using COALESCE
When using NULL as the value, the query plans were the same and the batch percent was different but relative: 42%, 38%, and 16%.
The I/O cost was significantly lower when using the BETWEEN and, as shown, there’s less complexity and it results in a lower cost to the overall batch.
The other tip was another use of COALESCE. Instead of:
and (@streetnumber is null or streetnumber like '%'+@StreetNumber+'%')
use:
and streetnumber like coalesce('%'+@StreetNumber+'%','%')
In my testing, that single change took the statement from 68% of the batch to 60% of the batch. After changing all the criteria, the whole query went from an unusable 25 seconds to a manageable 4 seconds.