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 .PersoonIDwhere CAN.Type = 11
Acquiring the number of contracts for persons
performance;sql;sql server
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 CANwhere CAN.Type = 11