5

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?

2
  • 1
    That message means, essentially, that your query is taking too long, and that the system is cancelling it before it gets a chance to complete. You'll basically need to optimize your query and find a way to make it take less time; in this particular case, I'd wager it's due to your LIKE %...%, which is a really resource-intensive operation.
    – zcoop98
    Sep 11, 2023 at 19:34
  • @zcoop98 thank you. I have had encountered that error before, and you are right about its root, as this one works: i.sstatic.net/levqM.png but that's an important part of my query. I am wondering if there's a way to optimize the joins to make this less resource consuming.
    – M--
    Sep 11, 2023 at 19:39

2 Answers 2

7

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: plan with two tables, both index scanned

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

plan with 4 tables, 3 index seek, one index scan

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.

1
5
+500

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 () without hitting the timeout, but your mileage might vary.

3
  • 1
    I just figured it out too: i.sstatic.net/jMGdQ.png the join on tagids and tagnames was the main culprit.
    – M--
    Sep 11, 2023 at 20:16
  • 2
    don't do Upper() on both sides of the predicate, use a Collate: meta.stackexchange.com/a/119336/158100 in this case Text LIKE '%##CommentText?Hello##%' collate sql_latin1_general_cp1_ci_ai might run slightly better. Also relevant wrt magic columns: meta.stackexchange.com/a/284086/158100
    – rene
    Sep 11, 2023 at 20:17
  • 2
    Thanks again for your answer. I went with rene's answer since that seems to be a bit more optimized by only joining with PT, but I am going to borrow the first line of your query to make the final one more user friendly. Cheers.
    – M--
    Sep 11, 2023 at 20:52

You must log in to answer this question.

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