Try (SQL Server 2005+ code):

;with cnt1 as (select COUNT(*) as rcount1 from childtable1 where pk=@lnPK) ,
cnt2 as (select COUNT(*) as rcount2 from childtable2 where pk=@lnPK),
etc.

select M.PK,
cnt1.rCount1, cnt2.rCount2, etc.
from Maintable M, cnt1, cnt2, etc.
where M.PK = @lnPK

If this will not perform well, start applying ideas from Plamen's artcile.


>What is the point of such strange code? Why not take the count into a variable first and then add it as a field whatever number you need?
>
>In addition, the problem you're facing is called parameter sniffing. Check Plamen Ratchev article
>
>Parameter Sniffing
>
>>I've written a query with several nested joins to check status of records being transferred successfully to archive tables. It looks something like this:
>>

>>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=12345
>>

>>this works fine and runs instantly.
>>
>>If I change it to a variable like:
>>
>>

>>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=@lnPK
>>

>>Performance goes to hell. It takes 45 seconds to complete and the execution plan is completely different.
>>
>>What's going on here? Is there a better way to do this with a variable?