Slow sorting data by subquery - 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

General SQL Server Forums

New to SQL Server Programming

Slow sorting data by subquery

Author

Topic

waveform
Yak Posting Veteran

93 Posts

Posted - 2014-01-22 : 05:45:05

I have a simple query which finds the average answers to questions in a survey. The structure is as follows:


   |Questions      |Answers          |Participants     |Surveys
   |------------   |---------        |--------------   |--------
   |QuestionId     |QuestionId       |ParticipantId    |SurveyId
   |SurveyId       |ParticipantId    |SurveyId         |...
   |...            |Answer           |...

The query to get the average answer for a specific survey ID is:


SELECT AVG(CAST(a.Answer AS float)) AS AvgAns
FROM Answers a
INNER JOIN Questions q ON a.QuestionId = q.QuestionId 
INNER JOIN Participants p ON a.ParticipantId = p.ParticipantId
WHERE (p.SurveyId = 123)

This works as expected, and returns the answer in about 0.0 seconds, according to query analyser.

However, when I use the above query in an ORDER BY clause, the entire query takes several seconds to complete - ordering only a handful on selected survey IDs. For example:


SELECT s.SurveyId FROM Surveys s
WHERE (s.SurveyId IN(1061,1075,1079,1084,1088,1112,1187,1192,1197,1206,1208,1210,1211,1105,1095))
ORDER BY
(SELECT AVG(CAST(a.Answer AS float)) AS AvgAns
FROM Answers a
INNER JOIN Questions q ON a.QuestionId = q.QuestionId 
INNER JOIN Participants p ON a.ParticipantId = p.ParticipantId
WHERE (p.SurveyId = s.SurveyId))

The above query takes a couple of seconds. This seems weird, considering each individual AVG calculation only takes a split second (0:00 seconds in QA), and I'm only going through a handful of surveys.

Is there a better way to approach sorting data by a subquery like this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-22 : 06:36:27

[code]
SELECT s.SurveyId FROM Surveys s
INNER JOIN Participants p ON p.SurveyId = s.SurveyId
INNER JOIN Answers a ON a.ParticipantId = p.ParticipantId
INNER JOIN Questions q ON a.QuestionId = q.QuestionId
GROUP BY s.SurveyId
ORDER BY AVG(CAST(a.Answer AS float))
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

waveform
Yak Posting Veteran

93 Posts

Posted - 2014-01-22 : 08:48:07

Ah, thank you, that now runs in 0.00 seconds too! :)
It seems taking out the sub-query makes all the difference.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-23 : 05:58:28

cool
yep..that was the key
Using subquery as above is performance killer especially when dataset involved is huge

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources