Can any one tell me what i am doing wrong here , the following update for millions of records takes hell lot of time.
What I would try:
answered Oct 13 '12 at 07:26 PM
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
answered Oct 14 '12 at 10:07 AM