Optimizer chooses different plans

We are using Sql 2000 sp3.
All of our database access is coded in stored procedures.
When we execute a stored procedure it will do table scans, wether it is call
ed from COM+ using ADO or executed in Query Analyzer.
If we cut and paste the code from the stored proc directly to Query Analyzer
and execute it, it uses the indexes(Index Seeks).
We have added index hints and the stored proc will work for a while, but the
n starts doing table scans again.
An example:
SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
FROM MILEAGE_RATE mr (index=pk_mileage_rate)
LEFT JOIN EMPLOYEE e (index=uq_employee) ON
mr.union_id = e.union_id AND mr.local_union = e.local_union
WHERE e.payroll_number = @.payroll_number AND
(mr.effective_start_date <= @.work_date AND mr.effective_end_date >= @.work_da
te)
We pass @.payroll_number and @.work_date, then return @.milage_rate.
The pk_mileage_rate index is on the columns: union_id,local_union,effective_
start_date.
The uq_employee index is on the column payroll_number.
After we added the hints it worked for a while but now does table scans.
If I cut and paste this code into Query Analyzer AND remove the index hints
it does Index Seeks on both tables using the indexes.
If I execute it as a stored proc it does table scans most of the time BUT in
dex seeks sometimes.
What can we do to make our stored procs use the indexes that are there?
What can we do to make the Optimizer be consistent?"Don" <Don@.discussions.microsoft.com> wrote in message
news:CE1CAA8E-3048-4B4C-BAA8-1754AB1994EB@.microsoft.com...
> We are using Sql 2000 sp3.
> All of our database access is coded in stored procedures.
> When we execute a stored procedure it will do table scans, wether it is
called from COM+ using ADO or executed in Query Analyzer.
> If we cut and paste the code from the stored proc directly to Query
Analyzer and execute it, it uses the indexes(Index Seeks).
> We have added index hints and the stored proc will work for a while, but
then starts doing table scans again.
> An example:
> SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
> FROM MILEAGE_RATE mr (index=pk_mileage_rate)
> LEFT JOIN EMPLOYEE e (index=uq_employee) ON
> mr.union_id = e.union_id AND mr.local_union = e.local_union
> WHERE e.payroll_number = @.payroll_number AND
> (mr.effective_start_date <= @.work_date AND mr.effective_end_date >=
@.work_date)
> We pass @.payroll_number and @.work_date, then return @.milage_rate.
> The pk_mileage_rate index is on the columns:
union_id,local_union,effective_start_dat
e.
> The uq_employee index is on the column payroll_number.
> After we added the hints it worked for a while but now does table scans.
> If I cut and paste this code into Query Analyzer AND remove the index
hints it does Index Seeks on both tables using the indexes.
> If I execute it as a stored proc it does table scans most of the time BUT
index seeks sometimes.
> What can we do to make our stored procs use the indexes that are there?
> What can we do to make the Optimizer be consistent?
>
When you paste it into QA, are you removing the variables and hard-coding
the values?
Why are you LEFT JOINing EMPLOYEE and then applying a WHERE-clause
restriction on it? That makes no sense, and it could screw up the plan.
Try this, instead
SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
FROM MILEAGE_RATE mr
INNER JOIN EMPLOYEE e
ON mr.union_id = e.union_id
AND mr.local_union = e.local_union
WHERE e.payroll_number = @.payroll_number
AND mr.effective_start_date <= @.work_date
AND mr.effective_end_date >= @.work_date
David|||We are Declaring the variables and using a SELECT to set the value when we c
ut and paste the code to QA.
I think the code used the LEFT JOIN because there was a concern that an Empl
oyees Union may not have been entered correctly which comes from our mainfra
me.
All our Employee data is from the mainframe, but the mileage_rate table has
no mainframe dependency.
Anyway, still doesn't answer why the code ALWAYS uses the indexes when cut a
nd pasted to QA, but not so when the stored proc is executed.
Don
"David Browne" wrote:

> "Don" <Don@.discussions.microsoft.com> wrote in message
> news:CE1CAA8E-3048-4B4C-BAA8-1754AB1994EB@.microsoft.com...
> called from COM+ using ADO or executed in Query Analyzer.
> Analyzer and execute it, it uses the indexes(Index Seeks).
> then starts doing table scans again.
> @.work_date)
> union_id,local_union,effective_start_dat
e.
> hints it does Index Seeks on both tables using the indexes.
> index seeks sometimes.
> When you paste it into QA, are you removing the variables and hard-coding
> the values?
> Why are you LEFT JOINing EMPLOYEE and then applying a WHERE-clause
> restriction on it? That makes no sense, and it could screw up the plan.
> Try this, instead
> SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
> FROM MILEAGE_RATE mr
> INNER JOIN EMPLOYEE e
> ON mr.union_id = e.union_id
> AND mr.local_union = e.local_union
> WHERE e.payroll_number = @.payroll_number
> AND mr.effective_start_date <= @.work_date
> AND mr.effective_end_date >= @.work_date
> David
>
>|||Don,
Search Google for "parameter sniffing", because this is probably the
cause of your problem. It can be circumvented by not using parameters in
the query, but local variables.
For example
CREATE PROCEDURE MyProc (@.param int) AS
SELECT * FROM MyTable WHERE MyColumn = @.Param
would then become
CREATE PROCEDURE MyProc (@.param int) AS
Declare @.local int
Set @.local=@.param
SELECT * FROM MyTable WHERE MyColumn = @.local
Hope this helps,
Gert-Jan
Don wrote:
> We are using Sql 2000 sp3.
> All of our database access is coded in stored procedures.
> When we execute a stored procedure it will do table scans, wether it is ca
lled from COM+ using ADO or executed in Query Analyzer.
> If we cut and paste the code from the stored proc directly to Query Analyz
er and execute it, it uses the indexes(Index Seeks).
> We have added index hints and the stored proc will work for a while, but t
hen starts doing table scans again.
> An example:
> SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
> FROM MILEAGE_RATE mr (index=pk_mileage_rate)
> LEFT JOIN EMPLOYEE e (index=uq_employee) ON
> mr.union_id = e.union_id AND mr.local_union = e.local_union
> WHERE e.payroll_number = @.payroll_number AND
> (mr.effective_start_date <= @.work_date AND mr.effective_end_date >
= @.work_date)
> We pass @.payroll_number and @.work_date, then return @.milage_rate.
> The pk_mileage_rate index is on the columns: union_id,local_union,effectiv
e_start_date.
> The uq_employee index is on the column payroll_number.
> After we added the hints it worked for a while but now does table scans.
> If I cut and paste this code into Query Analyzer AND remove the index hint
s it does Index Seeks on both tables using the indexes.
> If I execute it as a stored proc it does table scans most of the time BUT
index seeks sometimes.
> What can we do to make our stored procs use the indexes that are there?
> What can we do to make the Optimizer be consistent?
(Please reply only to the newsgroup)