January 12, 2004 at 12:14 pm
I am updating a bit field in a table with 4.5 million rows (row length 3017 bytes, 60%+ varchar fields). There is a clustered PK & 5 other indexes. The server (sql server 2k, win server 2k, quad xeon, 4GB ram 400GB single channel raid 5 (4 disks)) is defragged & dbreindexed weekly. The query to reset the existing flag is;
update table1 set flag = 0
It takes 30 min to truncate and reinsert the entire table with the flag reset and another 30 min to recreate indexes so i dont see why it should take 5 hours to run query. I have tried dropping the bit field and adding a new one with a default which takes 30 min also. DB is default settings with simple logging only. Any suggestions to speed this up?
January 13, 2004 at 6:43 am
I'm not sure that these items would cause the query to take 5 hours, but you might want to check these. 1) Are there any triggers or default values on the table? That results in extra processing for each row update. 2) Is "flag" field indexed? If so, you may want to drop that index, do the update, then rebuild the index.
Hope this helps.
Mark
January 13, 2004 at 8:22 am
The difference between using DML and using TRUNCATE is that your update statement is fully logged. If only some of the values are non-zero, you could try something like this:
UPDATE Table1 SET Flag = 0
WHERE Flag = 1
Or, if the column contains nulls:
UPDATE Table1 SET Flag = 0
WHERE Flag = 1 OR Flag IS NULL
Otherwise, the alternatives are those you have found.
--Jonathan
January 13, 2004 at 8:24 am
How big is the transaction log on the table before you run the update and then after you run the update. If you regularly are shrinking the transaction log and it has to grow when you run this process that could explain the time it is taking for this to run.
eg. 25mg translog needs to grow to 2 gig for transaction growth rate is only 10% and logs and data on same disks. You shrink logs at end of process to save disk space.
The process has to keep growing the transaction which is very time consuming.
Hope this helps.
Tom
January 13, 2004 at 10:45 am
Transaction log is set to 1gb and regularly truncated, then reset to 1GB. The database is set to simple logging due to the large number of records updated/inserted. I am going to review the calculated column, the text field in the middle of the table and the very complex trigger firing into an archive database. The trigger doesnt fire for this particular update sine the bit flag is not considered a significant change. However updating other columns indexes (char/varchar) which do fire the trigger are significantly quicker than updating the bit flag. O/S & SQL fully patched.
January 13, 2004 at 10:56 am
I would test it to see if that is your problem. Run your update, and then do not shrink your log. Then run the update a second time and compare times. I would quess that it could be a significant performance gain.
My philosophy on transaction logs is if you can't control commits within the application, set the transaction log low run for a week to see how big it grows, and then leave it. If your transaction log is continually growing to 4gig and you keep shrinking it to 1gig, there is a serious performance hit to keep growing that log for your processing.
Thanks
Tom
January 13, 2004 at 11:00 am
Reply thx for the help.
I am going to try your suggestions out throughout this month, with a background trace on some key sp.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply