Solved

Improve update query performance with T-SQL

Posted on 2011-09-06
5
258 Views
Last Modified: 2012-05-12
I have a SQL table with millions of rows which I need to update. At the moment this is taking a massive amount of time which I am hoping can be improved. The SQL in question is just a basic update statment which is adding 50 to an existing number (as basic as it gets), but the scale of the change means this is taking ages to complete.

Another problem is that the change is causing the transaction log to fill up to maximum capacity, this is causing the script to end prematurly with a warning. This happens whether I have the change wrapped in a transaction or not, I have tried shrinking the database prior to the change but this still occurs either way.

I have also tried breaking the script up into portions using the ID field, i.e. update all rows where ID is like '1%', '2%' etc, but this still takes forever.

Does anyone have any idea how I can do this as efficiently as possible?
0
Comment
Question by:Blowfelt82
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 36488985
Can you post the current query?
0
 

Author Comment

by:Blowfelt82
ID: 36489029
UPDATE REGISTRATION SET REF_NO = REF_NO + 50

0
 
LVL 5

Accepted Solution

by:
DavidMorrison earned 500 total points
ID: 36489079
Is this field in any non clustered indexes?

if so dropping them, doing the update, and then re-adding will speed up the process.

the transaction log issue will be due to your logging mode I would imagine, if you can change this it would help.

Another thing to consider is partition switching

Thanks

Dave
0
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 36489093
Is refno an integer? Is there a pk or some kind of unique index? Is there a timestamp column also being updated?
0
 

Author Closing Comment

by:Blowfelt82
ID: 36489865
Indexes was the cause of this, after dropping them the script took seconds!
Cheers.
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question