CODEWITHSUNDEEP
sqldatabaseperformanceselectsql-update
user2706266
user2706266

Reputation: 469

Very slow Update Statement

I have the following update statement which runs several times and is very slow (20+ seconds). Any recommendations on how to improve its performance?

    update acc_item 
set means_pay_no = m_pay_no
where acc_item_no in (select distinct(acc.acc_item_no) 
                      from acc_item acc, (select accm.acc_item_no acc1,accm.acc_item_no_2 acc2
                                            from acc_item_match accm,acc_payment_item accp
                                            where accp.acc_payment_no=pay_no
                                            and (accm.acc_item_no = accp.acc_item_no or accm.acc_item_no_2 = accp.acc_item_no))
                      where acc.acc_item_no = acc1
                      or acc.acc_item_no = acc2)                    
and reversed_to_acc_item_no is null;

Upvotes: 2

Views: 152

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44316

Try this instead:

update acc_item 
set means_pay_no = m_pay_no
where exists (select 1
              from acc_item acc 
              where exists 
               (select 1
                from acc_item_match accm,acc_payment_item accp
                where accp.acc_payment_no=pay_no
                and accp.acc_item_no in (accm.acc_item_no, accm.acc_item_no_2)
                and acc.acc_item_no in (accm.acc_item_no, accm.acc_item_no_2)
                ) and acc.acc_item_no = acc_item.acc_item_no
              )
and reversed_to_acc_item_no is null;

Upvotes: 1

user2702337
user2702337

Reputation:

How about insert statement query in temp table?

select distinct(acc.acc_item_no) into #temptbl 
from acc_item acc, (select accm.acc_item_no acc1,accm.acc_item_no_2 acc2
from acc_item_match accm,acc_payment_item accp
where accp.acc_payment_no=pay_no
and (accm.acc_item_no = accp.acc_item_no or accm.acc_item_no_2 = accp.acc_item_no))
where acc.acc_item_no = acc1
or acc.acc_item_no = acc2


update acc_item 
set means_pay_no = m_pay_no
where acc_item_no in (select * from #temptbl)                    
and reversed_to_acc_item_no is null;

Upvotes: 1

Related Questions