Fetching posts in a given month with the StackExchange Data Explorer produces Execution Timeout Expired error

Question

Some years ago, I wrote a generic query to fetch posts in a given month.

WITH allData AS (
    SELECT
                ROW_NUMBER() OVER (ORDER BY P.Id) AS row
                , P.Id
                , P.PostTypeId
                , P.ParentId
                , P.AcceptedAnswerId
                , P.CreationDate
                , P.OwnerUserId
                , P.Tags
                , P.ClosedDate
    FROM        Posts    AS P
    WHERE       YEAR(P.CreationDate) = (##Year:INT?1##) AND MONTH(P.CreationDate) = (##Month:INT?1##)
)
SELECT      *
FROM        allData
WHERE       row    >= 1 + 50000*(##StartPage:INT?1## - 1)
ORDER BY    row

I have used this query numerous times over the years without issue.

Yesterday and today, however, the query gives me the error

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

Note
The problem seems to lie with the first WHERE clause. (WHERE YEAR(P.CreationDate) = (##Year:INT?1##) AND MONTH(P.CreationDate) = (##Month:INT?1##)). Without this, the query runs fine.

Answer 1

That query will do a full table scan on the view Posts (which is backed by the table PostsWithDeleted) because that table is lacking an index on the creationdate attribute, assuming you rewrite that query to make actual use of a potential index. That the query ran till now is just pure luck and beefy CPU and IO juice to spare.

I've gone ahead and made a few changes (slightly altering what you query) to at least make that query run. These changes were made:

• Instead of using Year and Month on the left hand side of the expression, build the start date of the month and calculate the end of the month so we can do a range check
• Split the CTE in Questions and Answers because PostTypeId has an index so a lower number of rows has to be to scanned, we hope (didn't happen)
• Use OFFSET and FETCH for paging purposes

This is what the query looks like:

declare @month datetime = '##Year##-##Month##-01'
declare @eomonth datetime = dateadd(month,1,@month)
declare @offset int = (50000*(##StartPage:INT?1## - 1)) 

;WITH allQuestions AS (
    SELECT
                --  ROW_NUMBER() OVER (ORDER BY P.Id) AS row
                -- , 
                  P.Id
                , P.PostTypeId
                , P.ParentId
                , P.AcceptedAnswerId
                , P.CreationDate
                , P.OwnerUserId
                , P.Tags
                , P.ClosedDate
    FROM        Posts    AS P
    WHERE       PostTypeId = 1
    and         P.CreationDate >= @month 
    and         P.CreationDate < @eomonth
),
allAnswers AS (
    SELECT
                --  ROW_NUMBER() OVER (ORDER BY P.Id) AS row
                -- , 
                  P.Id
                , P.PostTypeId
                , P.ParentId
                , P.AcceptedAnswerId
                , P.CreationDate
                , P.OwnerUserId
                , P.Tags
                , P.ClosedDate
    FROM        Posts    AS P
    WHERE       PostTypeId = 2
    and         P.CreationDate >= @month 
    and         P.CreationDate < @eomonth
),
allData as (
  select *
  from allQuestions
  union
  select *
  from allAnswers
)


SELECT      ROW_NUMBER() OVER (ORDER BY Id ASC) [row]
          , *
FROM        allData
ORDER BY    Id ASC  OFFSET @offset ROWS 
FETCH NEXT 50000 ROWS ONLY;

Today that query runs on Stack Overflow for 68376 ms which is just 50% over the max allowed 120,000 ms. That explains the Timeout error you got.

Keep in mind SEDE is updated once a week on Sunday.
Use the very useful SEDE Tutorial written by the awesome Monica Cellio.
Say "Hi" in SEDE chat.