April 5, 2005 at 11:48 am
The update statement follows:
Update factmonth
set OnHandU=s2.Units,OnHandC=s2.Cost,OnHandR=s2.Retail
from factmonth
join sourcetable s2
on s2.weekenddate=factmonth.weekenddate
and s2.sku=factmonth.itemid
and s2.store=factmonth.store
Pretty simple right? Well factmonth has 20 million rows and sourcetable has 5.5 million rows. This takes anywhere from two hours to 15 hours to complete. The process seems to go to sleep after about 30 minutes and the cpu goes from 3% to 25% alternating with the disk queue going from about 30 to .2. Both tables have clustered indexes, and I have tried using a covered index on the sourcetable with no effect. If the source table has as little as 200,000 rows this update procedure will only take about 40 seconds. However, somewhere around 4 to 5 hundred thousand rows and this procedure falls asleep and takes an indeterminate amount of time to complete. The box is a 2 way Xeon ~2700ghz with the data file striped accross 6 drives. The data file is 400 gigs and the log file is 5 gigs, and niether is full. The waittype that is being reported most is iopagelatch, but the ops guys are convinced that it could not be the hardware. Further, I don't suffer hard disk problems with other operations. For instance I can insert all 20 million rows from one table into another table in a matter of minutes. I wonder if there is something I am missing, or if this transaction is just to large and I should break into smaller ones. Any help or suggestions would be much appreciated.
April 5, 2005 at 11:56 am
Couple of things that I can think of offhand.
Firstly, check the database recovery model. If you can set that to simple recovery then it will run significantly faster. Secondly, are you running this in a transaction? If you are then try without.
NOTE: This is not the best idea from a recovery standpoint, but from a performance perspective it will make a difference.
April 5, 2005 at 12:17 pm
The existing contents of the 3 columns being updated can have an affect on performance. If they are previously NULL and you're updating any of them to non-NULL, it changes the UDPATE from an in-place update on each data page, to requiring a behind the scenes DELETE/INSERT pair on each record.
April 5, 2005 at 12:24 pm
One thing that can slow this to a halt is that the transaction log will have to grow a lot to do the update of ALL rows. That's why Nicholas was suggesting to use simple recovery. Another good idea would be to change the size of the log to make sure that it doesn't have to grow while the transaction is being done.
Another thing that could help you is to do the update in small batches as you suggested. Maybe you could enter all the ids of the rows to update in a new table. Then select the first 10k rows from that table order by id as a derived table and join that to the main update so you update just those rows. Delete the first 10k rows from that table. Wait 1 sec to let other transaction go through and then start again. I presume that with the wait, it would still take at least 1 hour to perform, but the server wouldn't be affected a lot by this load and the users wouldn't see any difference.
April 5, 2005 at 12:41 pm
Thank you for the suggestions so far. It is in simple mode, and the log file is 5 GB - it never needs to grow. I have a solution that breaks it down into 170 thousand separate transactions (based on sku#), but I really didn't want to go this route if I didn't have to.
Another interesting thing in regards to Old Hand's post is that the same type of problem occurs with the insert statement when it contains more than about half a million records. I was thinking that Nulls might have something to do with the slowness, but the insert statement ends up being just as slow. One more piece of information that might be causing goofyness is the width of the table. While the factmonth table is only joining 3 columns and getting 3 other columns updated, it does have 39 columns total. Most of these other columns contain null values for most of the 20 million records.
April 5, 2005 at 12:55 pm
One thing I forgot to mention is that maybe the tempdb needs to grow to accomodate for this huge transaction. Maybe somebody else can confirm this.
April 5, 2005 at 1:01 pm
Ahh, now that's a possibility. The TempDB is on another file, and I know it is sustaining much activity in this process, but I haven't checked it's size or really monitored it any. I will do that right now.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply