By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,843 Members | 2,300 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,843 IT Pros & Developers. It's quick & easy.

Most efficient way to run update query

P: n/a
Hi all,
Any thoughts on the best way to run an update query to update a specific
list of records where all records get updated to same thing. I would think
a temp table to hold the list would be best but am also looking at the
easiest for an end user to run. The list of items is over 7000
Example:
update imitmidx_sql set activity_cd = 'O', activity_dt = '20060601',
prod_cat = 'OBS' where item_no = '001-LBK'
update imitmidx_sql set activity_cd = 'O', activity_dt = '20060601',
prod_cat = 'OBS' where item_no = '001-LYE'
update imitmidx_sql set activity_cd = 'O', activity_dt = '20060601',
prod_cat = 'OBS' where item_no = '001-XLBK'
update imitmidx_sql set activity_cd = 'O', activity_dt = '20060601',
prod_cat = 'OBS' where item_no = '001-XLYE'
update imitmidx_sql set activity_cd = 'O', activity_dt = '20060601',
prod_cat = 'OBS' where item_no = '002-LGR'
update imitmidx_sql set activity_cd = 'O', activity_dt = '20060601',
prod_cat = 'OBS' where item_no = '002-LRE'

All records get set to same. I tried using an IN list but this was
significantly slower:
update imitmidx_sql set activity_cd = 'O', activity_dt = '20060601',
prod_cat = 'OBS'
where item_no in
('001-LBK',
'001-LYE',
'001-XLBK',
'001-XLYE',
'002-LGR',
'002-LRE')
Thanks
Jun 9 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
rdraider (rd******@sbcglobal.net) writes:
Any thoughts on the best way to run an update query to update a specific
list of records where all records get updated to same thing. I would
think a temp table to hold the list would be best but am also looking at
the easiest for an end user to run. The list of items is over 7000
Example:
update imitmidx_sql set activity_cd = 'O', activity_dt = '20060601',
prod_cat = 'OBS' where item_no = '001-LBK'
...

All records get set to same. I tried using an IN list but this was
significantly slower:
update imitmidx_sql set activity_cd = 'O', activity_dt = '20060601',
prod_cat = 'OBS'
where item_no in
('001-LBK',
'001-LYE',
'001-XLBK',
'001-XLYE',
'002-LGR',
'002-LRE')


Yes, IN gives bad peformance here. Not so much because of the operation
itself, but the time it takes to compute the query plan. If you would
run exactly the same statement again, it would be snap. But obviously
there is little reason to do that.

Yes, a temp table to hold all the item numbers would be better.

You say that you are looking for something which is easy for an
end user to run. But does end user run the SQL directly? Is there
no GUI?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 9 '06 #2

P: n/a
Put the working data into a working table with an index on it and try
this:

UPDATE Imitmidx
SET activity_cd = 'O',
activity_dt = '2006-06-01',
prod_cat = 'OBS'
WHERE item_no
IN (SELECT item_no FROM WorkingData);

You can also add constraints for data scrubbing to WorkingData.

Jun 10 '06 #3

P: n/a
The end user is a customer and not familiar with SQL so I was looking for
the least number of steps. I could have created separate scripts or one big
script to create a temp table, populate the table, then update records. My
original script with 7000 lines of update statements seemed the easiest.

Thanks.

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
rdraider (rd******@sbcglobal.net) writes:
Any thoughts on the best way to run an update query to update a specific
list of records where all records get updated to same thing. I would
think a temp table to hold the list would be best but am also looking at
the easiest for an end user to run. The list of items is over 7000
Example:
update imitmidx_sql set activity_cd = 'O', activity_dt = '20060601',
prod_cat = 'OBS' where item_no = '001-LBK'
...

All records get set to same. I tried using an IN list but this was
significantly slower:
update imitmidx_sql set activity_cd = 'O', activity_dt = '20060601',
prod_cat = 'OBS'
where item_no in
('001-LBK',
'001-LYE',
'001-XLBK',
'001-XLYE',
'002-LGR',
'002-LRE')


Yes, IN gives bad peformance here. Not so much because of the operation
itself, but the time it takes to compute the query plan. If you would
run exactly the same statement again, it would be snap. But obviously
there is little reason to do that.

Yes, a temp table to hold all the item numbers would be better.

You say that you are looking for something which is easy for an
end user to run. But does end user run the SQL directly? Is there
no GUI?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Jun 13 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.