Return to Revisions

I think I've cracked this one, and I've got a few tips to offer that might be able to help with speeding up SEDE queries for you in the future as well.

Here's my modified version of your query:

DECLARE @TagId INT = (SELECT Id FROM Tags WHERE TagName = ##tag:string?r##);

SELECT C.Id, C.Score, C.Text
INTO #Subset
FROM Comments C
INNER JOIN Posts Q ON Q.Id = C.PostId
INNER JOIN PostTags PT ON PT.PostId = Q.Id
WHERE PT.TagId = @TagId

SELECT 
  CONCAT('site://posts/comments/', Id, '|Link') AS Link, 
  Score, 
  Text
FROM #Subset
WHERE UPPER(Text) LIKE UPPER('%##CommentText?Hello##%')
ORDER BY Score DESC

Updates I made:

1. Locate the tag's ID first, and then JOIN on the numerical ID rather than the string
   • This is a faster joining mechanism than using string keys, in my experience
   • This also allows us to avoid the third JOIN on the Tags table
2. Throw the initial results set (comments on questions with [tag]) into its own temporary table
   • This allows us to do operations on the smaller table containing just results rather than the enormous Comments table (which is 89 million records strong)
3. Manually create the "magic link"
   • For some reason, manually creating a custom "magic link" with the ${link}|${link text} syntax is much faster than relying on the built-in magic link syntax

All of these are techniques I've used and seen others use with SEDE in the past to help avoid the timeout; I can't vouch for how bullet proof they are for all cases, but I've had enough luck with them in the past to be able to recommend them here.

With these modifications, I was able to successfully run it on Stack Overflow's biggest tag (javascript) without hitting the timeout, but your mileage might vary.