Out-of-resources when trying to run a popular DataExplorer query

Question

So, I tried to run this query

Users with highest Socratic progress (socratic hopefuls)

which has many views (so I assume it's popular), but I get a message saying:

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

Now, true, that's not a trivial query, but is it really that bad? There isn't even a Join or anything.

Answer 1

The use of a magic column like [User Link], [Post Link], [Comment Link] and [Suggested Edit Link] in a query doesn't involve real magic but actually very hard work for the query plan processor. This is what happens in the SEDE internals.

Once the maximum of 50,000 rows are read from the SqlDataReader in PopulateResults the results are post-processed in ExecuteNonCached by calling ProcessMagicColumns which calls in the end for each magic column LookUpIds which will execute this query to get user displaynames:

select Id
     , case when DisplayName is null or LEN(DisplayName) = 0 
            then 'unknown' 
            else DisplayName 
       end 
from Users 
where Id in (-1, 1, 2 ,3 ... all 50,000 ids) 

The above query raises the same exception you get when using a magic column in a large resultset.

As a workaround, don't use the magic column but use the 'site://users/' + cast(userid as nvarchar) trick (called AutoLinking) instead to get a link to a user profile. You'll have to accept that you won't get user display name with this approach. If your query allows for it, you can add an extra join with users table so you can concatenate the url magic with display name.