in Below i have given each WITH clause and its records count and time taken. The final with clause is causing issue and it is running more than 6 minutes. Please let me know how to improve the performance of WITH clause.
-- main contains 22k unique member ids and it took 0.15 secs
WITH MAIN AS(
SELECT
MEMBER_ID
FROM MEMBER),
--FTR returns 0.3 Million rows and it took 0.25 desc
FTR AS (
SELECT
MEMBER_ID,
MONTH_NBR,
SUM(TRANS_AMT) AS TRANS_AMT
FROM
MAIN
INNER JOIN PORT_TRANS_AMOUNT
ON MEMBER_ID = PORT_MEMBER_ID
GROUP BY MEMBER_ID, MONTH_NBR),
--MEM_MTH_AMT returns 22K and it took 0.9 secs
MEM_MTH_AMT AS (
SELECT
MEMBER_ID,
SUM(CASE WHEN MONTH_NBR = 0 THEN TRANS_AMT ELSE 0 END) AS M0,
SUM(CASE WHEN MONTH_NBR = 1 THEN TRANS_AMT ELSE 0 END) AS M1,
SUM(CASE WHEN MONTH_NBR = 2 THEN TRANS_AMT ELSE 0 END) AS M2,
SUM(CASE WHEN MONTH_NBR = 3 THEN TRANS_AMT ELSE 0 END) AS M3,
SUM(CASE WHEN MONTH_NBR = 4 THEN TRANS_AMT ELSE 0 END) AS M4
FROM
FTR
GROUP BY MEMBER_ID),
--PROV_MEM returns 0.25 Million and it took 45 secs
PROV_MEM AS (
SELECT
MEMBER_ID,
PROV_MTH_NBR,
SUM(PROV_TRANS_AMT) AS PROV_TRANS_AMT
FROM
MAIN
INNER JOIN PROV_MBR_TRANS_AMT
ON MEMBER_ID = PROV_MBR_ID
GROUP BY MEMBER_ID, PROV_MTH_NBR),
--PROV_MEM_MTH_AMT returns 20K and it took 30 secs
PROV_MEM_MTH_AMT AS(
SELECT
MEMBER_ID,
SUM(CASE WHEN PROV_MTH_NBR = 0 THEN PROV_TRANS_AMT ELSE 0 END) AS P_M0,
SUM(CASE WHEN PROV_MTH_NBR = 1 THEN PROV_TRANS_AMT ELSE 0 END) AS P_M1,
SUM(CASE WHEN PROV_MTH_NBR = 2 THEN PROV_TRANS_AMT ELSE 0 END) AS P_M2,
SUM(CASE WHEN PROV_MTH_NBR = 3 THEN PROV_TRANS_AMT ELSE 0 END) AS P_M3,
SUM(CASE WHEN PROV_MTH_NBR = 4 THEN PROV_TRANS_AMT ELSE 0 END) AS P_M4
FROM
PROV_MEM
GROUP BY MEMBER_ID),
--RATIO returns 22k and it took 412 sec
RATIO AS(
SELECT
MEMBER_ID,
CASE WHEN M0 <>0 THEN COALESCE(P_M0)/CAST(M0 AS FLOAT) AS RATIO_M0,
CASE WHEN M1 <>0 THEN COALESCE(P_M1)/CAST(M1 AS FLOAT) AS RATIO_M0,
CASE WHEN M2 <>0 THEN COALESCE(P_M2)/CAST(M2 AS FLOAT) AS RATIO_M0,
CASE WHEN M3 <>0 THEN COALESCE(P_M3)/CAST(M3 AS FLOAT) AS RATIO_M0,
CASE WHEN M4 <>0 THEN COALESCE(P_M4)/CAST(M4 AS FLOAT) AS RATIO_M0
FROM
MEM_MTH_AMT
LEFT JOIN PROV_MEM_MTH_AMT
ON MEMBER_ID = MEMBER_ID)
SELECT * FROM RATIO;