mercredi 5 août 2015

Calculated fields from queries in CTE are quite slow, how to optimize

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