Posted by Erland Sommarskog on 06/09/06 21:46

rdraider (rdraider@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]