Optimise Query Efficency

Transact-SQL
, ,
1

Hi

Does any one have any advise of how I could optimise the below query to run more efficiently?

SELECT *
FROM AATABLE AA

LEFT JOIN (
SELECT *
,ROW_NUMBER() OVER (PARTITION BY OBJECTNUMBER ORDER BY TRUE_DATE DESC) AS ROW_ID
FROM
(SELECT
A.OBJECTNUMBER,
B.TXT04,
(CAST(CONCAT(CONCAT(CAST(A.CREATEDATE AS DATE),' '),CAST(A.CREATEDTIME AS TIME)) AS TIMESTAMP)) as TRUE_DATE
FROM ATABLE A
LEFT JOIN BTBLE B ON A.STATUS = B.STATUS
WHERE A.FILTERA IN ('SOMETHING','SOMETHING2')
AND B.FILTERB <> 'SOMETHING'
)x ) C
ON AA.OBJECTNUMBER = C.OBJECTNUMBER
AND C.ROW_ID = 1

WHERE AA.QMNUM = '111111'

2

Reducing to one subquery

SELECT *
  FROM AATABLE AA
       LEFT OUTER JOIN (SELECT TOP(1) WITH TIES
                               A.OBJECTNUMBER
                              ,B.TXT04
                              ,(CAST(CONCAT(CONCAT(CAST(A.CREATEDATE AS DATE),' '),CAST(A.CREATEDTIME AS TIME)) AS TIMESTAMP)) AS TRUE_DATE
                          FROM ATABLE A
                               INNER JOIN BTBLE AS B
                                       ON B.STATUS=A.STATUS
                                      AND B.FILTERB<>'SOMETHING'
                         WHERE A.FILTERA IN ('SOMETHING','SOMETHING2')
                         ORDER BY ROW_NUMBER() OVER(PARTITION BY OBJECTNUMBER ORDER BY TRUE_DATE DESC)
                       ) AS C
                    ON AA.OBJECTNUMBER = C.OBJECTNUMBER
 WHERE AA.QMNUM = '111111'
;
3

If you don't need all the columns replace * with the columns your using. Using CTEs instead of subqueries may make it faster depending on resources.
For better answers:
Post table DDL and inserts with sample data.

1 Like