CODEWITHSUNDEEP
sql-serverperformance
BartMao
BartMao

Reputation: 681

Parameter cause a table scan in SQL Server

I have two tables a, b, primary key is their index.

Requirement:

If @filter is empty, select all records of a, b, else split @filter by any specific separator, find records whose b.PKey are in the filter.

Current Implementation:

declare @filter nvarchar(max)= ''

SELECT * 
FROM a
JOIN b ON a.PKey = b.aPKey
       AND (@filter = '' OR b.PKey IN (SELECT item FROM splitFunction(@filter))

I found the last statement and (@filter = '' or b.PKey in (select item from splitFunction(@filter)) will always cause a table scan on table b, only if I remove @filter='', it will change to index seek.

Is there any way can implement my requirement and does not harm the performance?

Upvotes: 3

Views: 1852

Answers (2)

sam
sam

Reputation: 1304

For constants, the optimizer can work out the best plan based on statistics for the given values.

When you use variable you are forcing parameterization and the plan will be designed to be reusable for a wide range of values. So Optimizer uses scan and not seek. To overcome this problem stop using local variables in the query and use this as a parameter in stored procedure 

create procedure p1
@filter = ''
as
begin
SELECT * 
FROM a
JOIN b ON a.PKey = b.aPKey
       AND (@filter = '' OR b.PKey IN (SELECT item FROM splitFunction(@filter))
option (recompile)
end

This will give you the correct plan as in the stored procedure I have converted your local variable into parameter.

Upvotes: 1

Amit Sukralia
Amit Sukralia

Reputation: 950

Specifically for this case, you can also try this:

DECLARE @filter nvarchar(max)= ''

IF @filter = ''
BEGIN
    SELECT * 
    FROM a
    JOIN b ON a.PKey = b.aPKey
END
ELSE 
BEGIN
    SELECT * 
    FROM a
    JOIN b ON a.PKey = b.aPKey
    WHERE b.PKey IN (SELECT item FROM splitFunction(@filter))
END

Upvotes: 1

Related Questions