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.

  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

  • 1
    Welcome! We'd love to help, but there are few things that will help us answer your question. Please check out this link – Forrest May 14 '18 at 12:56
  • Everything is from a single table? so cryptox.trades and myTable.trades are the same? – Evandro Muchinski May 14 '18 at 13:02
  • @EvandroMuchinski yes, typing mistake over here i guess, but everything is from a single table. let me fix that – Rohit Hazra May 14 '18 at 13:08
  • @Forrest thanks, I am seeing a lot of suggestions in the link you provided, i am going through then as well thanks – Rohit Hazra May 14 '18 at 13:11
  • Is id the primary key of trades? – ypercubeᵀᴹ May 14 '18 at 13:13

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
  • that seems to work like a charm, i am guessing what it does is create a temp table with row number and selects the row 1. ? – Rohit Hazra May 14 '18 at 13:14
  • 1
    I would add an index on (isExecuted, status, transactionType, currency, created_at) INCLUDE (rateString) as well. – ypercubeᵀᴹ May 14 '18 at 13:16
  • 1
    Not a temp table. You can use Common Table Expressions (CTE) to solve a lot of problems in SQL Server, like avoiding 'copy and paste' complex calculations or even recursive queries. Please, have a look on docs.microsoft.com/en-us/sql/t-sql/queries/… – Evandro Muchinski May 14 '18 at 13:17
  • 1
    Or an index on (currency, created_at) INCLUDE (rateString) WHERE (isExecuted = 1 AND status = executed' AND transactionType = 'sell') if you are always querying with the same parameters. – ypercubeᵀᴹ May 14 '18 at 13:19
  • I think this could be optimized further using a WHERE EXISTS clause. The logic states that it really only cares if there is a 'TOP 1' record. Since it doesn't actually return anything from the sub-query, a where exists could be used instead and could potentially run quicker. In that way, you wouldn't even need the ORDER BY(from the OP) in the sub-select or in your answer, the ROW_NUMBER() with the ORDER BY. I haven't tested but it should return exactly the same result without the need to enumerate the table. – Shooter McGavin May 15 '18 at 22:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.