Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 enter image description here Image 1 enter image description here Image 2

share|improve this question

migrated from programmers.stackexchange.com Jun 9 at 13:54

This question came from our site for professional programmers interested in conceptual questions about software development.

3  
Can you at least give a rough view of the queries you are running. If people see it, they can at least get an idea of the algebraic forms of the queries and help you with a solution. –  randomA Jun 9 at 8:13
    
12-15 min for 6mil rows seems slow, never mind the 1-hour runtime. You may want to post your code (along with plans, etc) to get it reviewed or something. –  Clockwork-Muse Jun 9 at 11:59
1  
Your indexes are wrong. Every single Index Seek is doing a Key Lookup. If that Key Lookup is on 2 million rows (i.e. you'll do 2 million individual lookups), you're in for a whole world of slow query pain. –  Mark Sinkinson Jun 9 at 14:14
    
If you have three conditions which, together, cover the entire set of records, consider using a CASE in your SET so you only scan the table once instead of three times. –  JNK Jun 9 at 14:58
    
Thanks friends for giving your attention on my problem. Your suggestions are valuable for me. and i will keep it for performance. But my major question is that this query run in SQL Server 2008 in 12 to 15 mins. and any other version of SQL Server (like 2008 r2 or 2012) it takes 55 to 60 minutes. So i can't understand the reason for that. –  Ronak Mathia Jun 9 at 15:48

1 Answer 1

Yes it will be different. Every version will have tweaks to the optimiser, or code path, or caching algorithms or something. I would expect the later versions to run faster than the earlier ones.

I am using indexing to speed up the performance.

No, you're not. If every row in the table has to be updated then an index is, at best, irrelevant. If the index contains columns which you are updating then the index's pages also have to be updated incurring additional write overhead.

Have you checked the query plan for the three different instances? My guess is the 2008R2 has less memory available and the optimiser's choosing to spill to TempDB, or the operating system's forcing paging to the virtual memory pagefile. Or it may be that the transaction log files for the 8R2 instance started out small and what you're seeing is an artifact of autogrowth to accommodaate six million log records.

I would always recommend batching to my devs when processing this many records. Update the first N records in cluster key sequence. Then do the next N, and so forth until all 2M are done. "N" can be altered to give the best rows/minute on your particular installation, though the tens of thousands tends to be about right. In this way log files remain smaller, tranactional locking is reduced and it is restartable in the case of a failure.

share|improve this answer
1  
In fact, you may find that DROP'ing the indexes will speed the UPDATE up. Of course, re-creating the indexes afterward will take some time. You'll need to do some testing to see where the trade-off comes in. –  Brandon Jun 9 at 13:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.