sql server - Why does a parameterized query produces vastly slower query plan vs non-parameterized query

Question

In a SQL Server 2005 database I'm working on this query:

select *
from foo
join bar on bar.x = foo.x
join baz on baz.y = foo.y
where foo.x = 1000

has a vastly different and faster query plan than the following parameterized version.

declare @p0 int
set @p0 = 1000
select *
from foo
join bar on bar.x = foo.x
join baz on baz.y = foo.y
where foo.x = @p0

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.

Why are they getting different query plans?

Is there any way to make the parameterized version have the same performance as the literal version?

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?

• Literal Query Plan
• Parameterized Query Plan

Answer 1

It appears that the query planner has made a decision in the literal query which is based upon information that it already has. It would have statistics which it can query efficiently based on the spread of data given in your specific literal.

The parameterized query has chosen the query that it believes is fairest for all the data in your table, which you'll notice is many nested loops (performance = bad).

Perhaps you might try and run the database optimization tools on your database to see if some indexes could help you here?

Specifically in your query, try this:

declare @p0 int
set @p0 = 1000
select *
from foo
join bar on bar.x = foo.x
join baz on baz.y = foo.y
where foo.x = @p0
OPTION ( OPTIMIZE FOR (@p0 = 1000))

But I would be wary of doing this without being certain that the data contained in this query won't change and that your query on this plan will ALWAYS be more efficient.