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:
SELECT TOP(10) Tags.TagName, 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', ( SELECT AVG(post.Score+0.0) FROM Posts post,PostTags tag WHERE ( post.Id=tag.PostId OR post.ParentId=tag.PostId ) AND tag.TagId=Tags.Id ) 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 ON Questions.Id=PostTags.PostId INNER JOIN Tags ON PostTags.TagId=Tags.Id 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.