I have fired 3 update queries in my stored procedure for 3 different tables. Each table contains almost 2,00,000 records and all records have to be updated. I am using indexing to speed up the performance. It is quite working well with SQL Server 2008. A stored procedure takes only 12 to 15 minutes to execute. (updates almost 1000 rows in 1 second in all three tables)
But when I run the same scenario with SQL Server 2008 R2 then the stored procedure takes more time to complete execution. It's about 55 to 60 minutes. (updates almost 100 rows in 1 second in all three tables). I couldn't find any reason or solution for that.
I have also tested same scenario with SQL Server 2012, but the result is same as above.
Here is my 3 table update query in stored procedure.
if (select COUNT(*) from table where conditions)>0 begin update table1 set Coulmnname= @ColumnName where Conditions update table2 set Coulmnname= @ColumnName where Conditions update table3 set Coulmnname= @ColumnName where Conditions end
execution plan Image 1 Image 2