5

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)
Echilon
  • 10,064
  • 33
  • 131
  • 217
  • @close voter. This isn't a dupe. The previous question was about explaining different behaviour with variables not parameters. The two are treated differently by SQL Server. – Martin Smith Oct 24 '11 at 12:03
  • Can you show the code where you pass in the `@postcode` parameter? – Martin Smith Oct 24 '11 at 12:05
  • Is it definitely the SQL timing out? There is no possibility that its timing out trying to get a connection (eg because you have loads of connections open still when you do this) or anything else like that? – Chris Oct 24 '11 at 12:08
  • If I remove the clauses for the `Offset` and `Limit` in .NET it works every time, but only for small data sets. – Echilon Oct 24 '11 at 12:23
  • Interesting; not really an answer, but one common thing to try is looking at differences between the two usages; for example, SSMS normally has different `SET` options (which can render some indexes useless), or different isolation-level, etc. Also, have you tried [`OPTIMIZE FOR UNKNOWN`](http://blogs.msdn.com/b/sqlprogrammability/archive/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature.aspx) rather than `RECOMPILE` ? – Marc Gravell Oct 24 '11 at 12:26

2 Answers2

7

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)
Community
  • 1
  • 1
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Excellent tip. Exactly tuning the parameters in .NET to the DBType gets more or less instant response. – Echilon Oct 24 '11 at 13:20
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
Bogdan Sahlean
  • 19,233
  • 3
  • 42
  • 57