Acquiring the number of contracts for persons

Question

This script shows the number of contracts for persons. Table Contract has more than 82008 records and Candidate has about 7978.

Could you please suggest improvements for this script?

select ISNULL(CON.ContractCount, 0)
,      CAN.PersoonName
,      ... /* some fields from table Candidate (more than 25) */
from   Candidate CAN
       left join (select PersoonID, COUNT(*) ContractCount 
                  from Contract
                  group by PersoonID) CON on CAN.PersoonID = CON .PersoonID
where CAN.Type = 11

Answer 1

Sometimes, when doing left-join aggregation with a simple function (count()), it is easier to move the aggregation to be part of the select clause instead of as a subquery on the from clause.

As long as you are only pulling one aggregate from the sub table, it's easy.

Also, I presume there is at least one index on Contract where the PersoonID is the first (or only) column.

select (select count (*)
        from Contract
        where PersoonID = CAN.PersoonID
       ) as ContractCount
,      CAN.PersoonName
,      ... /* some fields from table Candidate (more than 25) */
from   Candidate CAN
where CAN.Type = 11