Most efficient way of getting record counts from a related table?
I have a stored procedure that has this select statement in it:
select
(select Count(FaqId) from faq_read_stats where faqid = Faq.Id) as ReadCount,
Faq.*
from Faq
where Faq.Show = 1 and Faq.Active = 1
This gives me the results I want but it seems like having the ad-hoc select count in there might not be optimized for SQL Server.
I tried doing an inner join on the faq_read_stats table but this did not yield correct results (it left out faq records that did not have a record in faq_read_stats w开发者_如何学编程hereas the sql I typed above correctly shows a count of 0 for those).
Is there a more efficient way of getting this data?
You'd need to try it out to compare, but an alternative is:
SELECT ISNULL(x.ReadCount, 0) AS ReadCount, f.*
FROM Faq f
LEFT JOIN
(
SELECT faqid, COUNT(faqid) AS ReadCount
FROM faq_read_stats
GROUP BY faqid
) x ON f.id = x.faqid
WHERE f.Show = 1 AND f.Active = 1
Instead of using an INNER JOIN use a LEFT OUTER JOIN:
SELECT
COUNT(*) AS read_count,
FAQ.col_1,
FAQ.col_2,
FAQ.col_3,
...
FROM
FAQ
LEFT OUTER JOIN Faq_Read_Stats FRS ON
FRS.faq_id = FAQ.faq_id
WHERE
FAQ.show = 1 AND
FAQ.active = 1
GROUP BY
FAQ.col_1,
FAQ.col_2,
FAQ.col_3,
...
Would something like this work or is this what you already tried?
SELECT SUM(CASE WHEN (faq_read_stats.FaqId = Faq.Id) THEN 1 ELSE 0 END) AS ReadCount, Faq.*
FROM Faq INNER JOIN faq_read_stats ON faq_read_stats.FaqId = Faq.Id
WHERE Faq.Show = 1 and Faq.Active = 1
James
When tuning queries, I always consider the 'divide to conquer' approach.
I would bet the code below yelds the best results:
select FaqID, count(*) as ReadCount
into #cnt
from faq_read_stats
group by FaqID
go
select
#cnt.ReadCount,
Faq.*
from
Faq
left outer join #cnt
on cnt.FaqID = Faq.FaqID
where Faq.Show = 1 and Faq.Active = 1
go
drop table #cnt
go
This approach makes it more 'human readable' and does not introduces 'false optimal' plans.
精彩评论