SQL Script optimization - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

SQL Script optimization

Author

Topic

zali_g
Starting Member

1 Post

Posted - 2007-08-29 : 04:28:52

Hello,

Does anyone can help me to optimize the following query. The query is generated by code and it makes union while the only changed parameter is 'Answer'. The problem is that sometimes the script contains above 200 times this union and i get an exception (Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (260) was exceeded).

SELECT AVG(NormalizedAnswer) as Average FROM
(
SELECT * FROM [V_QuestionnairesDataByBranches] WHERE QuestionnaireID IN
(SELECT QuestionnaireID FROM [V_VariableQuestionsData] WHERE SQClientID = 71
AND Answer = 1)
) as _Table GROUP BY QuestionID
Union
SELECT AVG(NormalizedAnswer) as Average FROM
(
SELECT * FROM [V_QuestionnairesDataByBranches] WHERE QuestionnaireID IN
(SELECT QuestionnaireID FROM [V_VariableQuestionsData] WHERE SQClientID = 71
AND Answer = 2)
) as _Table GROUP BY QuestionID

) AS allUnions WHERE Average>0

Thanks,
Tzali

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 04:39:37

This is you partly posted query. Where is the full query?

SELECT		AVG(NormalizedAnswer) as Average
FROM		(
			SELECT	*
			FROM	[V_QuestionnairesDataByBranches]
			WHERE	QuestionnaireID IN (SELECT QuestionnaireID FROM [V_VariableQuestionsData] WHERE SQClientID = 71 AND Answer = 1)
		) as _Table
GROUP BY	QuestionID

Union

SELECT		AVG(NormalizedAnswer) as Average
FROM		(
			SELECT	*
			FROM	[V_QuestionnairesDataByBranches]
			WHERE	QuestionnaireID IN (SELECT QuestionnaireID FROM [V_VariableQuestionsData] WHERE SQClientID = 71 AND Answer = 2)
		) as _Table
GROUP BY	QuestionID

) AS allUnions WHERE Average>0

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 04:44:09

Maybe this?

SELECT		b.QuestionnaireID,
		AVG(CASE
			WHEN d.Answer = 1 THEN NormalizedAnswer
			ELSE NULL
		END) AS Answer1,
		AVG(CASE
			WHEN d.Answer = 2 THEN NormalizedAnswer
			ELSE NULL
		END) AS Answer2
FROM		V_QuestionnairesDataByBranches AS b
LEFT JOIN	V_VariableQuestionsData AS d ON d.QuestionnaireID = b.QuestionnaireID
			AND d.SQClientID = 71
			AND d.Answer IN (1, 2)
GROUP BY	b.QuestionnaireID
ORDER BY	b.QuestionnaireID

E 12°55'05.25"
N 56°04'39.16"

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2007-08-29 : 09:20:55

Or maybe...



SELECT 
	Z.QuestionnaireId,
	Z.AnswerId,
	Average = AVG(NormalizedAnswer) 
FROM
	(
	SELECT 
		A.QuestionnaireId, 
		B.AnswerId, 
		A.NormalizedAnswer
	FROM [V_QuestionnairesDataByBranches] A
	Inner Join [V_VariableQuestionsData] B
	On A.QuestionnaireId = B.QuestionnaireId
	Where B.SQClientId = 71
	) Z
Group By Z.QuestionnaireId, Z.AnswerId
Having AVG(NormalizedAnswer) > 0
Order By 1, 2

Corey

snSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources