Optimize SQL with sub queries to?

Question

I have following two tables in the bottom of the post. The siteUsers table have a type column that can be "Member" or "Owner". I am looking for a SQL that shows how many members and owners each site has. I could do something like:

select 
    sites.Url,
    (select count(*) from siteusers where type='member' and siteusers.siteid=sites.siteid) as members,
    (select count(*) from siteusers where type='owners' and siteusers.siteid=sites.siteid) as owners
    from sites
    group by sites.url

However this is extreemly slow. Is there a faster and smarter way?

Sites table:

SiteId  int Unchecked
SPOSiteId   uniqueidentifier    Checked
Url nvarchar(MAX)   Checked

SiteUsers Table:

SiteUserId  int Unchecked
Type    nvarchar(256)   Checked
Name    nvarchar(512)   Checked
Email   nvarchar(512)   Checked
Host    nvarchar(256)   Checked
SiteId  int Checked

Gordon Linoff · Accepted Answer · 2019-03-15 19:34:28Z

2

One way is:

select s.Url,
       sum(case when su.type = 'member' then 1 else 0 end) as members,
       sum(case when su.type = 'owners' then 1 else 0 end) as owners,
from sites s left join
     siteusers su
     on s.siteid = su.siteid
where su.type in ('member', 'owners')
group by s.url;

edited Mar 15, 2019 at 19:34

answered Mar 15, 2019 at 19:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2019-03-15 19:36:03Z

1

You can do conditional aggregation with JOIN :

SELECT s.Url,
       SUM(CASE WHEN su.type ='member' THEN 1 ELSE 0 END) as members,
       SUM(CASE WHEN su.type ='owners' THEN 1 ELSE 0 END) as owners
FROM sites s INNER JOIN
     siteuser su
     ON su.siteid = s.siteid
GROUP BY s.Url;

However, you want index on sites(Url), siteuser(type) for better performance.

If the url is unique in sites, then this should be fast with the right indexes:

select s.Url,
    (select count(*) from siteusers su where su.type = 'member' and su.siteid = s.siteid) as members,
    (select count(*) from siteusers su where su.type = 'owners' and su.siteid = s.siteid) as owners
from sites s;

The right index is on siteusers(siteid, type). Of course, this assumes that url is unique which may not be the case.

edited Mar 15, 2019 at 19:36

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

answered Mar 15, 2019 at 19:03

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

1 Comment

Thomas Segato Over a year ago

You totally right I dont know why I have group by when all is unique!

Abdul Azeez · Accepted Answer · 2019-03-15 19:10:29Z

1

Use Group By

 select s.Url,su.type, count(*)

 from sites s join
 siteusers su
 on s.siteid = su.siteid

 group by s.url,su.type

answered Mar 15, 2019 at 19:10

Abdul Azeez

91512 silver badges19 bronze badges

1 Comment

Thomas Segato Over a year ago

OOhh guyyss som many good answers! Damn I can only mark one answers :( But thanks all highly appreciated!

Iłya Bursov · Accepted Answer · 2019-03-15 19:15:13Z

1

I'd first check that proper primary keys and indexes are built:

table Sites should have primary key SiteId
table SiteUsers should have primary key SiteUserId
table SiteUsers should have index on SiteId, type

then you can run following query to get results:

select s.url, t.members, t.owners
from Sites s inner join
(
  select
    SiteId, 
    sum(case when Type = 'member' then 1 else 0 end) as members,
    sum(case when Type = 'owners' then 1 else 0 end) as owners
  from SiteUsers
  group by SiteId
) t on s.SiteId = t.SiteId

this way you avoid group by varchar column

sample fiddle http://sqlfiddle.com/#!18/0b9b7/4

answered Mar 15, 2019 at 19:15

Iłya Bursov

24.4k4 gold badges36 silver badges68 bronze badges

1 Comment

Thomas Segato Over a year ago

That runs very fast! The index and PK was allready in place. Sweet!

Collectives™ on Stack Overflow

Optimize SQL with sub queries to?

4 Answers 4

Comments

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related