Most efficient way of getting record counts from a related table?

Question

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 whereas the sql I typed above correctly shows a count of 0 for those).

Is there a more efficient way of getting this data?

Answer 1

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

Answer 2

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

Answer 3

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,
     ...

Answer 4

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
from #cnt
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.