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

asked 14 Sep '13, 01:30

mfkpie8's gravatar image

mfkpie8
273667075
accept rate: 12%

edited 14 Sep '13, 20:41

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

(14 Sep '13, 05:44) Volker Barth

This statement is the cause of system data is slow

(14 Sep '13, 11:40) mfkpie8
Replies hidden

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?

(14 Sep '13, 12:23) Volker Barth

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
(14 Sep '13, 13:36) mfkpie8

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

(14 Sep '13, 20:43) mfkpie8

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.

permanent link

answered 15 Sep '13, 10:33

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

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
permanent link

answered 14 Sep '13, 20:49

mfkpie8's gravatar image

mfkpie8
273667075
accept rate: 12%

edited 14 Sep '13, 21:25

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×79

question asked: 14 Sep '13, 01:30

question was seen: 1,808 times

last updated: 15 Sep '13, 10:33