Geek Answers Handbook

SQL parameter slows down the query

I have a query that I am using with SQL Server 2008R2 through ADO.NET. When I use the LIKE inline clause, it works in less than a second, and 5 rows are returned from 2 million. If I declare a paramater, as I do in .NET at the beginning of a request in SSMS, it takes forever.

This is the same query, but parameterized.

The first (which works great) (works great):

;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 is always conducted:

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 internal workings of SQL Server, but I really don't know.

+5
source share
4 answers

Use

SELECT * 
FROM Results_CTE 
OPTION (RECOMPILE)

SQL Server , , , , , , , , , .

, , PostCode , ( , ), .

+2

optimize for, , , :

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

SqlCommand.Parameters.Add() #, . , SQL Server, Google, #.

, .

:

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

:

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

:

using System.Data;

, -!

+2

, sniffing - SQL Server "" . , , , "" /, , sniffing

, / @postcode, LIKE '%', , , LIKE . , / @postcode, SQL Server - .

, :

  • @postcode.
  • "" , ,

:

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 exactly the same as I found that SQL Server deals with parameters in a strange way - the rules about when only the sniffing parameter is used can be strange in time, so you might want to Playback with the options above.

+1
source

More articles:


All Articles