Friday, February 24, 2012

SQL server performance tuning - Avoid In statement

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...)



After:
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