7

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.

  • "contact Customer Support Services" - lol, does it mean Tim Stone? – Shadow The Princess Wizard Aug 30 '16 at 8:58
  • 1
    @ShadowWizard: Hey, as far as I'm concerned, meta is "customer support"... – einpoklum - reinstate Monica Aug 30 '16 at 9:27
  • 1
    Well, seriously now, that's an error message from SQL Server, this might be some kind of bug in the database itself. – Shadow The Princess Wizard Aug 30 '16 at 9:30
  • For the Stack Overflow database that query returns a lot of rows and creates an extra expensive join with the users table to create the user link for you. Without the magic column the query runs to completion: data.stackexchange.com/stackoverflow/query/530733/… – rene Aug 30 '16 at 11:33
  • 1
    Yeah, @rene is right about the cause, I've been meaning to fix this. I'll see if I can find time this week to finally just take care of it – Tim Stone Aug 30 '16 at 11:46
  • Let me guess @TimStone, it's on your To Do List? – Taryn Aug 30 '16 at 16:12
  • @bluefeet It is, but I'm trying to find ways to make that more actionable before I drown in todo-debt :P – Tim Stone Aug 30 '16 at 16:14
7

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. 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 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.

  • 1
    And site://q/ for postid. – Cœur Mar 24 at 17:24

You must log in to answer this question.

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