update statement

Question

hi i am updating table which has almost 550 millions record its almost 1 day and 7 hrs but it still executing 

 

anybody can plz help me how long it will take more

Answer 1

Hello banty:

Welcome to the Transact SQL Forum.  Here are a few things to get you oriented.  First, give a look at these two guidelines for posting questions in the Transact SQL Forum:

MSDN Transact SQL Forum Posting Guidelines:

   http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/af20783d-2489-46e4-b6f4-be0ab6d514cb

      Posted by Clifford Dibble

      http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/ce8989b5-dc58-47d8-af65-db5bcd4f7ee2

      Posted by Phil Brammer

Next, become aware of the code and samples that you post.  One thing that will help you get better answers for your questions is to make your source code more readable.  Therefore, this forum provides a code formatting tool.  You can find the code tool.  The code tool is identified on the tool pallet by an icon that looks like a tiny window with the symbols “</>”.  Use this tool to insert your sample code; this will help the people that read your code.

If you are getting some kind of error condition, please provide a full description of the error condition; if you are getting an error message from SQL Server itself, please provide the exact error message is generated.

Answer 2

Can you please show your update statement?

Also, rather than updating all 550mln. records at once you better divide the job into batches of, say, 10K records at a time. 

Take a look at the following blog

Processing hundreds of millions records got much easier

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 3

Check this solved thread

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/ceabbd48-1a2a-4a8f-b219-3c0a5ae89188

as well as check this

http://dfarber.com/computer-consulting-blog/2011/1/14/processing-hundreds-of-millions-records-got-much-easier.aspx

---

Yogesh Bhadauriya - Sr Software Engineer in Microsoft Technologies

Answer 4

I suggest to turn this update into batch updates. Also, do you have indexes in both tables on the ImpDate and Period_Date respectively?

Do you have a field in the Aggr_Geo_Target_Imps table that will tell you if a record was updated or not?

Also, how long a select statement takes (not the update)?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 5

Hi naomi I do have indexes on both tables

 

i am very new to sql can u plz help about how to write in batches

Answer 6

Have you checked the blog I gave a reference to and a thread referenced? It does show how to perform updates in batches. You do need to have a field, though, that can tell you if the record was updated or not.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 7

HI naomi yup period_ID will get value once is updated it is new cloumn in existing table which being updated it is null now select statement depend how many rows i am selecting generally for top 10000 rows it took normally 2 seconds

Answer 8

OK, try then

declare @cnt int

set @cnt = 1

while @cnt > 0
 begin
 update top (10000) a
   set period_ID=r.Day_Period_ID
from Ref_PeriodHierarchy r (nolock)
join Aggr_Geo_Target_Imps a
on a.impdate= r.Period_Date
where a.Period_ID IS NULL
set @cnt = @@ROWCOUNT
END

 

That's the basic algorithm of updating in batches.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 9

after updating 10000 recods what i have to do for next records

Answer 10

The above code will work for all records you need to update as it's a loop. You can also output the loop number and, say, after 10 executions do a break, then resume that loop.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 11

Hi niomi

i cancelled my querry but still its rollback do you know how long it will take

Answer 12

Did you read the blog? Do update for 10K records. Measure the time. Then multiply this value by (Count of All Records not updated/10000) * 10KUpdateTime * 2 -- I used 2 as a factor just in case to have bigger estimate.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog