I fixed a slow nested query by replacing an IN statement with a join with a temp table. I believe this trick really works when the inner query is
Before:
Select * From Table1
Where FKID IN (Select ID From Table2 Where...)
Before:
Select * From Table1
Where FKID IN (Select ID From Table2 Where...)
After:
Declare @Temp TABLE(ID int)
Insert Into @Temp
Select ID From Table2 Where...
Declare @Temp TABLE(ID int)
Insert Into @Temp
Select ID From Table2 Where...
Select *
From Table1 Inner Join @Temp tmp
On Table1.FKID = tmp.ID