how to Optimize SQL query cross apply

How to optimize this SQL statement. I have tried to put code written in cross apply in another function and in select.

SELECT i1.hprop AS investorid, i2.hprop AS investmentid, RATE.dRate
FROM intprop i1
INNER JOIN property p1
    ON p1.hmy = i1.hprop
INNER JOIN [owner] o1
    ON o1.hmyperson = p1.hlegalentity
CROSS JOIN intprop i2
INNER JOIN CUREXCHRATETYPE c
    ON c.hmy = o1.hexchangeratetype
CROSS APPLY (
    SELECT dRate
    FROM (
        SELECT dRate, dtdate, max(DTDATE) OVER () mxdate
        FROM intExchangeRate ix
        WHERE ix.HCXRTIDX = o1.hexchangeratetype
            AND ix.HFROMCUR = i2.hcurrency
            AND ix.HTOCUR = i1.hcurrency
            AND dtdate <= convert(DATETIME, N'10/31/2019', 101)
        ) A
    WHERE dtdate = mxdate
    ) RATE
WHERE i1.hprop IN (11648, 11659, 11662, 11668, 11815, 11816, 11817, 11819, 11821, 11823, 11824, 11826, 11828, 11833, 11834, 11835, 11837, 11840, 11842, 11844, 11846, 11848, 11850, 11852, 11854, 11856, 11858, 11860, 11862, 11864, 11866, 11928, 12016, 12026)
    AND i2.hprop IN (
        11649, 11650, 11651, 11652, 11653, 11654, 11655, 11656, 11657, 11692, 11693, 11708, 11709, 11710, 11711, 11712, 11713, 11714, 11715, 11716, 11717, 11718, 11739, 11876, 11877, 11878, 11887, 11660, 11719, 11720, 11721, 11742, 11880, 11881, 11892, 11893, 11663, 11664, 11665, 11666
        )

Answer

As written, I would recommend an index on:

intExchangeRate(HCXRTIDX, HFROMCUR, HTOCUR, dtdate, drate)

That said, it might be possible to rewrite the query so it is more efficient. However, this question does not provide enough information to make suggestions.

Advertisement

Answer