ADO.NET timeout but works fine in SSMS

Question

I'm trying to run a query from ADO.NET using SQL Server 2008R2. I'm using a CTE to provide paging and adding parameters for @Offset and @Limit, which are both integers.

I'm building up a parameterized query depending on user inputs. The final output is this:

;WITH Results_CTE AS (
    SELECT  ld.* , ROW_NUMBER() OVER (ORDER BY Key_Field) AS RowNum  
    FROM list..List_Data ld 
    WHERE VALUE_2010 IS NOT NULL  
    AND  Postcode LIKE @Postcode + '%' 
) SELECT * FROM Results_CTE 
    WHERE RowNum > @Offset AND RowNum < @Offset + @Limit 
OPTION (RECOMPILE) 

I'm using a few like clauses which is why I have OPTION RECOMPILE. If I declare the parameters via SSMS and run like so:

declare @postcode varchar(10) = 'SW1 1AA';
declare @Offset int = 0;
declare @Limit int = 10;

I get a very quick response time (less than 1s). If I try this with ADO.NET, however, it takes forever. I've tried adding the parameters with both of these:

cmd.Parameters.AddWithValue("@Offset", startRowIndex) // times out
cmd.Parameters.AddWithValue("@Limit", limit)

cmd.Parameters.Add(New SqlParameter("@Offset", SqlDbType.BigInt)) // also times out
cmd.Parameters.Item("@Offset").Value = startRowIndex
cmd.Parameters.Add(New SqlParameter("@Limit", SqlDbType.BigInt))
cmd.Parameters.Item("@Limit").Value = limit

If there are only a few rows returned by the first query though and I drop the @Offset and @Limit filtering, I get a decent response time. Is there a way I can speed this up and use paging?

EDIT: I'm passing in the @postcode parameter (which is a string in .NET via this:

cmd.Parameters.AddWithValue("@Postcode", normalizedPostcode)

Answer 1

Your ADO.NET code is passing in a parameter of a different datatype than the one you are testing in SSMS and you are getting implicit cast issues.

Don't use

cmd.Parameters.AddWithValue("@postcode", normalizedPostcode)

as this will auto create an nvarchar parameter and you will be getting implicit casts in your execution plan meaning that an index cannot be used. Instead pass in an explicitly created parameter of varchar type instead.

cmd.Parameters.Add("@postcode", SqlDbType.Varchar, 10)

Answer 2

1) For @postcode parameter please specify the length.

cmd.Parameters.Add("@postcode", SqlDbType.VarChar, 10).Value = str

2) Rewrite the query:

;WITH Results_CTE AS (
    SELECT  ld.Key_Field, ROW_NUMBER() OVER (ORDER BY Key_Field) AS RowNum  
    FROM list..List_Data ld 
    WHERE VALUE_2010 IS NOT NULL  
    AND  Postcode LIKE @Postcode + '%' 
) SELECT * FROM Results_CTE a
INNER JOIN list..List_Data b ON a.Key_Field = Key_Field
WHERE RowNum > @Offset AND RowNum < @Offset + @Limit 
--OPTION (RECOMPILE) 

Note 1: I assume that Key_Field is primary key (clustered) for List_Data table.

Note 2: Check if you have an index on VALUE_2010 and Postcode fields. If you have SQL 2008+ then you can create an filtered index:

--UNIQUE if Postcode has unique values for VALUE_2010 IS NOT NULL
CREATE [UNIQUE] INDEX aaa
ON MySchema.List_Data (Postcode)
WHERE VALUE_2010 IS NOT NULL