Most efficient way to run update query

[rdraider]

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

[Erland Sommarskog]

Re: Most efficient way to run update query

rdraider (rdraider@sbcgl obal.net) writes:[color=blue]
> 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')[/color]

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@sommarsk og.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

[--CELKO--]

Re: Most efficient way to run update query

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.

[rdraider]

Re: Most efficient way to run update query

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" <esquel@sommars kog.se> wrote in message
news:Xns97DDF1D 29A419Yazorman@ 127.0.0.1...[color=blue]
> rdraider (rdraider@sbcgl obal.net) writes:[color=green]
>> 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')[/color]
>
> 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@sommarsk og.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
>[/color]