SQL Server query takes longer with parameter than with constant string

Question

I'm facing a problem with MS SQL Server 2008 which is:

When I execute a query using a hard-coded string as a parameter, my query run fast but when I use a string parameter instead, the query takes longer!
Constant string query takes 1 second while the other takes 11 seconds.

Here are the codes bellow:

Constant string (1 second):

     SELECT * 
FROM   VIEWCONTENTS 
WHERE  COUNTRY = 'ZA' 
       AND CONTENTTYPE = 'A' 
       AND TASK = 'R23562'; 

Parameterized (11 seconds):

DECLARE @country AS CHAR(2); 

SET @country = 'ZA'; 

SELECT * 
FROM   VIEWCONTENTS 
WHERE  COUNTRY = @country 
       AND CONTENTTYPE = 'A' 
       AND TASK = 'R23562' 

Concent

Google "parameter sniffing".... – Laban

Have you compared the execution plans? Also read this: sqlperformance.com/2013/08/t-sql-queries/… – Copepod

As the optimizer doesn't know the value of the local variable in the second query it is most likely changing the query plan to use a table/index scan rather than a seek. – Bellini

Answer 1

Use OPTION (RECOMPILE) at the end of your query. So:

DECLARE @country AS CHAR(2); 

SET @country = 'ZA'; 

SELECT * 
FROM   VIEWCONTENTS 
WHERE  COUNTRY = @country 
       AND CONTENTTYPE = 'A' 
       AND TASK = 'R23562'
OPTION (RECOMPILE)

Favrot

Answer 2

What does this yield?

DECLARE @country AS VARCHAR(2); 
SET @country = 'ZA'; 

SELECT * 
FROM   VIEWCONTENTS 
WHERE  COUNTRY = @country 
       AND CONTENTTYPE = 'A' 
       AND TASK = 'R23562' 

How about this?

DECLARE @country AS CHAR(2); 
DECLARE @country1 AS VARCHAR(2); 
SET @country = 'ZA';
SET @country1 = @country; 

SELECT * 
FROM   VIEWCONTENTS 
WHERE  COUNTRY = @country 
       AND CONTENTTYPE = 'A' 
       AND TASK = 'R23562' 

Wallin