Vectorization - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Vectorization

Author

Topic

jon3k
Starting Member

49 Posts

Posted - 2003-11-03 : 11:55:17

Lets say you had a web based application, two tiers, one web server and one SQL server.

If I wanted to update all records in a table where state is florida and set them to say california, I would perform this query:

UPDATE [table]
SET State = 'CA'
WHERE State = 'FL'

But I've heard this is a bad idea, and you should do this:
SELECT ID from [table]
WHERE State = 'FL'

Then, in the web application software:
For each record in query:
UPDATE [table]
SET State = 'CA'
WHERE ID = [value from loop]
Next

Now, which is more efficient? Does it depend on the hardware? How do you determine when and where you should vector the processing, on the sql server or the webserver? Just trial and error?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-03 : 13:24:06

Do the first!!! Did someone seriously tell you a single set-based update is a bad idea and processing 1 row at a time is better?

To use a variation of the "salt shaker" analogy I've heard from a few gurus here:

The "update one value at a time" approach is like taking marbles out a bag, one at a time, painting each one individually, and then putting each one back.

The UPDATE all at once is like adding paint to the bag and just shaking them up.

- Jeff

homam
Starting Member

31 Posts

Posted - 2003-11-03 : 17:52:28

Updating each record individually is a very bad idea indeed. But if you have a huge, hot table and you're afraid your update statement might cause serious locking or a transactin log gone haywire, then you can batch the updates (e.g. 1000 rows at a time):


declare @updated_rows int

-- Update one thousand per batch:
set rowcount 1000

set @udpated_rows = 1

while @updated_rows > 0
begin
	update my_table set state = 'CA" where state = 'FL'
	set @updated_rows = @@rowcount
end

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-03 : 18:17:17

good point ... very nice idea; i never thought of that!

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources