query optimizer

Have two queries that are identical except for the one
parameter that is passed. I am getting two completely
different execution plans. Anyone run across this before.
ThanksIf the final result is different then the execution plan may well be
different. For example a query that returns just 1 row from a table may be
more likely to use an index seek than the same query with a different
parameter that returns 1 million rows from the table.
--
David Portas
--
Please reply only to the newsgroup
--
"cheilig" <cheilig@.utmck.edu> wrote in message
news:1def01c3839b$92a96390$a001280a@.phx.gbl...
> Have two queries that are identical except for the one
> parameter that is passed. I am getting two completely
> different execution plans. Anyone run across this before.
> Thanks|||The following might be helpfull ( I found it in
programming section)
Subject: Re: Slow Stored Procedures -- Executes fine in
Query Analyizer
From: "Erland Sommarskog" <sommar@.algonet.se> Sent:
8/25/2003 1:39:09 PM
Darchele Webb (dwebb@.insituform.com) writes:
> I have a parameterized stored procedure that when
executed
> from Query Analyzer as:
> exec sp_storedProcedureName parameter1, parameter2,
etc...
> takes 1:07 minutes and only takes 0:04 minutes when the
> stored procedure is copied into Query Analyzer and run.
> This is a huge difference and I can't figure out way.
> It's the same exact query.
There are several possible reasons. One has to do with the
fact
that when SQL Server builds a query plan for a stored
procedure,
it knows about values of constants and input parameter,
but not
variables. So when you say:
SELECT * FROM tbl WHERE col = 12
it knows that the search argument is 12, and it consult
its statistics
to find that 12 is a good value for using the non-
clustered index on
col. But if you say:
SELECT * FROM tbl WHERE col = @.val
It will have to guess, and it may find that there are many
values for
which the index on col would be no good. If @.val is an
input parameter,
SQL Server will use the value on first call, but that
means that for the
next invocation, the plan is less good. There are a few
more issues
about input parameter values, that I ignore for now.
A completely different reason is that there is an indexed
view or an
index of a computed column involved. In this case, it is
important
how the procedure was created. If you do
SELECT objectproperty(object_id
('procname'), 'IsQuotedIdentOn'),
objectproperty(object_id
('procname'), 'IsAnsiNullsOn')
This should return (1, 1) for the indexes on views and
computed
columns to be used.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books
.asp
Mirna
>--Original Message--
>Have two queries that are identical except for the one
>parameter that is passed. I am getting two completely
>different execution plans. Anyone run across this before.
>Thanks
>.
>