When performing this trigger is very slow to optimize can you help me

[ximen]

begin
 update act_recv_line set create_time = getdate(*),user_9=1-discount_detail.discount,disc_amt=goods_amt*(1-discount_detail.discount),sys_ar_amt=goods_amt-(goods_amt*(1-discount_detail.discount)),ar_amt=goods_amt-(goods_amt*(1-discount_detail.discount)) FROM  DBA.act_recv_line,DBA.discount_relation,DBA.discount_detail,dba.shipper,dba.co_return
where
((act_recv_line.shipper_no=shipper.trans_no and 
       shipper.customer_id=discount_relation.customer_id) or (act_recv_line.shipper_no=co_return.trans_no and 
       co_return.customer_id=discount_relation.customer_id)) and discount_relation.trans_no=discount_detail.trans_no and act_recv_line.part_id=discount_detail.part_iD and 
    act_recv_line.trans_no = new_act_recv_line.trans_no and
    act_recv_line.line_no = new_act_recv_line.line_no 
end

When performing this trigger is very slow to optimize can you help me! chang: if a='1' then begin update set
end elseif a='2' then begin update set
else begin update set
endif

[VolkerBarth]

Surely not without getting more information, say a graphical plan...

[ximen]

This statement is the cause of system data is slow

[VolkerBarth]

I don't think you can expect anyone to be able to analyse this statement without knowing the details of the according tables and their relationship and the overall number of rows etc. etc... - and "slow"/"very slow" is a rather relative term.

That being said, does the statement run faster if you split it into two by separating the ORed conditions?

[ximen]

yes!

This is my trigger on condition that "act_recv_line type:"{cust_cs,qt,shipper,co_return} original trigger:

 ALTER TRIGGER "tr_act_recv_line_in" after insert order 2 on
    dba.act_recv_line
    referencing new as new_act_recv_line
    for each row
    begin
      update act_recv_line set
        create_time = getdate(*) where
        act_recv_line.trans_no = new_act_recv_line.trans_no and
        act_recv_line.line_no = new_act_recv_line.line_no
    end

---

when act_recv_line type=cust_cs or qt exec:

update act_recv_line set
    create_time = getdate(*) where
    act_recv_line.trans_no = new_act_recv_line.trans_no and
    act_recv_line.line_no = new_act_recv_line.line_no

when act_recv_line type=shipper exec:

update act_recv_line set create_time = getdate(*),user_9=1-discount_detail.discount,disc_amt=goods_amt*(1-discount_detail.discount),sys_ar_amt=goods_amt-(goods_amt*(1-discount_detail.discount)),ar_amt=goods_amt-(goods_amt*(1-discount_detail.discount)) FROM  DBA.act_recv_line,DBA.discount_relation,DBA.discount_detail,dba.shipper
where
act_recv_line.shipper_no=shipper.trans_no and shipper.customer_id=discount_relation.customer_id and discount_relation.trans_no=discount_detail.trans_no and act_recv_line.part_id=discount_detail.part_iD and 
    act_recv_line.trans_no = new_act_recv_line.trans_no and
    act_recv_line.line_no = new_act_recv_line.line_no

when act_recv_line type=co_return

exec:

update act_recv_line set create_time = getdate(*),user_9=1-discount_detail.discount,disc_amt=goods_amt*(1-discount_detail.discount),sys_ar_amt=goods_amt-(goods_amt*(1-discount_detail.discount)),ar_amt=goods_amt-(goods_amt*(1-discount_detail.discount)) FROM  DBA.act_recv_line,DBA.discount_relation,DBA.discount_detail,dba.co_return
where
act_recv_line.shipper_no=co_return.trans_no and co_return.customer_id=discount_relation.customer_id  and discount_relation.trans_no=discount_detail.trans_no and act_recv_line.part_id=discount_detail.part_iD and 
    act_recv_line.trans_no = new_act_recv_line.trans_no and
    act_recv_line.line_no = new_act_recv_line.line_no

[ximen]

if a='1' then begin update set end elseif a='2' then begin update set else begin update set ..... endif

[Breck_Carter]

It helps to reformat the SQL so human beings can read it...

ALTER TRIGGER "tr_act_recv_line_in" after insert order 2 ON "DBA"."act_recv_line"
    referencing new as new_act_recv_line
    for each row
    begin
    if new_act_recv_line.trans_type ='shipper' then
      update act_recv_line 
         set create_time = getdate(*),
             user_9=1-discount_detail.discount,
             disc_amt=goods_amt*(1-discount_detail.discount),
             sys_ar_amt=goods_amt-(goods_amt*(1-discount_detail.discount)),
             ar_amt=goods_amt-(goods_amt*(1-discount_detail.discount)) 
        FROM DBA.act_recv_line,
             DBA.discount_relation,
             DBA.discount_detail,
             dba.shipper,
             dba.co_return
       where act_recv_line.shipper_no       = shipper.trans_no  
             and shipper.customer_id        = discount_relation.customer_id  
             and discount_relation.trans_no = discount_detail.trans_no  
             and act_recv_line.part_id      = discount_detail.part_iD  
             and act_recv_line.trans_no     = new_act_recv_line.trans_no  
             and act_recv_line.line_no      = new_act_recv_line.line_no

elseif new_act_recv_line.trans_type ='CO_return'  then
      update act_recv_line  
         set create_time = getdate(*),
             user_9=1-discount_detail.discount,
             disc_amt=goods_amt*(1-discount_detail.discount),
             sys_ar_amt=goods_amt-(goods_amt*(1-discount_detail.discount)),
             ar_amt=goods_amt-(goods_amt*(1-discount_detail.discount))  
        FROM DBA.act_recv_line,
             DBA.discount_relation,
             DBA.discount_detail,
             dba.shipper,
             dba.co_return
       where act_recv_line.shipper_no=co_return.trans_no   
             and co_return.customer_id=discount_relation.customer_id   
             and discount_relation.trans_no=discount_detail.trans_no   
             and act_recv_line.part_id=discount_detail.part_iD   
             and act_recv_line.trans_no = new_act_recv_line.trans_no   
             and act_recv_line.line_no = new_act_recv_line.line_no  
    else 
      update act_recv_line   
         set create_time = getdate(*) 
       where act_recv_line.trans_no = new_act_recv_line.trans_no   
         and act_recv_line.line_no = new_act_recv_line.line_no
    end if
    end

The first UPDATE statement appear to contain CROSS JOIN operation with dba.co_return, and the second UPDATE appears to contain a CROSS JOIN with dba.shipper because those two tables are not referenced by the respective WHERE clause. This may force the UPDATE statements to update the same row ... over ... and ... over ... again.

Instead of using the WHERE clause to code the join predicates, the ANSI JOIN operators with ON clauses are recommended. When JOIN operators and ON clauses are used, it is almost impossible to code accidental CROSS JOIN operations.

[ximen]

Thank you I have already written the trigger, but still have some questions: (the update ") of the action will be to trigger the execution is this why

 ALTER TRIGGER "tr_act_recv_line_in" after insert order 2 ON "DBA"."act_recv_line"
    referencing new as new_act_recv_line
    for each row
    begin
    if new_act_recv_line.trans_type ='shipper' then
      update act_recv_line set create_time = getdate(*),user_9=1-discount_detail.discount,disc_amt=goods_amt*(1-discount_detail.discount),sys_ar_amt=goods_amt-(goods_amt*(1-discount_detail.discount)),ar_amt=goods_amt-(goods_amt*(1-discount_detail.discount)) FROM  DBA.act_recv_line,DBA.discount_relation,DBA.discount_detail,dba.shipper,dba.co_return
    where
    act_recv_line.shipper_no=shipper.trans_no and 
           shipper.customer_id=discount_relation.customer_id and discount_relation.trans_no=discount_detail.trans_no and act_recv_line.part_id=discount_detail.part_iD and 
        act_recv_line.trans_no = new_act_recv_line.trans_no and
        act_recv_line.line_no = new_act_recv_line.line_no 
    elseif new_act_recv_line.trans_type ='CO_return'  then
    update act_recv_line set create_time = getdate(*),user_9=1-discount_detail.discount,disc_amt=goods_amt*(1-discount_detail.discount),sys_ar_amt=goods_amt-(goods_amt*(1-discount_detail.discount)),ar_amt=goods_amt-(goods_amt*(1-discount_detail.discount)) FROM  DBA.act_recv_line,DBA.discount_relation,DBA.discount_detail,dba.shipper,dba.co_return
    where
    act_recv_line.shipper_no=co_return.trans_no and 
           co_return.customer_id=discount_relation.customer_id and discount_relation.trans_no=discount_detail.trans_no and act_recv_line.part_id=discount_detail.part_iD and 
        act_recv_line.trans_no = new_act_recv_line.trans_no and
        act_recv_line.line_no = new_act_recv_line.line_no  
    else 
    update act_recv_line set
        create_time = getdate(*) where
        act_recv_line.trans_no = new_act_recv_line.trans_no and
        act_recv_line.line_no = new_act_recv_line.line_no   end if
    end