How to optimize this SQL statement. I have tried to put code written in cross apply in another function and in select.
x
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
)
Advertisement
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.