-4
SELECT p.Title,p.Body,c.Text
  
FROM Posts p
JOIN Comments c ON c.PostId = p.Id
WHERE p.Body like '%Winter Bash%'
AND p.CreationDate < '2015-12-31'
And p.CreationDate > '2011-01-01' 
ORDER BY p.CreationDate ASC

I wanted to use this query to find all posts and comments related to Winter Bash in a certain period. But the SEDE just returns:

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

What is the problem and how can I fix it?

  • 2
    Why you think this is a bug? Any database will timeout with such a search. – Shadow Wizard Wearing Mask Oct 12 at 13:01
  • 1
    Also, this is not specific to Winter Bash at all. Just because your query happens to contain “Winter Bash” it’s not a reason to tag this question with winter-bash. – user289905 Oct 12 at 13:19
4

This clause WHERE p.Body like '%Winter Bash%' will do a index scan and by joining with the Comments table you get an insane amount of rows to check for.

enter image description here

It is worth noting that SQL queries have a command timeout of 120 seconds. This means that if the query didn't start returning results within 120 seconds it will throw you that error. Creating complex queries but also Magic Links can cause this condition. On Stack Overflow you often run into this if many rows need to be visited with only a low number of matches. Limiting the sets by applying extra conditions, preferable on columns that are in an index or adding a TOP can help here.

Could it be you've run that query on Stack Overflow? Because if I run this on Meta Stack Exchange it does return rows.

Here is a query that uses a tagfilter as well. It needs to juggle a bit with the parentid because answers don't have tags, so you need to find the question id so you can join correctly with the Tags table.

SELECT p.Title,p.Body,c.Text
  
FROM Posts p
JOIN Comments c ON c.PostId = p.Id
--  filter on tags
inner join posttags pt on pt.postid = coalesce(p.parentid, p.id) -- find question
inner join tags t on t.id = pt.tagid
-- 
WHERE p.Body like '%Winter Bash%'
AND p.CreationDate < '2015-12-31'
And p.CreationDate > '2011-01-01' 
AND t.tagname like 'winter-bash%'
ORDER BY p.CreationDate ASC
| improve this answer | |

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .