November 17, 2008 at 6:29 am
I want to add NOT NULL and DEFAULT (0) constraint on the columns in the below update query.
Prior to adding these constraints I need to update the existing NULL in these columns with 0. The table in the given query has 32334581 records and it's a HEAP.
I am executing following update statement given below.
UPDATE TRANS SET
ACOM_BILLRC = ISNULL(ACOM_BILLRC , 0),
ACOM_BILLRCV = ISNULL(ACOM_BILLRCV , 0),
ADTS_BILLRC = ISNULL(ADTS_BILLRC , 0),
ADTS_BILLRCV = ISNULL(ADTS_BILLRCV , 0),
DCOST_BILLRC = ISNULL(DCOST_BILLRC , 0),
DCOST_BILLRCV = ISNULL(DCOST_BILLRCV , 0),
MCOM_BILLRC = ISNULL(MCOM_BILLRC , 0),
MCOM_BILLRCV = ISNULL(MCOM_BILLRCV , 0),
MCOST_BILLRC = ISNULL(MCOST_BILLRC , 0),
MCOST_BILLRCV = ISNULL(MCOST_BILLRCV , 0),
MVATP_BILLRC = ISNULL(MVATP_BILLRC , 0),
MVATP_BILLRCV = ISNULL(MVATP_BILLRCV , 0),
MVATS_BILLRC = ISNULL(MVATS_BILLRC , 0),
MVATS_BILLRCV = ISNULL(MVATS_BILLRCV , 0),
UCOST_BILLRC = ISNULL(UCOST_BILLRC, 0),
UCOST_BILLRCV = ISNULL(UCOST_BILLRCV , 0),
VD_FEERC = ISNULL(VD_FEERC , 0),
VD_FEERCV = ISNULL(VD_FEERCV , 0),
VD_RATE = ISNULL(VD_RATE , 0),
VD_RATEV = ISNULL(VD_RATEV , 0) WHERE (
ACOM_BILLRC IS NULL OR
ACOM_BILLRCV IS NULL OR
ADTS_BILLRC IS NULL OR
ADTS_BILLRCV IS NULL OR
DCOST_BILLRC IS NULL OR
DCOST_BILLRCV IS NULL OR
MCOM_BILLRC IS NULL OR
MCOM_BILLRCV IS NULL OR
MCOST_BILLRC IS NULL OR
MCOST_BILLRCV IS NULL OR
MVATP_BILLRC IS NULL OR
MVATP_BILLRCV IS NULL OR
MVATS_BILLRC IS NULL OR
MVATS_BILLRCV IS NULL OR
UCOST_BILLRC IS NULL OR
UCOST_BILLRCV IS NULL OR
VD_FEERC IS NULL OR
VD_FEERCV IS NULL OR
VD_RATE IS NULL OR
VD_RATEV IS NULL )
The purpose of this tables is
While this statement is running the log file size and temp file size grows.
Can you all please provide me some inputs on this issue.
Thanks in advance.
November 17, 2008 at 7:20 am
The update is going to update every row in which any column happens to have a NULL. If the table is large and you have even a few columns that have a lot of NULL values, it could be a big transaction.
You may want to break it into the individual parts and update each column in it's own batch:
[font="Courier New"]UPDATE TRANS SET ACOM_BILLRC = 0 WHERE ACOM_BILLRC IS NULL
GO
UPDATE TRANS SET ACOM_BILLRCV = 0 WHERE ACOM_BILLRCV IS NULL
GO
...[/font]
This will cut the transaction size down to the largest transaction including the records for the column with the most NULL values. If your database recovery model is SIMPLE or you do transaction log backups between the batches, this will probably help. It will also be more efficient on your indexes (if you have any) because you will not be updating columns to their original value if they are not NULL.
November 18, 2008 at 9:19 pm
Thank you so much for the response.
I am working on ur suggestions and will post the findings.
November 20, 2008 at 7:23 am
If you have the disk space, I would just re-create the table with the updated values then drop the TRANS table and then rename NEW_TRANS to TRANS again. ie:
select into new_trans
select ACOM_BILLRC = ISNULL(ACOM_BILLRC , 0),
ACOM_BILLRCV = ISNULL(ACOM_BILLRCV , 0),
ADTS_BILLRC = ISNULL(ADTS_BILLRC , 0),
ADTS_BILLRCV = ISNULL(ADTS_BILLRCV , 0),
DCOST_BILLRC = ISNULL(DCOST_BILLRC , 0),
DCOST_BILLRCV = ISNULL(DCOST_BILLRCV , 0),
MCOM_BILLRC = ISNULL(MCOM_BILLRC , 0),
MCOM_BILLRCV = ISNULL(MCOM_BILLRCV , 0),
MCOST_BILLRC = ISNULL(MCOST_BILLRC , 0),
MCOST_BILLRCV = ISNULL(MCOST_BILLRCV , 0),
MVATP_BILLRC = ISNULL(MVATP_BILLRC , 0),
MVATP_BILLRCV = ISNULL(MVATP_BILLRCV , 0),
MVATS_BILLRC = ISNULL(MVATS_BILLRC , 0),
MVATS_BILLRCV = ISNULL(MVATS_BILLRCV , 0),
UCOST_BILLRC = ISNULL(UCOST_BILLRC, 0),
UCOST_BILLRCV = ISNULL(UCOST_BILLRCV , 0),
VD_FEERC = ISNULL(VD_FEERC , 0),
VD_FEERCV = ISNULL(VD_FEERCV , 0),
VD_RATE = ISNULL(VD_RATE , 0),
VD_RATEV = ISNULL(VD_RATEV , 0)
from Trans
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply