title: Query performance WHERE clause contains IN (subquery)
body: <pre><code>SELECT Trade.TradeId, Trade.Type, Trade.Symbol, Trade.TradeDate, 
         SUM(TradeLine.Notional) / 1000 AS Expr1
  FROM   Trade INNER JOIN
               TradeLine ON Trade.TradeId = TradeLine.TradeId
  WHERE  (TradeLine.Id IN
                        (SELECT     PairOffId
                          FROM          TradeLine AS TradeLine_1
                          WHERE      (TradeDate &lt;= '2011-05-11')
                          GROUP BY PairOffId
                          HAVING      (SUM(Notional) &lt;&gt; 0)))
  GROUP BY Trade.TradeId, Trade.Type, Trade.Symbol, Trade.TradeDate
  ORDER BY Trade.Type, Trade.TradeDate
  </code></pre>
  
  <p>I am concerned about the performance of the IN in the WHERE clause when the table starts to grow.  Does anyone have a better strategy for this kind of query?  The number of records returned by the subquery grows much slower than the number of records in the TradeLine table.  The TradeLine table itself grows at a rate of 10/day.  </p>
  
  <p>Thank you.</p>
  
  <p>EDIT:
  I used the idea of moving the subquery from WHERE to FROM.  I voted up on all answers that contributed to this new query.  </p>
  
  <pre><code>   SELECT Trade.TradeId, Trade.Type, Trade.Symbol, Trade.TradeDate,   
            PairOff.Notional / 1000 AS Expr1
     FROM         Trade INNER JOIN
                    TradeLine ON Trade.TradeId = TradeLine.TradeId INNER JOIN
                        (SELECT     PairOffId, SUM(Notional) AS Notional
                          FROM          TradeLine AS TradeLine_1
                          WHERE      (TradeDate &lt;= '2011-05-11')
                          GROUP BY PairOffId
                     HAVING (SUM(Notional) &lt;&gt; 0)) AS PairOff ON TradeLine.Id = PairOff.PairOffId
     ORDER BY Trade.Type, Trade.TradeDate
  </code></pre>