Reputation: 1093
So I have some database entities describing projects and users in said projects:
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
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
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