SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Accelerate a query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kwacz23
Starting Member

44 Posts

Posted - 02/28/2013 :  08:58:32  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3695 Posts

Posted - 02/28/2013 :  09:05:11  Show Profile  Reply with Quote
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 - 02/28/2013 :  09:32:38  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3695 Posts

Posted - 02/28/2013 :  09:44:57  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000