Query performance WHERE clause contains IN (subquery)

sql sqlperformance
Candy Chiu picture Candy Chiu · May 11, 2011 · Viewed 12.5k times · Source 
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 <= '2011-05-11')
                        GROUP BY PairOffId
                        HAVING      (SUM(Notional) <> 0)))
GROUP BY Trade.TradeId, Trade.Type, Trade.Symbol, Trade.TradeDate
ORDER BY Trade.Type, Trade.TradeDate

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.

Thank you.

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. 

   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 <= '2011-05-11')
                        GROUP BY PairOffId
                   HAVING (SUM(Notional) <> 0)) AS PairOff ON TradeLine.Id = PairOff.PairOffId
   ORDER BY Trade.Type, Trade.TradeDate

Answer

9000 picture 9000 · May 11, 2011

The subquery in the IN clause does not depend on anything in the outer query. You can safely move it into FROM clause; a sane query plan builder would do it automatically.

Also, calling EXPLAIN PLAN on any query you're going to use in production is a must. Do it and see what the DBMS thinks of the plan for this query.

Related questions

Database/SQL: How to store longitude/latitude data?

Performance question ... I have a database of houses that have geolocation data (longitude & latitude). What I want to do is find the best way to store the locational data in my MySQL (v5.0.24a) using InnoDB database-engine so that …

Read More
How to measure performance of query in oracle

I'm new to Oracle db. I have 2 queries which return the same result set. I want to measure the performance of each of them and choose the better one. How do I do that using Oracle SQL developer? I remember …

Read More
How to Bulk Update with SQL Server?

I have a table with 10 millions rows that I need to join with another table and update all data. This is taking more than 1 one hour and it is increasing my transaction log in 10+ GBs. Is there another way to …

Read More