Board index MS SQL Server

Efficient Update

Efficient Update

Post by walter bogg » Wed, 23 Apr 1997 04:00:00



Assuming I have to update at least one column, how much of a performance
problem is it to update all of the columns even if they didn't change?

I have stored procedures doing my updates, which means I can either pass in
and update every column value whether it changed or not, or just pass the
values that changed and then do a separate UPDATE statement for each
parameter that isn't NULL -- all  updating the same row, all wrapped in a
transaction.

Is there a philosophy on this? Thanks.

 
 
 
Top

Efficient Update

Post by Gianluca Hot » Thu, 24 Apr 1997 04:00:00




Quote:> Assuming I have to update at least one column, how much of a performance
> problem is it to update all of the columns even if they didn't change?

> I have stored procedures doing my updates, which means I can either pass
in
> and update every column value whether it changed or not, or just pass the
> values that changed and then do a separate UPDATE statement for each
> parameter that isn't NULL -- all  updating the same row, all wrapped in a
> transaction.

> Is there a philosophy on this? Thanks.

Depending on various rules the update can be done in-place or deferred.
In-place updates (or direct updates) are much faster since
only row modification of the page are logged in the transaction log.
Deferred updates translates in a delete/insert. Updating all columns
is always processed as deferred update. You may want to check the
following documents in SQL Server Books Online:

1) Transact SQL Reference, UPDATE statement at the paragraph
   "Rules for an Update-in-place"
2) Administration Companion, Troubleshooting, Chapter 23
   "The update mode is deferred" and "The update mode is direct"

Best regards,
--                                                  __

Technical Service Manager at Alphasys srl         / /\/ /
Microsoft Product Specialist                      \ \/ /\
Currently rent by Ekar srl                         \__/\ \
PGP mail encouraged. Finger for key.           WWW.ALPHASYS.IT
PGP Fingerprint: BA 9F 9E 9E A9 2C 9D 98  92 54 D1 90 30 1E 8B 94

 
 
 
Top

1. Efficient updating

Just had a question about improving the performance of a update script that
I'm putting together. I have a database used for importing and scrubbing
large amounts of data.  When the data is imported, all null dates are
converted to 12/21/1899 by the driver used to import the data.  What I need
to do is check all date columns and convert the invalid dates back to null.
There are two facts that hold true for the data and the database schema.
First of all, no valid dates will every have a year of 1899.  Also, the
schema is designed so that all fields that need to have the the date checked
will start with d, no other columns will though.  Finally, I'm using a
cursor in my script and I hear a lot of people talking down about using them
saying temp tables provide better performance.  If this is applicable to
what I'm doing would someone be able to show me an example?




-- Since all fields that are going to be smalldatetime start with D,
-- only grab those column names and the tables they are in
DECLARE tblList CURSOR FAST_FORWARD FOR
 SELECT table_name, column_name FROM INFORMATION_SCHEMA.Columns
  WHERE Column_Name Like 'D%'

 OPEN tblList

 FETCH NEXT FROM tblList


  BEGIN
   -- create the update statement. This statement will update any dates that
have 1899 as the year


   -- execute the statement

   FETCH NEXT FROM tblList

  END

 -- make sure to cleanup the cursor !!!!
 CLOSE tblList
 DEALLOCATE tblList

2. OLAP NEWBIE: How to define an time interval but not just one date ?

3. Request for info: Efficient updates on Materialzed Relations.

4. Defining Cursors for accessing a MYSQL db

5. Is this an efficient way to update?

6. VA-Arlington-259593--ORACLE-RDBMS-UNIX-IBM Mainframe-CASE Tools-Oracle Developer

7. An efficient way for updates

8. Q: To VarChar Or To Char?

9. Efficient Remote Updating

10. Efficient strategy needed to update remote server

11. MOST EFFICIENT BULK UPDATE?

12. Most efficient large update?

13. efficient view updates?


All times are UTC
Board index