Counting child rows in related table in 'main query'

Question

Post reply

Counting child rows in related table in 'main query'

Rich Caine

SSC Veteran

Points: 269
More actions
June 20, 2008 at 3:03 am

#208604

Hi,
From with in a query on a parent table, I want to get the number (COUNT) of related rows in a child table.
e.g., table of publishers and a table of books, I want to list all publishers with the total number of books each publishes.
At present I simply use a sub SELECT query to COUNT the number of rows in the child table by joiing on the parent tables primary key.
The thing is, the result set from the parent table can be quite large (and therefore the total execute time performing the sub query) so I was wondering if there is a better/more efficient way?
Kind regards

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Hello

Something like this is quite efficient:

SELECT p.Publisher, ISNULL(b.BookCount, 0) AS BookCount

FROM Publisher p

LEFT JOIN (

SELECT Publisher, COUNT(*) AS BookCount

FROM Books

GROUP BY Publisher) b

ON b.Publisher = p.Publisher

ORDER BY p.Publisher

But it's difficult to tell without seeing some or all of your existing query.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Rich Caine SSC Veteran Points: 269 More actions · Answer 2

Thanks Chris, that looks good. I didn't know you could join on a query like that 😛

Do you think your method above is more efficient than say,

SELECT p.Publisher,

(SELECT COUNT(*) FROM Books b WHERE b.Publisher_Ref = p.Ref) AS BookCount

FROM Publishers p

ORDER BY p.Publisher

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Yep Rich it's more efficient. The correlated subquery is evaluated once for every row in the publishers table: it's slow and doesn't scale well. If you have a large enough data set, why not try both for yourself?

The join-to-a-derived-table method is usually much quicker than a correlated subquery, but what if you wanted the count of books per publisher and also say the minimum book price per publisher as well? Easy with the derived table.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 4

This should perform better than your correlated subquery

SELECT p.Publisher, COUNT(b.Publisher_Ref) AS BookCount

FROM Publishers p

LEFT OUTER JOIN Books b ON b.Publisher_Ref = p.Ref

GROUP BY p.Publisher

ORDER BY p.Publisher

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537