Calculated fields from queries in CTE are pretty slow, how to optimize
I have a query with computed fields that involves looking up a dataset in the CTE for each one, but it is pretty slow when I hit a couple of those fields.
Here's the 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 request could take up to 10 minutes. Collecting data in CTE takes about 15 seconds for about 1,000,000 records.
I don't really need JOINS, since I am not using the data that the JOIN will do, I only want to check for records in TRNS_RPT with certains criteria and set alias fields for certain values, whether I find such records or not.
Can you help me optimize this? Thanks to
source to share
There is a trick you can do in such cases. You need to insert the cte result into the temp table. This will save you the cost of conversion. Try it out.
;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 * INTO #temp
FROM TRNCTE
SELECT
C.CLIENT_NAME,
C.PORT_N,
C.PHONE_NUMBER,
CASE
WHEN EXISTS(SELECT TOP 1 1 FROM #temp 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 #temp WHERE PORT_N = C.PORT_N)
DROP TABLE #temp
source to share
From looking at your code, I would most likely do join
to avoid having to include TRNCTE
twice in the request. It is not exactly the same unless TRNCTE.PORT_N
unique and you end up with duplicate lines.
;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 MONTH(TRNCTE.SETTLE_DATE) = 12)
THEN 'DECEMBER TRANSACTION'
ELSE 'NOT DECEMBER TRANSACTION'
END AS ALIAS1
FROM CLIENTS C
JOIN TRNCTE ON C.PORT_N = TRNCTE.PORT_N
source to share