Parameters in Stored Procedures problem

I have 2 stored procedures, both are listed below. The
first Stored Procedure takes 8 parameters and runs and
update query using those parameters. This procedure
takes about 3 minutes to run. The second procedure
doesn't take any parameters, but declares the same 8
variables at the start of the procedure and sets them to
same valuse that I used when I ran the first procedure
and this one takes less than a second to run.
Just a little more information here is the size of the
tables:
aggQuotaAtt 135194
txoQuota: 135194
mstrsCustomer: 19741
So my question is why does the same procedure take
radically different when I use parameters.
Here is the first procedure which takes 3 minutes:
ALTER PROCEDURE [dbo].[Paul'sJunk]
@.PrdID varchar(18),
@.ShipToID int,
@.PoolYearPeriodID int,
@.AorID int,
@.Aor0ID int,
@.Aor1ID int,
@.Aor2ID int,
@.Aor3ID int
as
print getDate()
UPDATE aggQuotaAtt SET
OrderQty = txoQuota.OrderQty,
Revenue = txoQuota.Revenue,
HistoricalPercentage = txoQuota.HistoricalPercentage,
CurrentPercentage = txoQuota.CurrentPercentage,
ModifiedDate = GetDate(),
AorID = mstrsCustomer.AorID,
Aor0ID = mstrsCustomer.Aor0ID,
Aor1ID = mstrsCustomer.Aor1ID,
Aor2ID = mstrsCustomer.Aor2ID,
Aor3ID = mstrsCustomer.Aor3ID,
Aor4ID = mstrsCustomer.Aor4ID
FROM aggQuotaAtt QuotaBase INNER JOIN txoQuota ON
QuotaBase.ShipToID = txoQuota.ShipToID
AND QuotaBase.PrdID = txoQuota.PrdID
AND QuotaBase.PoolYearPeriodID = txoQuota.PoolYearPeriodID
INNER JOIN WaveAgg.dbo.mstrsCustomer mstrsCustomer ON
mstrsCustomer.ShipToID = txoQuota.ShipToID
WHERE txoQuota.PrdID = @.PrdID
AND txoQuota.PoolYearPeriodID = @.PoolYearPeriodID
AND (txoQuota.ShipToID = @.ShipToID OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE AorID = @.AorID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor0ID = @.Aor0ID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor1ID = @.Aor1ID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor2ID = @.Aor2ID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor3ID = @.Aor3ID))
and here is the second procedure. This is the one that
takes less than a second:
ALTER PROCEDURE [dbo].[Paul'sJunk2]
as
DECLARE @.PrdID varchar(18)
DECLARE @.ShipToID int
DECLARE @.PoolYearPeriodID int
DECLARE @.AorID int
DECLARE @.Aor0ID int
DECLARE @.Aor1ID int
DECLARE @.Aor2ID int
DECLARE @.Aor3ID int
SET @.PrdID = '20027-933'
SET @.ShipToID = 0
SET @.PoolYearPeriodID = 73
SET @.AorID = 1
SET @.Aor0ID = NULL
SET @.Aor1ID = NULL
SET @.Aor2ID = NULL
SET @.Aor3ID = NULL
UPDATE aggQuotaAtt SET
OrderQty = txoQuota.OrderQty,
Revenue = txoQuota.Revenue,
HistoricalPercentage = txoQuota.HistoricalPercentage,
CurrentPercentage = txoQuota.CurrentPercentage,
ModifiedDate = GetDate(),
AorID = mstrsCustomer.AorID,
Aor0ID = mstrsCustomer.Aor0ID,
Aor1ID = mstrsCustomer.Aor1ID,
Aor2ID = mstrsCustomer.Aor2ID,
Aor3ID = mstrsCustomer.Aor3ID,
Aor4ID = mstrsCustomer.Aor4ID
FROM aggQuotaAtt QuotaBase INNER JOIN txoQuota ON
QuotaBase.ShipToID = txoQuota.ShipToID
AND QuotaBase.PrdID = txoQuota.PrdID
AND QuotaBase.PoolYearPeriodID = txoQuota.PoolYearPeriodID
INNER JOIN WaveAgg.dbo.mstrsCustomer mstrsCustomer ON
mstrsCustomer.ShipToID = txoQuota.ShipToID
WHERE txoQuota.PrdID = @.PrdID
AND txoQuota.PoolYearPeriodID = @.PoolYearPeriodID
AND (txoQuota.ShipToID = @.ShipToID OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE AorID = @.AorID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor0ID = @.Aor0ID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor1ID = @.Aor1ID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor2ID = @.Aor2ID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor3ID = @.Aor3ID))Jory asked effectively the same question yesterday in the .programming
group - the same responses apply.
"Paul Cavacas" <pcavacas@.oceanspray.com> wrote in message
news:08f301c36327$a1e66090$a601280a@.phx.gbl...
> I have 2 stored procedures, both are listed below. The
> first Stored Procedure takes 8 parameters and runs and
> update query using those parameters. This procedure
> takes about 3 minutes to run. The second procedure
> doesn't take any parameters, but declares the same 8
> variables at the start of the procedure and sets them to
> same valuse that I used when I ran the first procedure
> and this one takes less than a second to run.
> Just a little more information here is the size of the
> tables:
> aggQuotaAtt 135194
> txoQuota: 135194
> mstrsCustomer: 19741
> So my question is why does the same procedure take
> radically different when I use parameters.
> Here is the first procedure which takes 3 minutes:
> ALTER PROCEDURE [dbo].[Paul'sJunk]
> @.PrdID varchar(18),
> @.ShipToID int,
> @.PoolYearPeriodID int,
> @.AorID int,
> @.Aor0ID int,
> @.Aor1ID int,
> @.Aor2ID int,
> @.Aor3ID int
> as
> print getDate()
> UPDATE aggQuotaAtt SET
> OrderQty = txoQuota.OrderQty,
> Revenue = txoQuota.Revenue,
> HistoricalPercentage => txoQuota.HistoricalPercentage,
> CurrentPercentage => txoQuota.CurrentPercentage,
> ModifiedDate = GetDate(),
> AorID = mstrsCustomer.AorID,
> Aor0ID = mstrsCustomer.Aor0ID,
> Aor1ID = mstrsCustomer.Aor1ID,
> Aor2ID = mstrsCustomer.Aor2ID,
> Aor3ID = mstrsCustomer.Aor3ID,
> Aor4ID = mstrsCustomer.Aor4ID
> FROM aggQuotaAtt QuotaBase INNER JOIN txoQuota ON
> QuotaBase.ShipToID = txoQuota.ShipToID
> AND QuotaBase.PrdID = txoQuota.PrdID
> AND QuotaBase.PoolYearPeriodID => txoQuota.PoolYearPeriodID
> INNER JOIN WaveAgg.dbo.mstrsCustomer mstrsCustomer ON
> mstrsCustomer.ShipToID = txoQuota.ShipToID
> WHERE txoQuota.PrdID = @.PrdID
> AND txoQuota.PoolYearPeriodID = @.PoolYearPeriodID
> AND (txoQuota.ShipToID = @.ShipToID OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE AorID = @.AorID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor0ID = @.Aor0ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor1ID = @.Aor1ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor2ID = @.Aor2ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor3ID = @.Aor3ID))
>
>
>
> and here is the second procedure. This is the one that
> takes less than a second:
> ALTER PROCEDURE [dbo].[Paul'sJunk2]
> as
> DECLARE @.PrdID varchar(18)
> DECLARE @.ShipToID int
> DECLARE @.PoolYearPeriodID int
> DECLARE @.AorID int
> DECLARE @.Aor0ID int
> DECLARE @.Aor1ID int
> DECLARE @.Aor2ID int
> DECLARE @.Aor3ID int
> SET @.PrdID = '20027-933'
> SET @.ShipToID = 0
> SET @.PoolYearPeriodID = 73
> SET @.AorID = 1
> SET @.Aor0ID = NULL
> SET @.Aor1ID = NULL
> SET @.Aor2ID = NULL
> SET @.Aor3ID = NULL
> UPDATE aggQuotaAtt SET
> OrderQty = txoQuota.OrderQty,
> Revenue = txoQuota.Revenue,
> HistoricalPercentage => txoQuota.HistoricalPercentage,
> CurrentPercentage => txoQuota.CurrentPercentage,
> ModifiedDate = GetDate(),
> AorID = mstrsCustomer.AorID,
> Aor0ID = mstrsCustomer.Aor0ID,
> Aor1ID = mstrsCustomer.Aor1ID,
> Aor2ID = mstrsCustomer.Aor2ID,
> Aor3ID = mstrsCustomer.Aor3ID,
> Aor4ID = mstrsCustomer.Aor4ID
> FROM aggQuotaAtt QuotaBase INNER JOIN txoQuota ON
> QuotaBase.ShipToID = txoQuota.ShipToID
> AND QuotaBase.PrdID = txoQuota.PrdID
> AND QuotaBase.PoolYearPeriodID => txoQuota.PoolYearPeriodID
> INNER JOIN WaveAgg.dbo.mstrsCustomer mstrsCustomer ON
> mstrsCustomer.ShipToID = txoQuota.ShipToID
> WHERE txoQuota.PrdID = @.PrdID
> AND txoQuota.PoolYearPeriodID = @.PoolYearPeriodID
> AND (txoQuota.ShipToID = @.ShipToID OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE AorID = @.AorID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor0ID = @.Aor0ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor1ID = @.Aor1ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor2ID = @.Aor2ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor3ID = @.Aor3ID))|||can't say about the whole thing, but
what are the differences in the execution plan?
i have concern the select queries:
SELECT ShipToID FROM WaveAgg.dbo.mstrsCustomer WHERE
Aor0ID = @.Aor0ID
etc, where @.AorxID value is NULL,
this query should be a simple index lookup
but i have situations where the NULL value in the where
clause resulting in a table scan
>--Original Message--
> I have 2 stored procedures, both are listed below. The
>first Stored Procedure takes 8 parameters and runs and
>update query using those parameters. This procedure
>takes about 3 minutes to run. The second procedure
>doesn't take any parameters, but declares the same 8
>variables at the start of the procedure and sets them to
>same valuse that I used when I ran the first procedure
>and this one takes less than a second to run.
> Just a little more information here is the size of the
>tables:
>aggQuotaAtt 135194
>txoQuota: 135194
>mstrsCustomer: 19741
> So my question is why does the same procedure take
>radically different when I use parameters.
>Here is the first procedure which takes 3 minutes:
>ALTER PROCEDURE [dbo].[Paul'sJunk]
> @.PrdID varchar(18),
> @.ShipToID int,
> @.PoolYearPeriodID int,
> @.AorID int,
> @.Aor0ID int,
> @.Aor1ID int,
> @.Aor2ID int,
> @.Aor3ID int
>as
>print getDate()
>UPDATE aggQuotaAtt SET
> OrderQty = txoQuota.OrderQty,
> Revenue = txoQuota.Revenue,
> HistoricalPercentage =>txoQuota.HistoricalPercentage,
> CurrentPercentage =>txoQuota.CurrentPercentage,
> ModifiedDate = GetDate(),
> AorID = mstrsCustomer.AorID,
> Aor0ID = mstrsCustomer.Aor0ID,
> Aor1ID = mstrsCustomer.Aor1ID,
> Aor2ID = mstrsCustomer.Aor2ID,
> Aor3ID = mstrsCustomer.Aor3ID,
> Aor4ID = mstrsCustomer.Aor4ID
>FROM aggQuotaAtt QuotaBase INNER JOIN txoQuota ON
>QuotaBase.ShipToID = txoQuota.ShipToID
> AND QuotaBase.PrdID = txoQuota.PrdID
> AND QuotaBase.PoolYearPeriodID =>txoQuota.PoolYearPeriodID
>INNER JOIN WaveAgg.dbo.mstrsCustomer mstrsCustomer ON
>mstrsCustomer.ShipToID = txoQuota.ShipToID
>WHERE txoQuota.PrdID = @.PrdID
> AND txoQuota.PoolYearPeriodID = @.PoolYearPeriodID
> AND (txoQuota.ShipToID = @.ShipToID OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE AorID = @.AorID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor0ID = @.Aor0ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor1ID = @.Aor1ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor2ID = @.Aor2ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor3ID = @.Aor3ID))
>
>
>
>and here is the second procedure. This is the one that
>takes less than a second:
>ALTER PROCEDURE [dbo].[Paul'sJunk2]
>as
>DECLARE @.PrdID varchar(18)
>DECLARE @.ShipToID int
>DECLARE @.PoolYearPeriodID int
>DECLARE @.AorID int
>DECLARE @.Aor0ID int
>DECLARE @.Aor1ID int
>DECLARE @.Aor2ID int
>DECLARE @.Aor3ID int
>SET @.PrdID = '20027-933'
>SET @.ShipToID = 0
>SET @.PoolYearPeriodID = 73
>SET @.AorID = 1
>SET @.Aor0ID = NULL
>SET @.Aor1ID = NULL
>SET @.Aor2ID = NULL
>SET @.Aor3ID = NULL
>UPDATE aggQuotaAtt SET
> OrderQty = txoQuota.OrderQty,
> Revenue = txoQuota.Revenue,
> HistoricalPercentage =>txoQuota.HistoricalPercentage,
> CurrentPercentage =>txoQuota.CurrentPercentage,
> ModifiedDate = GetDate(),
> AorID = mstrsCustomer.AorID,
> Aor0ID = mstrsCustomer.Aor0ID,
> Aor1ID = mstrsCustomer.Aor1ID,
> Aor2ID = mstrsCustomer.Aor2ID,
> Aor3ID = mstrsCustomer.Aor3ID,
> Aor4ID = mstrsCustomer.Aor4ID
>FROM aggQuotaAtt QuotaBase INNER JOIN txoQuota ON
>QuotaBase.ShipToID = txoQuota.ShipToID
> AND QuotaBase.PrdID = txoQuota.PrdID
> AND QuotaBase.PoolYearPeriodID =>txoQuota.PoolYearPeriodID
>INNER JOIN WaveAgg.dbo.mstrsCustomer mstrsCustomer ON
>mstrsCustomer.ShipToID = txoQuota.ShipToID
>WHERE txoQuota.PrdID = @.PrdID
> AND txoQuota.PoolYearPeriodID = @.PoolYearPeriodID
> AND (txoQuota.ShipToID = @.ShipToID OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE AorID = @.AorID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor0ID = @.Aor0ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor1ID = @.Aor1ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor2ID = @.Aor2ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor3ID = @.Aor3ID))
>.
>