Update statement

greenspun.com : LUSENET : SQL Server Database Administration : One Thread

I am trying to update a column and set it to 0 where it is null. the server is a PIII 500 with 196MB RAM. SQL has 140 of it to itself. It is taking a very long time to update 290,570 recodes. I mean 4 hours.

I have a non clusted index on the field.

I have been watching the performance monitor ans SQL is taking 98% of the CPU time. The I/O Cach Hit Ratio is about 95.

Appreciate your input and ideas as how I can speed this process.

Thanks

-- Anonymous, October 28, 1999

Answers

Farshad,

Because the column can contain nulls, when you update it, SQL Server does a deferred update, that is, a delete followed by an insert.

You mention that you are replacing all the nulls with 0. If you can change the column to have the constraint NOT NULL, any further updates on that column can be made to be an "update in-place". Of course, in SQL 6.5, to do this you must create a new table with the NOT NULL constraint and copy the data over from the old table.

The SQL Server Books Online article, "Update Statement" provides this explanation of the rules for an "Update In-Place":

In SQL Server 6.0, various techniques are used to determine whether or not an UPDATE can be performed "in-place." When an "update-in-place" occurs, the overall performance of the UPDATE statement is improved because the update is direct (only the row modifications to the page are logged). The decision as to whether or not an update-in-place can occur is based on the following requirements. If these requirements are not true, a deferred update will take place. A deferred update is a delete followed by an insert.

7 The UPDATE cannot affect the column(s) that participates in a clustered index.

7 The table cannot have an UPDATE trigger.

7 The table cannot be marked for replication.

7 For single-row updates:

7 The updated column(s) can be variable length, but the new total row size must fit on the same page as the old row.

7 The updated column(s) can participate in a nonunique nonclustered index only if the index key is a fixed-width column.

7 The updated column(s) can participate in a unique nonclustered index only if the index key is fixed-width and the WHERE clause criteria must have an exact match (based on an unique index match>the index could be the same as the updated column).

7 The new row cannot include differing bytes by more than 50 percent of the original row size, and the total number of discontiguous differing bytes is not more than 24.

7 For multirow updates:

7 The updated column must be fixed length.

7 The updated column cannot participate in a unique nonclustered index.

7 The updated column can participate in a nonunique nonclustered index only if the column is a fixed-width column (the index used to find rows cannot be the same as the updated column).

7 The table cannot include a column with the timestamp datatype.

To see the update strategy used (DIRECT or DEFERRED), set the SHOWPLAN session setting. For details, see the SET statement.

Hope this helps,

Eric

-- Anonymous, October 28, 1999


Moderation questions? read the FAQ