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 asked 14 Sep '13, 01:30 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. answered 15 Sep '13, 10:33 Breck Carter |
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 answered 14 Sep '13, 20:49 mfkpie8 |
Surely not without getting more information, say a graphical plan...
This statement is the cause of system data is slow
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?
yes!
This is my trigger on condition that "act_recv_line type:"{cust_cs,qt,shipper,co_return} original trigger:
when act_recv_line type=cust_cs or qt exec:
when act_recv_line type=shipper exec:
when act_recv_line type=co_return
exec:
if a='1' then begin update set end elseif a='2' then begin update set else begin update set ..... endif