Hi All, Can any one tell me what i am doing wrong here , the following update for millions of records takes hell lot of time. How can i make this update faster. Thanks in advance. Kannan UPDATE BR SET PPO = ROUND(100 * ST.PPO, 2) , PSO = ROUND(100 * ST.PSO, 2) , KPO = ROUND(100 * ST.KPO, 2) , KSO = ROUND(100 * ST.KSO, 2) FROM TABLEA INNER JOIN ( SELECT ID , MAX(CASE WHEN COUNTRYID = 1 THEN STRENGTH ELSE 0 END) PPO , MAX(CASE WHEN COUNTRYID = 3 THEN SCOPE ELSE 0 END) PSO , MAX(CASE WHEN COUNTRYID = 4 THEN TOTAL ELSE 0 END) KPO , MAX(CASE WHEN COUNTRYID = 5 THEN VALUE ELSE 0 END) KSO FROM TABLEA INNER JOIN COUNTRY ON TABLEA.COUNTRYID = COUNTRY.COUNTRYID AND COUNTRYID IN ( 1, 3, 4, 5 ) ) ST ON
What I would try: 1. Move the creation of the Pivot table into it's own step. Throw it into a temp table or work table (#myTemp). Index it on ID. 2. Do the Rounding work as you are creating the pivoted work table (#myTemp). 3. Try batches of 1000. These will be moderately kinder to your transaction log and may help if you're hitting a hardware limit.
I agree with @willspurgeon, making the INNER SELECT a step in its own right to have the data in a temp table would be my choice the try next for an improvement. I'd also look to move the COUNTRYID filter into a WHERE predicate rather than in the JOIN. Are there many indexes on TABLEA? updates will have to update all indexes on this table too. If it is millions of rows deep and you are updating a lots of indexes then it will be slow. You may need to consider dropping the indexes while the update is actioned and then re create them after the work is done. The DDL of TABLEA would help us a lot