Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
biggs
Starting Member
10 Posts |
 Posted - 2006-04-08 : 05:22:01
|
| Hi. Forgive the stupid question, but I'm not a big DB guy and have been working since around 1PM Thursday afternoon on a time sensitive project.I need to add three currency fields to an 11 million record table and then force zeros into every record. Obviously I know how to add fields to a table.Whats the fastest way to insert the zeros in though? Doing 'Update Table Set Field1 = 0, Field2 = 0, Field3 = 0' will take hours.Thanks in advance. I really appreciate the help.Tony |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-08 : 06:25:28
|
Can you create the new fields with a DEFAULT of 0? If so it will happen as part of the addition of the new columns.Otherwise you can add the new columns as NULL and then do the UPDATE as you indicate."will take hours"If you've got a half decent server I would expect it to be able to update 11 Million rows in a couple of minutes ...You could do the update in batches:DECLARE @intRowCount intSET @intRowCount = 1 -- Force first iterationWHILE @intRowCount > 0BEGIN SET ROWCOUNT 50000 UPDATE U SET Field1 = 0, Field2 = 0, Field3 = 0 FROM MyTable AS U WHERE Field1 IS NULL AND Field2 IS NULL AND Field3 IS NULL SELECT @intRowCount = @@ROWCOUNT SET ROWCOUNT 0 -- Reset to ALL rows!-- Possible "sleep for 1 second" here to allow other processes accessEND Kristen |
 |
|
|
biggs
Starting Member
10 Posts |
Posted - 2006-04-08 : 06:45:53
|
| There are already records in the table. If you specify a default while creating a field in EM, it won't apply to existing records. I tested it myself and it didn't work.I'm not sure if it helps, but it's an older SQL 7 machine that I can't upgrade right now.I wrote a simple update query and fired it with QA. It's been running for 7 hours... Not sure why...How about using alter table? I'm just not sure how to specify the value for existing records.Thanks for help. |
|
|
|
biggs
Starting Member
10 Posts |
Posted - 2006-04-08 : 06:51:09
|
| Nevermind. Was working on another part of the project and just got back to focusing on this thing. I was going to do alter table with values.But it turns out EM will insert the value, but only if you say nulls arent allowed.This is what happens to a brain after nearly 48 hours with no sleep.Thanks. |
|
|
|
biggs
Starting Member
10 Posts |
Posted - 2006-04-08 : 06:56:54
|
| On a side note, why would doing an update query on an 11 million row table take hours? The values going in are static. So no joins or anything. Just simple 'update table set field = x' type stuff.The server is just a little practice server. But it's dual 3.2 P4s with a Raid5 array of 74gig raptors. Those are not quite server class HDDs, but they aren't exactly slow either. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-09 : 02:07:21
|
| "only if you say nulls arent allowed."Sorry, I should have said that."why would doing an update query on an 11 million row table take hours?"Some piece of hardware is slow! I'd check the Task Manager first off and see if its running at 100% CPU. I can't believe that it will be, so that means that either a) the disks are not fast enough [more correctly the rate at which data can be pulled off the disks, which in turn means the type of RAID array (and RAID5 isn't it!) and the number of spindles on the array) and b) the amount of memory for caching.Doing it in batches, as I suggested earlier, would ease the burden on the transaction log too. That's probably being extended numerous times (which is itself a surprisingly "expensive" operation for SQL Server) if you do it all-in-one-strike, which is creating additional burden at each extension, and also leaving you with an abnormal sized log file.Kristen |
|
|
|
|
|
|
|
|
Adding fields to a BIG table