Accelerating an update statement

I was wondering if anyone sees a way to make this faster, its not super slow(10-300 seconds) right now but it has to be run up to 40 times in a row with different variables which combined can take up to 60 minutes.

With CorrectTab(SumCol,Loc1,Loc2) as
(
Select Sum(coltobesum1),Loc1,Loc2
from [Dat1].dbo.Table1 where (CCol=@.cCol) and Quarter=@.Quarter and Year=@.Year and area='D'
Group By Loc1,Loc2
),
CombinedCorrectTab(SumCol2,SumCol,SumColPer,Loc1,Loc2) as
(
Select Sum(coltobesum2)*10 as SumCol2,Min(SumCol)
,((Min(SumCol)-Sum(coltobesum2)*10)/(Sum(coltobesum2)*10))+1.0
,Table2.Loc1,Table2.Loc2
from Table2,CorrectTab
where (OpCCol=@.CCol)
and Table2.Loc1=Table1.Loc1
and Table2.Loc2=Table1.Loc2 and newcoltobesum is null
Group by Table2.Loc1,Table2.Loc2
)
Update Table2
Set NewColtobesum=coltobesum2*SumColPer
from CombinedCorrectTab
where (OpCCol=@.CCol) and Table2.Loc1=CombinedCorrectTab.Loc1 and Table2.Loc2=CombinedCorrectTab.Loc2

How many rows are being updated? If it is one row at a time, you might build a table of @.cCol values to join to rather than doing it multiple times... This is especially true if each of these updates does a table scan.

Can you post the plan? Use:

set showplan_text on
go

<exec query>
go

set showplan_text off
go

|||The number of rows updated per run varies from 90 to about 1.5 million. I will post an execution plan shortly.|||is there a way to simplify the two CTe's using join into one then call the update|||

You can also use batch updates: place your update statement in a loop and set rowcount 1000 or 10000 (depending on what's faster)..

P.S. Nice nick

|||

You may also consider using a temporary table to hold the calculation values that does not change between various parameter invocations of the UPDATE statement. With the CTE approach, you are essentially doing the work every time you perform the UPDATE statement. So using temporary table will considerably reduce the time taken to run the UPDATE statement. Apart from this, there are other factors that affect the UPDATE statement performance:

1. Indexes on the table (one being updated). You might consider dropping unnecessary indexes

2. Does the table have triggers? What about the logic in the triggers?

3. If you are running the same UPDATE statement multiple times then you can also consider storing the different parameter values in a temporary table and joining against that as suggested by Louis. This will also help

4. Lastly, if you are updating large number of rows then consider running the UPDATE in batches using the TOP clause