Posted by Catch_22 on 10/29/06 22:37

Hi Roy,

Thanks for your response ...... to supply all the tables and code is
tough due to the volume.

The looping that I'm using is via a table variable

I create the table variable :

declare @t_ElligiblePromotions table
(ElligiblePromotionRowNum int IDENTITY (1, 1) Primary key NOT NULL ,
[prefix] int NULL,
[body] int NULL,
[entryrectype] varchar(3) NULL,
[DateMostRecentRevsion] smalldatetime NULL)

Insert the key records :

INSERT INTO @t_ElligiblePromotions
([prefix],
[body],
[entryrectype],
[DateMostRecentRevsion])
select distinct
t_Orders.prefix,
t_Orders.body,
@RunType_Order,
getdate()
from t_Orders

then loop through the variable


SELECT @ElligiblePromotions_RowCnt = (select min
(ElligiblePromotionsRowNum) from
@t_ElligiblePromotions)
SELECT @MaxElligiblePromotions = (select max
(ElligiblePromotionsRowNum) from
@t_ElligiblePromotions)

while @ElligiblePromotions_RowCnt <= @MaxElligiblePromotions
begin
select @Prefix = Prefix,
@Body = Body,
@entryrectype = entryrectype,
@DateMostRecentRevsion = DateMostRecentRevsion
from @t_ElligiblePromotions
where ElligiblePromotionsRowNum = @ElligiblePromotion_RowCnt

-- Match the record to the control table - then loop through each prize
draw to determine elligibility
--
BEGIN
EXEC @ErrorNum = proc_QualificationDetail @ControlID,
@Prefix, @Body,
@EntryRecType, @DateMostRecentRevsion, @Identity,
'FM', @Result
OUTPUT

SET @ElligiblePromotions_RowCnt =
@ElligiblePromotions_RowCnt
+ 1
END

I've been reading about parameter sniffing and might try some of the
suggestions that have been put forward for it as it is the statements
in the child procedure proc_QualificationDetail where the issues are
arising with the updates to the 2nd and 3rd table.

Regards,
Ian.

Roy Harvey wrote:
> Unless you provide table definitions and the code it is unlikely that
> anyone will be able to help very much.
>
> >I then loop through the table variable to process all the records with
> >the particular key value.
>
> It sounds as though this "process" is where things go bad, but all we
> know about the process is that it loops. Looping sounds like cursors.
> (Cursors are powerful and indispensable tools - but are often
> misused.)
>
> There are other possible hints in the description.... building a table
> of keys instead of (potentially) using a derived table or view, using
> a table variable with 3000 values, the fact that the processing of the
> different tables is similar but apparently different brings the
> cohesion of the procedure into question.... but without specifics it
> isn't even worth guessing.
>
> Roy Harvey
> Beacon Falls, CT

[Back to original message]