SELECT maintable.pk ,a.rcount ,b.rcount ,c.rcount ,d.rcount ,e.rcount ,f.rcount from maintable left join (select COUNT(*) as rcount from childtable1 where pk=12345) as a on 1=1 left join (select COUNT(*) as rcount from childtable2 where pk=12345) as b on 1=1 left join (select COUNT(*) as rcount from childtable3 where pk=12345) as c on 1=1 left join (select COUNT(*) as rcount from childtable4 where pk=12345) as d on 1=1 left join (select COUNT(*) as rcount from childtable5 where pk=12345) as e on 1=1 left join (select COUNT(*) as rcount from childtable6 where pk=12345) as f on 1=1 where maintable.pk=12345this works fine and runs instantly.
DECLARE @lnPK as int=12345 SELECT maintable.pk ,a.rcount ,b.rcount ,c.rcount ,d.rcount ,e.rcount ,f.rcount from maintable left join (select COUNT(*) as rcount from childtable1 where pk=@lnPK) as a on 1=1 left join (select COUNT(*) as rcount from childtable2 where pk=@lnPK) as b on 1=1 left join (select COUNT(*) as rcount from childtable3 where pk=@lnPK) as c on 1=1 left join (select COUNT(*) as rcount from childtable4 where pk=@lnPK) as d on 1=1 left join (select COUNT(*) as rcount from childtable5 where pk=@lnPK) as e on 1=1 left join (select COUNT(*) as rcount from childtable6 where pk=@lnPK) as f on 1=1 where maintable.pk=@lnPKPerformance goes to hell. It takes 45 seconds to complete and the execution plan is completely different.