2

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?

2
  • the [User Link] is probably killing it
    – rene
    Mar 2, 2017 at 13:57
  • Because of the [User Link], which crash when there are too many records. Mar 2, 2017 at 13:57

1 Answer 1

5

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

7

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