I have a query with calculated fields which involves looking up a dataset within a CTE for each of them, but it's quite slow when I get to a couple of these fields.
Here's an idea:
;WITH TRNCTE AS
(
SELECT TRN.PORT_N, TRN.TRADE_DATE, TRN.TRANS_TYPE, TRN.TRANS_SUB_CODE, TRN.SEC_TYPE, TRN.SETTLE_DATE
FROM TRNS_RPT TRN
WHERE TRN.TRADEDT >= '2014-01-01' AND TRN.TRADEDT <= '2014-12-31'
)
SELECT
C.CLIENT_NAME,
C.PORT_N,
C.PHONE_NUMBER,
CASE
WHEN EXISTS(SELECT TOP 1 1 FROM TRNCTE WHERE PORT_N = C.PORT_N AND MONTH(SETTLE_DATE) = 12) THEN 'DECEMBER TRANSACTION'
ELSE 'NOT DECEMBER TRANSACTION'
END AS ALIAS1
FROM CLIENTS C
WHERE EXISTS(SELECT TOP 1 1 FROM TRNCTE WHERE PORT_N = C.PORT_N)
If I had many of these calculated fields, the query can take up to 10 minutes to execute. Gathering the data in the CTE takes about 15 seconds for around 1,000,000 records.
I don't really need JOINS since I'm not really using the data that a JOIN would do, I only want to check for the existence of records in TRNS_RPT with certains criterias and set alias fields to certain values whether I find such records or not.
Can you help me optimize this ? Thanks
Aucun commentaire:
Enregistrer un commentaire