SQL Server query takes longer with parameter than with constant string

database-performance, parameters, performance, sql-server

Solution

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)

Problem

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' ```

Original source