title: Why does a parameterized query produces vastly slower query plan vs non-parameterized query
body: <p>In a SQL Server 2005 database I'm working on this query:</p>
  
  <blockquote>
    <p>select * <br/>
    from foo <br/>
    join bar on bar.x = foo.x <br/>
    join baz on baz.y = foo.y <br/>
    where foo.x = 1000</p>
  </blockquote>
  
  <p>has a vastly different and faster query plan than the following parameterized version.</p>
  
  <blockquote>
    <p>declare @p0 int <br/>
    set @p0 = 1000 <br/>
    select * <br/>
    from foo <br/>
    join bar on bar.x = foo.x <br/>
    join baz on baz.y = foo.y <br/>
    where foo.x = @p0</p>
  </blockquote>
  
  <p>In my particular case the version with the literal runs in sub-second time.  The parameterized version takes 2-3 seconds.  I expected them to be identical given that they're the same query.</p>
  
  <p>Why are they getting different query plans?  </p>
  
  <p>Is there any way to make the parameterized version have the same performance as the literal version?</p>
  
  <p>Here are the query plans.  My real query is rather different than the generic one I gave above, however the ONLY difference between the two queries that produced these plans is the parameter.  Why would replacing a literal with a parameter result in such vastly different plans?  </p>
  
  <ul>
  <li><a href="http://www.freeimagehosting.net/image.php?c5ac690e59.jpg" rel="noreferrer">Literal Query Plan</a> </li>
  <li><a href="http://www.freeimagehosting.net/image.php?10c12c2e59.jpg" rel="noreferrer">Parameterized Query Plan</a></li>
  </ul>