I want to use the Stack exchange data explorer in order to display some basic information about the top tags.
The query should print the number of questions and answers, the average number of answers per question and the average score of posts(questions and answers under such questions). The query is ordered by the total number of questions.
This is my SQL code:
COUNT(DISTINCT Questions.Id) AS 'Number of Questions',
COUNT(Answers.Id) AS 'Number of Answers',
(COUNT(Answers.Id)+0.0)/COUNT(DISTINCT Questions.Id) AS 'Answers per question',
FROM Posts post,PostTags tag
) AS 'average score of posts under this tag(questions and answers)'
FROM Posts Questions
LEFT JOIN Posts Answers
ON Answers.ParentId = Questions.Id
INNER JOIN PostTags
INNER JOIN Tags
GROUP BY Tags.Id,Tags.TagName
ORDER BY COUNT(*) DESC
You can look at the script in the data explorer here.
The problem is that the script times out(in around 100 seconds). If I remove the
OR post.ParentId=tag.PostId, it still takes around 25 seconds to run (for Stack Overflow) but it does not time out.
If I run my script on Super User, it does not time out. (It runs around 4 seconds, I think it is because Super User is smaller than Stack Overflow)
Is there a way to run optimize the script so that it does not time out on Stack Overflow without losing functionality and why does that simple
OR decrease the performance that much?
The OR is that answers do also count for the average score.
I've added the execution plan files here.