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

Aten

Have you checked what the execution plans actually are? (Using 'include actual execution plan' in Management Studio) – Diatomic

Yes, and they are totally different. – Aten

Would you include the execution plans? They may indicate what the issue is. – Median

Here's another solution for anyone whose use case prevented them from implementing any of the other solutions listed below. I ended up running two queries—the first one stayed parameterized, but only returned the Unique key (in my case, a single column named CTID). My second query was non-parameterized, and simply queried the same table WHERE CTID IN ( list of CTIDs I pulled in earlier go here ). This may not be the solution depending on your situation, but it effectively circumvented the issue SQL was having with the parameters and moved my very big query from 19 seconds to 2.5 seconds. – Durr

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.

Metrorrhagia

This seems to be what is happening. The OPTIMIZE hint made the parameterized query have the same plan as the literal value. – Aten

This is not correct. The query optimiser does not consider fairness. It just uses the values of the parameters at compile time however unrepresentative they may be. OPTIMIZE FOR UNKNOWN would have this effect. – Posture

I beg to differ Martin. SQL Server stores "statistics" against data in the table (SD, spread, top 10 etc.) This data is evaluated by the query planner to determine the most efficient plan to query the data. Please see: technet.microsoft.com/en-us/library/cc966419.aspx. The hint here is forcing the plan into the "optimal" plan because of the specific literal 1000, rather than the "unknown" value. – Metrorrhagia

Answer 2

I think you're running afoul of "parameter sniffing". Basically, what this means is that SQL Server tries to use as much information as it has to calculate an optimal execution plan for your query. In the case of your first query, you have a constant value that is known at compile time, so the engine can directly optimize the query plan for that value.

In the second one, the fact that you are using a variable masks that value from the engine at compile time (you'd think it should be able to figure it out, but I've actually had similar issues with a simple constant expression!), leading to poor performance.

One way you can try to get around this would be to wrap the query in a stored procedure that takes the parameter directly and then applies it to the query -- something like this:

CREATE PROCEDURE test
  @p0 int
AS
BEGIN
  select *
  from foo
  join bar on bar.x = foo.x
  join baz on baz.y = foo.y
  where foo.x = @p0
END

This should allow the optimizer to accurately "sniff" the parameter you use and generate an optimal query plan for you.

Puett

This saved me 2 minutes in a report that was taking 2.30 mins. Awesome! – Bilbao

Answer 3

In my case in DB table column type was defined as VarChar and in parameterized query parameter type was defined as NVarChar, this introduced CONVERT_IMPLICIT in the actual execution plan to match data type before comparing and that was culprit for sow performance, 2 sec vs 11 sec. Just correcting parameter type made parameterized query as fast as non parameterized version.

Hope this may help someone with similar issue.

Kablesh

This was exactly my problem. The db driver was converting my string to a unicode string. – Garmon

Same here, our solution can be found here: https://mcmap.net/q/456100/-sql-server-query-running-slow-from-java – Mendelian

glad I scrolled down this far, was our exact problem, an import went from 1 hour to 5 mins!! – Cimex

Answer 4

Your starting point should be the SQL profiler. Run both through the profiler, and see what the query plan is in each case... then update the question to describe the two plans.

One thing which I believe can be a problem is that if you have a parameterised query with one set of values, the optimiser may look at some of the stats/indexes and pick one way of doing it, then reuse that plan for all queries - despite it not being particularly appropriate for a different set of values. Likewise if the plan is determined when there's one set of data (e.g. when one table is tiny, encouraging a table scan) and then you add bucket-loads of data, the plan may not be appropriate. Neither of these would affect a query which was bad as the very first query for the prepared statement though.

Chalet

I have included screen shots of the plans. – Aten