0

Suppose I have a table called ShipmentItem with 1 million records in it. A few fields on it are indexed, to speed up searches. One of such fields is PartNumber. I would like to have search with optional parameters on ShipmentItem.

For example, a stored procedure has @PartNumber parameter, which can be NULL. If it's NULL, a query should return any item, as if @PartNumber wasn't in the WHERE clause. In production environment there are multiple fields that can be searched in a similar fashion, and only a subset of them can be entered (others will be NULL).

I found out by experiment that ISNULL and its alternatives kills index use, so the query becomes slower by 100 times (approximately).

To clarify, what I want to do is find "best match" to user search. If user does not enter anything, find any 1 record in the table (which is useless from business perspective, but UI will take care of that). All of the fields used in search are indexed, so if user enters at least one, it should work fast... But it doesn't, not with optional parameters.

Suppose a parameter was declared:

DECLARE @PartNumber varchar(5) = 'XXXXX';

And no items can be found, this query runs in 0.5sec.

SELECT TOP 1 * 
FROM ShipmentItem 
WHERE (@PartNumber IS NULL OR ShipmentItem.PartNumber = @PartNumber)

If an item is found, it's found within 3-10ms. I would like to ensure user does not wait long if it cannot be found. In real production environment it takes more that 0.5sec in that case, because query is 2 pages of SQL long.

My understanding is that it should run as fast as this:

SELECT TOP 1 * 
FROM ShipmentItem  
WHERE ShipmentItem.PartNumber = @PartNumber

I tried all combinations of COALESCE, ISNULL*,CASE WHENandCASE @Variable WHEN`, which was most promising, but it can't work with NULLs, since nothing is ever equal to NULL.

Question: Is there any solution to this performance problem aside from switching to dynamic SQL?

For reference, yes, dynamic SQL, i.e. building a query string dynamically, and appending or skipping those conditions manually solves the problem, but it becomes a maintenance problem from now on (passing parameters, escaping, syntax highlight etc).

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Victor Zakharov
  • 25,801
  • 18
  • 85
  • 151
  • What version of SQL Server? – Martin Smith Mar 25 '15 at 17:52
  • @MartinSmith: 2014 Standard. – Victor Zakharov Mar 25 '15 at 17:56
  • @JNK posted an idea to use `OPTION(RECOMPILE)`, which seems to be faster at first glance. This answer is now deleted (not sure why). Slightly slower than straight WHERE clause (2 times), but much faster than plain ISNULL and similar. – Victor Zakharov Mar 25 '15 at 18:01
  • @MartinSmith: Thanks for pointing me to the right answer. I browsed through a dozen of stackoverflow answers and internet articles before asking, they all suggested using ISNULL and alternatives, but no word about performance issues. It seems that `OPTION(RECOMPILE)` is the way to go. – Victor Zakharov Mar 25 '15 at 18:03
  • you can use case col1 is null then.... – benjamin moskovits Mar 25 '15 at 18:04
  • 1
    Apart from the links in the accepted answer in the other question (to the "dynamic search conditions" articles) this is another relevant link http://sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options – Martin Smith Mar 25 '15 at 18:05

0 Answers0