CODEWITHSUNDEEP
sqloptimization
MariusD
MariusD

Reputation: 129

long running sp

The following sp: I have a stored procedure which runs anywhere from 1/2 minute to 4 hours (during nightly processing):

update tableA
set tableA.Other_Flag_50 = isnull(Staging.other_flag_50, 0)
from tableA
inner join (
    select acct_nbr,
        appl_code,
        Other_Flag_50
    from tableB
) Staging on tableA.lnhist_acct_nbr = Staging.acct_nbr
    and tableA.lnhist_appl_code = Staging.appl_code

I ran Blocking reports in Profiler for 2 nights in a row, first at 10 minutes interval then at 5 minutes. The stored procedure never shows up as being blocked (but it blocks other queries).

Any ideas on optimizing this? Would creating a view with the join help? (acct_nbr, appl_code, Other_Flag_50 from tableB) Thanks!!

Upvotes: 0

Views: 95

Answers (2)

Adam Porad
Adam Porad

Reputation: 14471

Have you tried doing the INNER JOIN directly to tableB? 

UPDATE tableA 
SET tableA.Other_Flag_50=isnull(tableB.other_flag_50,0)
FROM  tableA 
INNER JOIN tableB
     ON tableA.lnhist_acct_nbr = tableB.acct_nbr 
     AND tableA.lnhist_appl_code = tableB.appl_code

Upvotes: 1

dance2die
dance2die

Reputation: 36915

Try using rowlock to prevent locking a whole table.

update tableA with (rowlock)
...

EDIT Not sure about other RDBMS but the answer I provided works for SQL Server

Upvotes: 0

Related Questions