Data Explorer (SEDE)

« first day (1871 days earlier) ← previous day next day → last day (462 days later) »

M--

19:23

@rene do you have time to answer yet another question of mine? meta.stackexchange.com/q/392953

Feeds

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

I am trying to get comments on posts with a specific tag which include a phrase or a keyword of interest using SEDE. Here's my first my 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 ...

Martin

19:37

Your query runs fine on a smaller site: data.stackexchange.com/mathoverflow/query/1782303/…

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 5 mins ago

Have you considered using something like this: `(q.CreationDate >= '##Date1?2019-01-01##') AND (q.CreationDate <= '##Date2?2021-01-01##')? So that the query only has to chek a smaller chunk of posts.

M--

19:53

@Martin great suggestion!!! I'll try that

I am also fine with getting a count for the comments which include the keyword, do you think that would help with the "runtime error"?

OK, limited time-frame did not resolve the issue i.sstatic.net/va6CK.png

Martin

20:20

I remembered this:

Oct 14, 2021 at 14:18, by Martin

> The use of a magic column like [User Link] in a query doesn't involve real magic but actually very hard work for the query plan processor.

But I have just tried removing [Comment Link] - and it did not help (on SO).

rene

comments is a large table and that upper(text) like upper() needs a full table scan. It has to visit all rows, get the string, change to upper, compare, do next. That burns IO and CPU cycles which are scarce resources

In general: don't do a function on the left-hand-side of the predicate , assuming the right-hand-side is a constant (or evaluates once to a constant)

relevant in this context as well: meta.stackexchange.com/questions/312509/…

M--

20:59

thanks everyone; besides getting my answer, I have learned a lot. Really appreciate it

« first day (1871 days earlier) ← previous day next day → last day (462 days later) »

all rooms