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 2008 Forums
 Transact-SQL (2008)
 Accelerate a query

Author  Topic 

kwacz23
Starting Member

44 Posts

Posted - 2013-02-28 : 08:58:32
Hello

Could you help me to accelerate a below query?
UPDATE a_stg_value_descrription
SET id_part_number = (SELECT id FROM (SELECT DISTINCT A.id , B.attribute_id FROM a_stg_PartNumber A , a_stg_value_descrription B WHERE
A.attribute_id = B.attribute_id ) C WHERE c.attribute_id = a_stg_value_descrription.attribute_id)

It tooks about 90 second

Regards

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-28 : 09:05:11
Try one of these. I think these are logically equivalent to your queries, but please examine and test to see if they really are:
UPDATE b SET 
id_part_number = a.ID
FROM
a_stg_value_descrription b
INNER JOIN a_stg_PartNumber a ON
a.attribute_id = b.attribute_id;

;WITH cte AS
(
SELECT DISTINCT
b.id_part_number,
a.ID
FROM
a_stg_value_descrription b
INNER JOIN a_stg_PartNumber a ON
a.attribute_id = attribute_id
)UPDATE cte SET id_part_number = id;
Go to Top of Page

kwacz23
Starting Member

44 Posts

Posted - 2013-02-28 : 09:32:38
Unfortunately I got error message

Msg 4403, Level 16, State 1, Line 1
Cannot update the view or function 'cte' because it contains aggregates, or a DISTINCT or GROUP BY clause, or PIVOT or UNPIVOT operator.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-28 : 09:44:57
Remove the DISTINCT from the second query.

You don't have to use both queries. Both are supposed to do the same thing; use whichever is faster and logically correct.
Go to Top of Page
   

- Advertisement -