CODEWITHSUNDEEP
sqlsql-serverdatabase
Marnus Steyn
Marnus Steyn

Reputation: 1093

Best way to count in joined table

So I have some database entities describing projects and users in said projects:

enter image description here Then I wrote a query in order to get a collection of projects, as well as an extra field to get the amount of users in the project:

select x.RefProject, x.ProjectName, x.DateCreated, x.PlannedDueDate, 
(select count(RefUser) from ProjectUsers where RefProject = x.RefProject) as ProjectUserCount
from Projects x
left outer join ProjectUsers a on x.RefProject = a.RefProject
where a.RefUser = 1

My question is - Is there a better way to do the user count? I feel like a CTE table or funtion would be the "more correct" way to do this.

Upvotes: 1

Views: 64

Answers (2)

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14097

Alternative would be to write a sub-query that groups by RefProject, does the sum and then joins your table.

SELECT x.RefProject, x.ProjectName, x.DateCreated, x.PlannedDueDate, T.Cnt
FROM Projects AS x
LEFT JOIN (   SELECT RefProject, COUNT(RefUser) AS Cnt
              FROM ProjectUser
              GROUP BY RefProject) AS T
    ON T.RefProject = x.RefProject
LEFT JOIN ProjectUsers AS a
    ON x.RefProject = a.RefProject
WHERE a.RefUser = 1;

Using a scalar function or multi-statement table valued function will draw performance down, both this and your query are fine, just index tables well and this is going to work alright.

Alternatively, if grouping becomes a problem, you can create an indexed view and join with it. This adds some write overhead, but does miracles on read performance.

CREATE VIEW RefProjectCountView
WITH SCHEMABINDING
AS
SELECT RefProject, COUNT_BIG(RefUser) AS Cnt
FROM ProjectUser
GROUP BY RefProject;
GO

CREATE UNIQUE CLUSTERED INDEX PK_RefProjectCountView
ON RefProjectCountView (RefProject);
GO

SELECT x.RefProject, x.ProjectName, x.DateCreated, x.PlannedDueDate, T.Cnt
FROM Projects AS x
LEFT JOIN RefProjectCountView AS T WITH(NOEXPAND)
    ON T.RefProject = x.RefProject
LEFT JOIN ProjectUsers AS a
    ON x.RefProject = a.RefProject
WHERE a.RefUser = 1;

WITH(NOEXPAND) hint is needed for SQL Server to query view index and not to expand the full select statement.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271241

First, your where clause turns the outer join to an inner join. So you might as well be explicit. Then use a window function:

select p.RefProject, p.ProjectName, p.DateCreated, p.PlannedDueDate, 
       count(*) over (partition by p.RefProject) as ProjectUserCount
from Projects p join
     ProjectUsers pu
     on p.RefProject = pu.RefProject
where pu.RefUser = 1;

EDIT:

It occurs to me that the filter might limit the actual count. A more accurate representation uses a subquery:

select p.*
from (select p.RefProject, p.ProjectName, p.DateCreated, p.PlannedDueDate, 
             count(*) over (partition by p.RefProject) as ProjectUserCount,
             pu.RefUser
      from Projects p join
           ProjectUsers pu
           on p.RefProject = pu.RefProject
     ) p
where RefUser = 1;

Upvotes: 2

Related Questions