Low performance when using parameterised query

[Jakob]

Hey...

Why do I sometimes experience low performance when I use a parameter instead
of an exact value?

For example the following performs very bad:

declare @TypeID integer

select @TypeID = 10

select ID from t_Table1 t1, t_Table2 t2 where

t_t1.ID = t_t2.FID and

t2.Type = @TypeID

but this query performs ok:

select ID from t_Table1 t1, t_Table2 t2 where

t_t1.ID = t_t2.FID and

t2.Type = 10

Jakob

[Erland Sommarskog]

[posted and mailed, please reply in news]

Jakob (ja************ *@hotmail.com) writes:

Why do I sometimes experience low performance when I use a parameter
instead of an exact value?
For example the following performs very bad:

declare @TypeID integer
select @TypeID = 10

select ID from t_Table1 t1, t_Table2 t2 where
t_t1.ID = t_t2.FID and
t2.Type = @TypeID

but this query performs ok:

select ID from t_Table1 t1, t_Table2 t2 where
t_t1.ID = t_t2.FID and
t2.Type = 10

First, you are not using a parameter, but a variable. This may seem
like a quibble, but it has some importance. More about that later.

To understand this, we need to know that SQL Server maintains statistics
about the values of table columns. When SQL Server needs to build a
plan for a query, it consults the statistics to see how the values
are distributed.

In the second query, SQL Server can thus look up the distribution of
t2.Type and find that maybe only a handful of the rows are likely to
match the condition. Assuming that there is an non-clustered index on
this column, SQL Server assumes that using this index is good.

But in the first query, SQL Server does not know the value of @TypeID,
because SQL Server builds the plan for the entire batch. It therefore
makes a general assumption, and may find that there are not that many
distinct values of t2.Type. If @TypeID has a common value, using the
index will be more expensive than scanning the table.

There is a third case, when you have a stored procedure:

CREATE PROCEDURE jakob @TypeID int AS
select ID from t_Table1 t1, t_Table2 t2 where
t_t1.ID = t_t2.FID and
t2.Type = @TypeID
go
EXEC jakob 10

In this case, SQL Server builds the query plan on first invocation,
and then it does indeed in regard the value passed to it. Sometimes
this may be bad, because SQL Server caches the plan. So if this value
is atypical, succeding invocations could perform poorly.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp