SQL Parameter Slows Down Query

Question

I have a query which I'm using with SQL Server 2008R2 via ADO.NET. When I use a LIKE clause inline, it works in less than a second, with 5 rows returned from 2 million. If I declare the paramater as I do in .NET at the start of the query in SSMS, it takes forever.

It's the same query, but parameterized.

The first (which works fine) is (which works fine):

;WITH Results_CTE AS (
    SELECT  ld.* , ROW_NUMBER() OVER (ORDER BY PK_ID) AS RowNum  
    FROM list..List_Data ld 
    WHERE Name IS NOT NULL  AND 
    Postcode LIKE 'SW14 1xx%' 
) SELECT * FROM Results_CTE 

The second which takes forever is:

declare @postcode varchar(10) = 'SW14 1xx'
;WITH Results_CTE AS (
    SELECT  ld.* , ROW_NUMBER() OVER (ORDER BY PK_ID) AS RowNum  
    FROM list..List_Data ld
    WHERE Name IS NOT NULL  AND 
    Postcode LIKE @postcode +'%' 
) SELECT * FROM Results_CTE 

I believe this has something to do with the inner workings of SQL Server but I really have no idea.

Answer 1

I was googling for potential problems with SqlCommand.Parameters.Add() in C#, and I found this page. I know this is an SQL Server post, but others might find it through google, and it may help them with C#.

For me, none of the above answers worked, so I tried another method.

Instead of:

cmd.Parameters.Add(new SqlParameter("@postcode", postcode));

I used this instead:

// Replace SqlDbType enumeration with whatever SQL Data Type you're using.
cmd.Parameters.Add("@postcode", SqlDbType.VarChar).Value = postcode;

And don't forget the namespace:

using System.Data;

Hope this helps someone!

Answer 2

Use

SELECT * 
FROM Results_CTE 
OPTION (RECOMPILE)

SQL Server does not sniff the value of the variable so it has no idea how selective it will be and will probably be assuming that the query will return significantly more rows than is actually the case and giving you a plan optimised for that.

In your case I'm pretty sure that in the good plan you will find it is using a non covering non clustered index to evaluate the PostCode predicate and some lookups to retrieve the missing columns whereas in the bad plan (as it guesses the query will return a greater number of rows) it avoids this in favour of a full table scan.

Answer 3

You can use optimize for to have the parameterized query use the same execution plan as the one with a specific parameter:

SELECT * 
FROM Results_CTE 
OPTION (OPTIMIZE FOR (@postcode = 'SW14 1xx'))

Answer 4

This looks like a problem caused by parameter sniffing - during plan compilation SQL Server "sniffs" the current parameters values and uses it to optimise the query. The most common problem that this might cause is if the query is run with an "odd" parameter value the first time its run / compiled in which case the query plan will be optimised for that parameter value, parameter sniffing can cause all other problems however

In your case if the query is run with an empty / null value for @postcode then the query is using a LIKE '%' clause, which is very likely to cause a table scan as a LIKE wildcard is being used at the start of the filter. It looks like either the plan was initially run / compiled with an empty @postcode parameter, or SQL Server is somehow getting confused by this parameter.

There are a couple of things you can try:

1. Mark the query for recompilation and then run the query again with a non-null value for @postcode.
2. "Mask" the parameter to try and prevent parameter sniffing,

for example:

declare @postcode varchar(10) = 'SW14 1xx'
declare @postcode_filter varchar(10) = @postcode + '%'
-- Run the query using @postcode_filter instead of @postcode

Although this query looks like it should behave in exactly the same way I've found that SQL Server deals with parameters in strange ways - the rules on when exactly parameter sniffing is used can be a tad strange at time so you may want to play around with variations on the above.