how to avoid group by on a lot of columns

Question

I have two large tables

-Master table A: 1.4million rows
-Detail table B: 9 million rows

B has a foreign key to A.

Basically I need all the 25 detail-fields from table A + in this case a count on B.

Now I have the following pseudo query:

Select
       A.field1,
       A.field2,
       ...
       A.field25,
       Count(b.id)
 from
       A left outer join B on B.fkAid = A.id
 Group by 
       A.id,
       A.field1,
       A.field2,
       ...
       A.field25
Order by A.field1  

The Query plans show the grouping by to take a lot of time (no surprise).

Is there a more efficient way to do this kind of Select?

Answer 1

What about using a common table expression (you tagged it SQL Server 2008?)

WITH CountB AS
(
    SELECT A.aId, value= Count(*)
    FROM A left outer join B on B.fkAid = A.id
    GROUP BY A.id
)
Select
   A.field1,
   A.field2,
   ...
   A.field25,
   CountB.value
from
   A left outer join CountB on A.id = CountB.aId
Order by A.field1