Trigger with Cursor - performance issue

[davidgoulet]

I am having a performance problem using a cursor. Looking for an alternative method, that I can understand.

I have two tables: 1. On order header table (opheadm) and
and order detail table (opdetm). I am trying to set the delivery method on the order header tables, based on the total weight of the order, which unfortunately is held against each detail line.

The trigger works O.K. - but the performance is very slow and slowing down.

Here is my trigger:

CREATE TRIGGER [weight_update] ON [scheme].[opdetm]
FOR INSERT, UPDATE
AS
IF (UPDATE(weight))

DECLARE @order_no char (10)
DECLARE @carrier char (10)
DECLARE @transaction_anals3 char (10)
DECLARE @delivery_method char (10)
DECLARE @weight FLOAT

DECLARE e1 cursor for
select h.order_no, h.carrier_code, h.delivery_method, h.transaction_anals3, sum(d.order_qty * d.weight)
from scheme.opheadm h, scheme.opdetm d, inserted i
where h.order_no = i.order_no
and d.order_no = i.order_no
group by h.order_no, h.carrier_code, h.delivery_method, h.transaction_anals3

OPEN e1

FETCH NEXT from e1 into @order_no, @carrier, @delivery_method, @transaction_anals3, @weight

WHILE @@FETCH_STATUS = 0

BEGIN

/* need to add delivery_method based on weight and other factors*/

IF @order_no like '%/%' set @delivery_method = 'PPD'
else
IF @weight > 750 and @delivery_method = ' ' and @transaction_anals3 = 'SALE' set @delivery_method = 'PPD'
else
IF @weight > 750 and @delivery_method = 'PDA ' and @transaction_anals3 = 'SALE' set @delivery_method = 'PPD'
else
IF @weight < 750 and @delivery_method = ' ' and @transaction_anals3 = 'SALE' set @delivery_method = 'PDA'
else
IF @weight < 750 and @delivery_method = 'PPD ' and @transaction_anals3 = 'SALE' set @delivery_method = 'PPA'
else
IF @transaction_anals3 = 'SAMPLE' set @delivery_method = 'PNA'

update scheme.opheadm
set delivery_method = @delivery_method
where scheme.opheadm.order_no = @order_no


FETCH NEXT from e1 into @order_no, @carrier, @delivery_method, @transaction_anals3, @weight

END
CLOSE e1
DEALLOCATE e1

 

[swampBoogie]

CREATE TRIGGER weight_update ON scheme.opdetm
FOR UPDATE
AS
IF UPDATE(weight)
update scheme.opheadm
set delivery_method = case when order_no like '%/%' then 'PPD'
                           when weight > 750 
                            and delivery_method  in (' ','PDA') 
                            and transaction_anals3 = 'SALE' then 'PPD'
                           when weight < 750 
                            and delivery_method in (' ','PPD')
                            and transaction_anals3 = 'SALE' then  'PDA'
                           when transaction_anals3 = 'SAMPLE' then 'PNA' end
  from scheme.opheadm inner join inserted on scheme.opheadm.orderno = inserted.orderno

The case for insert is similar

 

[arrowhouse]

Are you updating/inserting many rows at the same time (so the cursor is processing many rows) or is it just a single row update, or both that are slow?

Try adding the redundant join h.order_no = d.order_no to the cursor select so it becomes:

select h.order_no, h.carrier_code, h.delivery_method, h.transaction_anals3, sum(d.order_qty * d.weight)
from scheme.opheadm h, scheme.opdetm d, inserted i
where h.order_no = d.order_no
and h.order_no = i.order_no
and d.order_no = i.order_no
group by h.order_no, h.carrier_code, h.delivery_method, h.transaction_anals3

I think it is probable that there is a foregin key between these two & this can drastically change the way the query optimiser regards any indexes that are present.

It may be that you need to look at the indexing of the tables concerned too.

Not sure why you are grouping by the carrier_code as the variable doesn't seem to be used. I'd remove this if it is redundant.



You could also look at using something along the line of:

update scheme.opheadm
set delivery_method =
CASE
WHEN order_no like '%/%' THEN 'PPD'
WHEN weight > 750 delivery_method = ' ' and transaction_anals3 = 'SALE' THEN 'PPD'
WHEN weight > 750 and delivery_method = 'PDA ' and transaction_anals3 = 'SALE' THEN 'PPD'
WHEN weight < 750 and delivery_method = ' ' and transaction_anals3 = 'SALE' THEN 'PDA'
WHEN weight < 750 and delivery_method = 'PPD ' and transaction_anals3 = 'SALE' THEN delivery_method = 'PPA'
WHEN transaction_anals3 = 'SAMPLE' THEN delivery_method = 'PNA'
ELSE delivery_method
END
FROM
(select h.order_no, h.carrier_code, h.delivery_method, h.transaction_anals3, sum(d.order_qty * d.weight) as weight
from scheme.opheadm h, scheme.opdetm d, inserted i
where h.order_no = d.order_no
and h.order_no = i.order_no
and d.order_no = i.order_no
group by h.order_no, h.carrier_code, h.delivery_method, h.transaction_anals3 ) order_weight
WHERE scheme.opheadm.order_no = order_weight.order_no

So instead of processing row by row process all the inserted records in one step, but this may or may not help depending on what the issue is! (Sorry can't be more helpful)

Just out of interest is it possible that more than one h.order_no, h.carrier_code, h.delivery_method, h.transaction_anals3 combination
exists for a particular order? Neither the cursor or this statement takes account of this.

Good luck

 

[kutty007]

Hi!
I have an alternative for the cursor part

Good Luck

Karthik
---------------------------------------------------------
CREATE TRIGGER [weight_update] ON [scheme].[opdetm]
FOR INSERT, UPDATE
AS
IF (UPDATE(weight))

DECLARE @order_no char (10)
DECLARE @carrier char (10)
DECLARE @transaction_anals3 char (10)
DECLARE @delivery_method char (10)
DECLARE @weight FLOAT
DECLARE @TmpFK INT




select h.order_no, h.carrier_code, h.delivery_method,
h.transaction_anals3, sum(d.order_qty * d.weight) ,
'N' as Stat, IDENTITY (INT, 1,1) as [TmpPk]
INTO #T
from scheme.opheadm h, scheme.opdetm d, inserted i
where h.order_no = i.order_no
and d.order_no = i.order_no
group by h.order_no, h.carrier_code, h.delivery_method, h.transaction_anals3

SELECT @order_no = null

SET ROWCOUNT 1

SELECT @order_no = order_no, @carrier =carrier_code,
@delivery_method = delivery_method, @transaction_anals3 = transaction_anals3,
@weight = sum(order_qty * weight) , @TmpFK = TmpPK
FROM #T
WHERE stat = 'N'

SET ROWCOUNT 0

WHILE @order_no IS NOT NULL
BEGIN

/* need to add delivery_method based on weight and other factors*/
IF @order_no like '%/%' set @delivery_method = 'PPD'
else
IF @weight > 750 and @delivery_method = ' ' and @transaction_anals3 = 'SALE' set @delivery_method = 'PPD'
else
IF @weight > 750 and @delivery_method = 'PDA ' and @transaction_anals3 = 'SALE' set @delivery_method = 'PPD'
else
IF @weight < 750 and @delivery_method = ' ' and @transaction_anals3 = 'SALE' set @delivery_method = 'PDA'
else
IF @weight < 750 and @delivery_method = 'PPD ' and @transaction_anals3 = 'SALE' set @delivery_method = 'PPA'
else
IF @transaction_anals3 = 'SAMPLE' set @delivery_method = 'PNA'

update scheme.opheadm
set delivery_method = @delivery_method
where scheme.opheadm.order_no = @order_no

UPDATE #T
SET Stat = 'Y'
WHERE TmpPK = @TmpFK

SELECT @order_no = null

SET ROWCOUNT 1

SELECT @order_no = order_no, @carrier =carrier_code,
@delivery_method = delivery_method, @transaction_anals3 = transaction_anals3,
@weight = sum(order_qty * weight) , @TmpFK = TmpPK
FROM #T
WHERE stat = 'N'

SET ROWCOUNT 0

END

 

[davidgoulet]

To: SwampBoogie

Tried you suggestion, but application returns an error message when I add second detail order line.

 

[davidgoulet]

To: arrowhouse

Multiple users are adding order lines all the time. Yes, there are many lines on each order.

Tried you suggestion about adding redundant join - no improvement.


Tried you second suggestion, but application returns an error message when I add second detail order line.

No it is not possible that more than one h.order_no, h.carrier_code, h.delivery_method, h.transaction_anals3 combination
exists for a particular order? Neither the cursor or this statement takes account of this.

 

[davidgoulet]

TO kutty007

Tried you cursor, but get the following error when checking syntax - could figure out what the problem was.

Error 8155: No column was specified for column 5 of '#T'.

THANKS

 

[kutty007]

I'm sorry David. When a col name has to be specified for each col when you insert a computed value into a temp table. It escaped my attention as I didnt have your tables in my database and could not fully test it.

However I corrected it.

Regards
Karthik
-----------------------

DECLARE @order_no int, @carrier char(10), @delivery_method char(10),
@transaction_anals3 char(10), @weight Numeric(10,2), @TmpFK INT


select h.order_no, h.carrier_code, h.delivery_method,
h.transaction_anals3, sum(d.order_qty * d.weight) [Weight] ,
'N' as Stat, IDENTITY (INT, 1,1) as [TmpPk]
INTO #T
from scheme.opheadm h, scheme.opdetm d, inserted i
where h.order_no = i.order_no
and d.order_no = i.order_no
group by h.order_no, h.carrier_code, h.delivery_method, h.transaction_anals3

SELECT @order_no = null

SET ROWCOUNT 1

SELECT @order_no = order_no, @carrier =carrier_code,
@delivery_method = delivery_method, @transaction_anals3 = transaction_anals3,
@weight = weight , @TmpFK = TmpPK
FROM #T
WHERE stat = 'N'

SET ROWCOUNT 0

WHILE @order_no IS NOT NULL
BEGIN

/* need to add delivery_method based on weight and other factors*/
IF @order_no like '%/%' set @delivery_method = 'PPD'
else
IF @weight > 750 and @delivery_method = ' ' and @transaction_anals3 = 'SALE' set @delivery_method = 'PPD'
else
IF @weight > 750 and @delivery_method = 'PDA ' and @transaction_anals3 = 'SALE' set @delivery_method = 'PPD'
else
IF @weight < 750 and @delivery_method = ' ' and @transaction_anals3 = 'SALE' set @delivery_method = 'PDA'
else
IF @weight < 750 and @delivery_method = 'PPD ' and @transaction_anals3 = 'SALE' set @delivery_method = 'PPA'
else
IF @transaction_anals3 = 'SAMPLE' set @delivery_method = 'PNA'

update scheme.opheadm
set delivery_method = @delivery_method
where scheme.opheadm.order_no = @order_no

UPDATE #T
SET Stat = 'Y'
WHERE TmpPK = @TmpFK

SELECT @order_no = null

SET ROWCOUNT 1

SELECT @order_no = order_no, @carrier =carrier_code,
@delivery_method = delivery_method, @transaction_anals3 = transaction_anals3,
@weight = sum(order_qty * weight) , @TmpFK = TmpPK
FROM #T
WHERE stat = 'N'

SET ROWCOUNT 0

END