Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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

3873 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

3873 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  
 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.02 seconds. Powered By: Snitz Forums 2000