Dynamically calculated values per row = slow webpage 1

[yesti]

I have a perl page that first opens the following query (field/table names shortened and are consistent):

Select A, B, Count(B) as cnt
From T1 Inner Join T2 ON T1.ID=T2.ID
Where T1.Type='ABCD'
Group By A, B
Order By A

While in this query, for each row, the following query is run:

Select Count(C) as svr
From T1 Inner Join T3 ON T1.ID=T3.ID
Left Outer Join T4 on T3.ID=T4.ID
Where T1.Type='ABCD' and T1.A='$A' ($A being the column value returned for this row)
and T4.D like '%server%'

I format the results in a table like so:

A B svr

'cnt' is used on the perl page to calculate other things.

This runs rather slowly and was wondering how to have it all done on the SQL server.

Can I combine the two statements so that they run at the same time?
If so, how would I have access to the 'current' value of A for the Where clause in the second query?

Select A, B, Count(B) as cnt,
(Select Count(C) as svr
From T1 Inner Join T3 ON T1.ID=T3.ID
Left Outer Join T4 on T3.ID=T4.ID
Where T1.Type='ABCD' and T1.A='CURRENT VALUE FOR A'
and T4.D like '%server%') as svr
From T1 Inner Join T2 ON T1.ID=T2.ID
Where T1.Type='ABCD'
Group By A, B
Order By A

 

[Otto]

Name the outer T1 to something else:

Select A, B, Count(B) as cnt,
    (Select Count(C) as svr
    From T1 Inner Join T3 ON T1.ID=T3.ID
    Left Outer Join T4 on T3.ID=T4.ID
    Where T1.Type='ABCD' and T1.A=[b]T1_O.A[/b]
    and T4.D like '%server%') as svr
From T1 [b]T1_O[/b] Inner Join T2 ON [b]T1_O[/b].ID=T2.ID
Where [b]T1_O[/b].Type='ABCD'
Group By A, B
Order By A

 

[yesti]

That worked great, glad it was such an easy fix. Thanks for the quick reply.

I also added indexes on the columns in the two where clauses. Those two changes resulted in execution time going from 5 min -> 10 sec.