Parameterized SQL and NULL running slow

问题:

I'm calling some parametrized sql from .net. I'm not sure why but the sql runs quite slow when checking if the parameter is NULL compared to when its not included:

So this:

exec sp_executesql N'
 SELECT [id]
 FROM [tblAddress] (nolock)
 WHERE 1 = 1
 AND ([id] = @id OR @id  IS NULL)

',N'@id int',
@id=4395

Runs quicker then this:

exec sp_executesql N'
 SELECT [id]
 FROM [tblAddress] (nolock)
 WHERE 1 = 1
 AND ([id] = @id)

',N'@id int',
@id=4395

Running SQL profiler the duration of the top query over 1 million rows is 175 and its reads are 3720 but the second query's duration is 1 and only 3 reads.

Why such a difference and how could it be improved?

回答1:

An OR clause isn't SARGABLE so the plan used has a scan, not a seek like the 2nd one

Try this: 2 seeks

SELECT [id]
 FROM [tblAddress]
 WHERE [id] = @id
UNION ALL
SELECT [id]
 FROM [tblAddress] 
 WHERE @id IS NULL)

Note: you don't need the NOLOCK hint. Or 1=1

回答2:

There is no way you can make it to SEEK if @id is NULL, so it will always SCAN, and will always be slow (depending on the amount of rows in [tblAddress]. To fight this, you might want to limit the amount of results, returned by your query by specifying the TOP(N) clause.

So what I would do is:

IF @id IS NOT NULL
BEGIN
    SELECT [id]
      FROM [tblAddress]
     WHERE [id] = @id
END
ELSE
BEGIN
    SELECT TOP(20) [id] FROM [tblAddress] 
END

Also, I wouldn't use the NOLOCK hint if it is not strictly reuired.