SQL Performance - CA PPM - Sub Queries

Sub Queries can be handy

Sub queries are good in some situation but not all situation

How many projects is a resource assigned to

SELECT SRMR.FULL_NAME,(SELECT COUNT(*) FROM PRTEAM TM WHERE TM.PRRESOURCEID=SRMR.ID) TEAM_COUNT FROM SRM_RESOURCES SRMR
ORDER BY SRMR.FULL_NAME


Instead of LEFT JOIN to a large inline view

SELECT SRMR.FULL_NAME, TM.TEAM_COUNT
FROM SRM_RESOURCES SRMR
LEFT JOIN (SELECT TM.PRRESOURCEID, COUNT(*) TEAM_COUNT
FROM PRTEAM TM
GROUP BY TM.PRRESOURCEID) TM ON SRMR.ID=TM.PRRESOURCEID
ORDER BY SRMR.FULL_NAME

Avoid substituting sub-queries for table joins

SELECT SRMR.FULL_NAME,
(SELECT COUNT(*) FROM PRTEAM TM WHERE TM.PRRESOURCEID=SRMR.ID) TEAM_COUNT,
(SELECT SUM(TM.PRALLOCSUM)/3600 FROM PRTEAM  TM WHERE TM.PRRESOURCEID=SRMR.ID) TOTAL_ALLOC,
(SELECT SUM(TM.HARD_SUM) /3600 FROM PRTEAM TM WHERE TM.PRRESOURCID =SRMR.ID) TOTAL_HALLOC
FROM SRM_RESOURCES SRMR
ORDER BY SRMR.FULL_NAME