4

Some popular queries, like this one are returning:

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

Is it a temporary issue? Do I need to run the query at the certain time of the day with lower server load? If yes, what time? Or is it the kind of queries that will never work again, due to the growing amount of data to process?


[edit]
For the specific one above, I've managed to rewrite it from scratch like that: https://data.stackexchange.com/stackoverflow/query/771259/top-50-most-prolific-editors

  • Getting rid of the magic columns (e.g. [User Link]) should make the queries work again. – ShaWiz Dec 16 '17 at 16:27
  • @ShadowWizard, no, it didn't work to only remove the AS [User Link] – Cœur Dec 16 '17 at 16:32
  • Hmm... yeah this one is different, it's due to the Select Count(*) from the posts table which by now is huge on SO. I'm afraid there's no quick fix for this, need to write a different query. – ShaWiz Dec 16 '17 at 16:42
1

Is it a temporary issue?

It could be, but if you have run it twice or three times with the same outcome most data needed is probably now in cache so it won't fetch the data much faster. The query is highly likely at fault, not the resources available to get an outcome.

Do I need to run the query at the certain time of the day with lower server load?

It might matter for some queries but it will be so marginal that finding such time and then retrying is probably a waste of time.

If yes, what time?

On the SE network some scripts are scheduled to run around 03:00 UTC. You could argue that specific time might see the least number of users competing for the same resource. For Sunday 03:00 UTC this will no work though as in that timeslot SEDE is refreshed with recent backup.

Or is it the kind of queries that will never work again, due to the growing amount of data to process?

Yes, that is most likely. Inefficient queries that hit the Posts (or actually PostsWithDeleted), PostHistory and/or Users table will choke if they are joined with resultsets that can't be quickly sliced. The number of indexes used is low so if you have a where clause on a non-indexed column your query is doomed if you don't optimize the hell out of it.

If you're into SQL Query optimization you can obtain the query plan. Unfortunately the query needs to run to obtain the plan by means if the SEDE UI. However, you can obtain a text version of the execution plan by adding

SET SHOWPLAN_TEXT ON
GO

and set the output to text-only results. You can then analyze if you have options to reduce for example

|--Index Spool(SEEK:([StackOverflow].[dbo].[PostsWithDeleted].[LastEditorUserId]=[StackOverflow].[dbo].[Users].[Id] AND [StackOverflow].[dbo].[PostsWithDeleted].[DeletionDate]=NULL))                                                                         
    |--Clustered Index Scan(OBJECT:([StackOverflow].[dbo].[PostsWithDeleted].[UIX_PostsWithDeleted_Id]))   

that happen to be in the query you picked as an example. With this information you could even ask a question on SO or dba.se. You and others have already found ways to implement that particular query more efficient.

There is one other known issue for the “Execution Timeout Expired” and that is when you use one of the magic columns on a resultset with a lot of rows. See Out-of-resources when trying to run a popular DataExplorer query for details.

3

Some old queries are also very complicated queries. While they might have run many years ago, they no longer run on the much larger amount of data that now exists. They'd need to be rewritten to be much cleaner, or would need to be run on a private instance created from the data dump. For example, that query you linked runs three separate SELECTs with counts over the Posts table. That's quite intense, and if it's timing out now then it's never going to work again. But there's probably a much more efficient way of getting that data which won't time out.

  • Oh well, thanks, I've managed to reduce the number of SELECT for the example query from my question (see my edit). What I note from your answer is: if it's timing out now then it's never going to work again and They'd need to be rewritten. – Cœur Dec 16 '17 at 17:28
  • @Cœur If you still want the question/answer counts: data.stackexchange.com/stackoverflow/query/771271/… - Though you'd probably be better off looking for edits in the PostHistory table or something, not looking at the Posts table at all. – animuson Dec 16 '17 at 17:34
  • Thank you animuson for your CASE trick. My query is now much nicer and based on PostHistory (see latest revision). – Cœur Dec 16 '17 at 19:03
  • What would be a better way of writing this query? – Donald Duck Dec 17 '17 at 18:24

You must log in to answer this question.

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