3

I am trying to use the data explorer to try and evaluate the prevalence of a phenomenon on (main) Stack Overflow where either the accepted or the top-voted answer is actually intrinsically flawed. Unfortunately, the query either times out with the message "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.", or it returns no result after a time suspiciously close to the timeout delay (120 seconds), where I know for a fact there is at least one result.

No matter, I'm going to limit the answers to a range, and make multiple queries… Nope, doesn't work either, even if I limit to 1 Id or one day, the query still times out. I tried simplifying the query. I tried limiting to the top ten. I tried querying in the middle of the (north American) night. I tweaked everything I could. I sacrificed files to the gods of databases. To no avail.

So, what the heck is wrong with either my query, SEDE, or possibly me?

(last parameters I tried with are 05/05/2009 to 06/06/2009, because I have no idea which date format SEDE actually accepts. Query is on the SO proper db.)

declare @startDate datetime = ##startDate##;
declare @endDate datetime = ##endDate##;

select top 10
  a.Id as [Post Link],
  c.text as [Reason]
from
    Posts q
  inner join
    Posts a
  on (a.ParentId = q.Id)
  inner join
    Comments c
  on (c.PostId = a.Id)
where
(a.CreationDate <= @endDate)
and (a.CreationDate >= @startDate)
and
((a.Id = q.AcceptedAnswerId)
or (a.Score = (
            SELECT TOP 1 Score
                FROM Posts otherA
                WHERE
                    otherA.ParentId = q.Id
                ORDER BY Score DESC))
)
and ((Upper(c.text) LIKE Upper('%race%'))
     or (Upper(c.text) LIKE Upper('%security%'))
     or (Upper(c.text) LIKE Upper('%vulnerability%')))
;
  • There is no index on Creationdate, you better use a.id > 2000000 (to only have recent posts) – rene Dec 3 '13 at 21:22
  • Upper is not needed I guess, the db collation is in CI mode I believe – rene Dec 3 '13 at 21:25
  • It is not you, this produces output – rene Dec 3 '13 at 21:35
2

Aha! Ranging by Id rather than date (while keeping Upper()) does help, but it actually works to avoid the timeout only if the Id range is hardcoded in the query. I shit you not, if I use @firstId/@lastId parameters I get a timeout (however small the range).

Removing Upper() also does help dramatically, such that in this case I can use a parametrised Id range. However, db collation does not appear to be in CI mode: if my query has "c.text like '%Sup%'" (in place of the other terms) over an appropriate Id range I get COM Exceptions in C# as an answer, however if I replace that by "c.text like '%sup%'", I get other results which do not include that answer.

Thanks for the help (especially @rene), while not all my problems are solved, now that I have examples of variants that do work I should be able to take it from there, rather than bouncing into walls in the dark.

You must log in to answer this question.

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