Search for comments on posts with a certain tag based on a keyword

Question

I am trying to get the comments on posts with a specific tag which include a phrase or a keyword of interest using SEDE. Here's my first attempt: https://data.stackexchange.com/stackoverflow/query/1782303/search-comments-by-tags-and-a-keyword

SELECT C.Id AS [Comment Link], C.Score [Score], C.Text [Text] 
  FROM Comments C
    inner join Posts Q on Q.Id = C.PostId
    inner join PostTags PT on PT.PostId = Q.Id
    inner join Tags T on T.id = PT.TagId 
  WHERE UPPER(C.Text) LIKE UPPER('%##CommentText##%')
    AND T.TagName = ##tag:string?r##
  ORDER BY Score DESC

However, this keeps returning an error:

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

How can I optimize this query?

Answer 1

You can cut back on an extra join because PostTags has the PostId and you don't seem to project any attributes from the Post table. Furthermore improve the LIKE performance with a COLLATE instead of using UPPER(). That is a pretty expensive function to run over a huge table.

This is the trimmed down version, including the handcrafted Link that is also used by zcoop98 in their answer.

SELECT 
       concat('site://posts/comments', C.Id, '|', c.text) [Link]
     , C.Score [Score]
     , C.Text [Text] 
  
  FROM Comments C
    inner join PostTags PT on PT.PostId = c.PostId
 
  WHERE C.Text LIKE '%##CommentText?reproducible##%' collate sql_latin1_general_cp1_ci_ai
    AND PT.TagID = ##TagID?4452## -- 4452 is the TagID for [r]

If this still times out for larger tags, consider adding a TOP 2000 and sensible Order By that fits your use case.

Here is the execution plan of above query:

And here is the Execution Plan of a version of your query:

Keep in mind SEDE is updated once a week on Sunday.
Never forget that Monica Cellio created the awesome SEDE Tutorial.
Say "Hi" in SEDE chat.

Answer 2

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.