1
\$\begingroup\$

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
\$\endgroup\$
  • 1
    \$\begingroup\$ Hi, can you clarify what's in the "..." ? If it's not too long, please include it anyway. \$\endgroup\$ – Kaz Sep 9 '15 at 10:34
  • \$\begingroup\$ "..." - there have to be more than 25 fields to select \$\endgroup\$ – Roman Marusyk Sep 9 '15 at 10:37
1
\$\begingroup\$

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
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.