very slow query - help requested.

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

Transact-SQL (2000)

very slow query - help requested.

Author

Topic

KevinSean
Starting Member

2 Posts

Posted - 2004-04-30 : 22:32:56

I am trying to get the mode (most repeated number) for each testQuestion that users have answered for each test at a fiven location.

this query is horribly slow as I am dealing with 67,000 userTestQuestion Rows. Does anyone have any idea of how to make this thing more efficient?

Thanks in advance.

SELECT a.repeats, a.idtest, a.points
FROM (
SELECT test.id AS idtest, COALESCE(reviewedPoints, gradedPoints) AS points, COUNT(*) AS repeats
FROM test
INNER JOIN test_question ON test_question.idtest = test.id
INNER JOIN userTestQuestion ON userTestQuestion.idQuestion = test_question.idQuestion
WHERE test.locationID = 37
GROUP BY test.id, reviewedPoints, gradedPoints
) A, (
SELECT MAX(mode.repeats) AS repeats, mode.idtest
FROM (
SELECT test.id AS idtest, COALESCE(reviewedPoints, gradedPoints) AS points, COUNT(*) AS repeats
FROM test
INNER JOIN test_question ON test_question.idtest = test.id
INNER JOIN userTestQuestion ON userTestQuestion.idQuestion = test_question.idQuestion
WHERE test.locationID = 37
GROUP BY test.id, reviewedPoints, gradedPoints
) mode
GROUP BY mode.idtest) B where a.idtest = B.idtest AND A.repeats = B.repeats
ORDER BY a.idtest

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2004-05-01 : 00:44:02

Please post ddl, sample data, and sample results.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources