SQL Server – How to Optimize a Query Using Sub-Query in WHERE Clause

sql server

I have been trying to find a way to optimize this query by probably removing the subquery in where clause.

it currently takes 2 minutes to run this query with the where clause and 4 seconds without the where clause.

SELECT 
    rateString,
    currency
  FROM myTable.trades ct
  WHERE ct.id IN
      (
        SELECT top 1 id
        FROM myTable.trades tt
        WHERE tt.currency = ct.currency
        AND tt.isExecuted = 1
        AND tt.status LIKE 'executed'
        AND tt.transactionType LIKE 'sell'
        ORDER BY tt.created_at DESC
      )
ORDER BY currency, created_at DESC

The subquery will limit the result to the top 1 row per
currency with some more where and order clause.

Everything is from a single table and no foreign key is involved

Best Answer

You can try the following:

WITH CTE_Trades 
AS (
    SELECT  ct.rateString
    ,       ct.currency 
    ,       RowNum  = ROW_NUMBER() OVER (PARTITION BY ct.currency ORDER BY ct.created_at DESC) -- try with ct.id too
    FROM    myTable.trades ct
    WHERE   ct.isExecuted = 1
    AND     ct.status = 'executed'
    AND     ct.transactionType = 'sell'
)

SELECT rateString, currency FROM CTE_Trades WHERE RowNum = 1
Related Question