Parameterized SQL and NULL running slow

Question

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?

Answer 1

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.

Answer 2

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