-1

This is a Stack Exchange Data Explorer query which gets the top users on specific skill scores (example: JavaScript) based on a specific country (example: Egypt), and it took a lot of time until it gave Execution Timeout Expired. error, so I tried to limit the result to 100, but there isn't any progress.
How could it be improved?

This is the query link

This is the code.

SELECT ROW_NUMBER() OVER(ORDER BY Score DESC) AS [#], us.id [User Link],
       us.DisplayName,
       tuser.Score

FROM Users us

JOIN
(SELECT Answers.OwnerUserId AS UserId, SUM(Answers.Score) AS Score
     FROM Tags
    JOIN PostTags ON Tags.Id = PostTags.TagId
    JOIN Posts ON Posts.Id = PostTags.PostId
    JOIN Posts Answers ON Answers.ParentId = Posts.Id
   WHERE Tags.TagName IN ('javascript')
  GROUP BY Answers.OwnerUserId
) tuser ON tuser.UserId = us.Id

WHERE lower(us.Location) like '%egypt%'
ORDER BY Score DESC;

BTW, it was working fine a while ago, but it was taking a lot of time.

7
  • Perhaps you wanted to link to the actual query rather than to window where the query can be edited? Or maybe to a specific revision of this query.
    – Martin
    Commented Apr 30 at 16:33
  • @AaronBertrand, I tried but unfortunately no progress.
    – Sage
    Commented Apr 30 at 16:37
  • @Martin, I shared the edit link for the debugging purposes.
    – Sage
    Commented Apr 30 at 16:37
  • 4
    I do not have advice on this particular query - but perhaps for purposes of experimentation it is reasonable to try the query on a smaller site first - and only later on SO, if you see that you have query that runs well on a smaller site. You can use things like Country or tag name as parameters, so that you can easily change them - for example, such as here.
    – Martin
    Commented Apr 30 at 16:58
  • 2
    BTW you could also have a look at the queries suggested previously for this purpose: How do I get the highest posters for a certain tag in a specific country? and Query for getting top users on a specific tag by country sorted by tag score (Stack Overflow).
    – Martin
    Commented Apr 30 at 17:13
  • 1
    (Sorry for off: I think only very few user shares their location and thus the location-based data can not be very useful)
    – peterh
    Commented Apr 30 at 23:25
  • @Martin, These queries you shared look much faster, thanks for help.
    – Sage
    Commented May 1 at 18:05

1 Answer 1

11

I have made two versions that now work.

This is the version that performs "best" (still 106 seconds)

SELECT -- top 100 
       us.id
     , us.DisplayName
into #users       
FROM Users us
WHERE us.location is not null 
 and us.Location like '%egypt%' collate SQL_Latin1_General_CP1_CI_AS

create index #ix_users on #users(id)

SELECT -- top 100 
       ROW_NUMBER() OVER(ORDER BY Score DESC) AS [#]
     , us.id [User Link]
     , us.DisplayName
     , tuser.Score
FROM #users us
JOIN 
(
  SELECT Answers.OwnerUserId AS UserId, SUM(Answers.Score) AS Score
     FROM Tags 
    JOIN PostTags ON Tags.Id = PostTags.TagId
    JOIN Posts Answers ON Answers.ParentId = PostTags.PostId 
    inner join #users u on u.id = Answers.OwnerUserId
   WHERE Tags.TagName = 'javascript'
  GROUP BY Answers.OwnerUserId
) tuser ON tuser.UserId = us.Id

ORDER BY Score DESC;

I have made these changes:

  • Select the users into a temporary table for usage in a join later
  • remove the use of LOWER on the left side of the expression and replace it with a collation
  • Create an index on the temporary users table
  • Remove the unneeded join on Posts
  • Join on the temporary users to prevent unneeded rows in the subquery

This is the other version that is closer to the original query.

The execution plans on both queries aren't too different. All indexes are accessed with Seek operations and I doubt that gets much more effective.

Keep in mind SEDE is only updated once a week, on Sunday.
Use the neat SEDE Tutorial written by the awesome Monica Cellio.
Say "Hi" in SEDE chat.

3
  • does it help if you get the target tag ID outside of the query? remove a join and string comparisons?
    – starball
    Commented Apr 30 at 18:37
  • @starball there is an index on tags for tag_name and is used in a seek, so I don't think that will gain us much.
    – rene
    Commented Apr 30 at 18:52
  • Thanks @rene for help
    – Sage
    Commented May 1 at 18:06

You must log in to answer this question.

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