Parameterized query performance

• am-244616

  SSCarpal Tunnel

  Points: 4936

  More actions

  July 11, 2011 at 1:39 pm

  #389948

  Guys

  I am having a weird where the same query with parameterized vs non parameterized for the same input parameter, the parameterized query is taking 3X times longer

  and this is consistent with any kind of input parameters, I am not sure why the SQL Engine is taking more execution time for parameterized query though the query

  plan is same.

  Non Parameterized < 1sec response time

  SELECT Top(500) DISPLAYNAME AS 'Name',lastdate,identityid,SEX

  FROM view1 WHERE 1=1

  WHERE 1=1 AND LAST_NAME LIKE 'matt%'

  Parameterized > 5 sec response time

  declare @parm1 varchar(100) = 'matt%'

  SELECT Top(500) DISPLAYNAME AS 'Name',lastdate,identityid,SEX

  FROM view1 WHERE 1=1 AND LAST_NAME LIKE @parm1

  Any suggestions or inputs would help

  Thanks

• Orlando Colamatteo

  SSC Guru

  Points: 182276

  More actions

  July 11, 2011 at 2:39 pm

  #1351331

  Please post the actual execution plans for both queries.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• Jack Vamvas

  SSChasing Mays

  Points: 651

  More actions

  July 19, 2011 at 11:30 pm

  #1355354

  Is this a large table? Is one plan being stored in the cache with not optimal estimates?

  Try clearing down the cache , updating statistics and rerunning or RECOMPILING

  Jack Vamvas
  sqlserver-dba.com