Why is data.SX going out of resources for my no-Joins query? [duplicate]

Question

I'm trying to run the following query on data.SX.com:

select
    OwnerUserId as [User Link],
    sum(case when up > 0 and down = 0 then 1 else 0 end) as socratism
from (
  select
    OwnerUserId,
    max(case when score > 0 then 1 else 0 end) as up,
    max(case when score < 1 then 1 else 0 end) as down
  from Posts
  where PostTypeId = 1 -- a question
  group by CONVERT(VARCHAR(10), creationDate, 111), OwnerUserId
) question_days
group by question_days.OwnerUserId
order by socratism desc;

This query involves no joining, so the table is not even that large. So what's the problem? What can I do to make the query more palatable?

Answer 1

The magic column [User Link] is extremely expensive or at least that is what SQL Server thinks so it often gives up.

I've made a small change by replacing the magic column with the autolinking feature and this does work:

select
      'site://users/' 
    + cast(OwnerUserId as nvarchar) 
    + '|'
    + (select displayname from users du where du.id = owneruserid)  as [User],
    sum(case when up > 0 and down = 0 then 1 else 0 end) as socratism
from (
  select
    OwnerUserId,
    max(case when score > 0 then 1 else 0 end) as up,
    max(case when score < 1 then 1 else 0 end) as down
  from Posts
  where PostTypeId = 1 -- a question
  group by CONVERT(VARCHAR(10), creationDate, 111), OwnerUserId
) question_days
group by question_days.OwnerUserId
order by socratism desc;

The site:// construct expands to the base URL of the site, in this case http://stackoverflow.com the rest is straight forward SQL string concatenation and inline SQL statements

    'site://users/'  --  'https://stackoverflow.com/users/' (1)
    + cast(OwnerUserId as nvarchar) -- '578411'
    + '|' -- the pipe symbol '|'
    + (select displayname  -- project the display name
       from users du       -- found in the users table, aliased as du     
       where du.id = owneruserid) -- the row where its id equals the owneruserid

end result:

'https://stackoverflow.com/users/578411|rene' and that string is rendered as a proper link, rene, not as plain text.

^{1. Technically the site:// isn't expanded server side but client side. For the understanding of how this works it is an implementation detail not relevant for this discussion}