Hello all (sorry first time posting!),
I have a query using CTEs and some days it runs within 6 mins, other days it can take up to 1 hour and I'm not too sure why. Can anyone provide any advice please? I have spoken to our server team as I thought it may be a data warehouse issue but they are unsure.
Here are my CTEs I'm using in case I am doing something wrong?
;with RecentInvolvement as
(SELECT DIM_LOOKUP_INVOLVEMENT_TYPE_DESC, END_DTTM, DIM_PERSON_ID, START_DTTM, DIM_WORKER_NAME, DIM_WORKER_ID,
ROW_NUMBER() over(partition by DIM_PERSON_ID order by START_DTTM desc) RN1
FROM
(SELECT FACT_INVOLVEMENTS.DIM_LOOKUP_INVOLVEMENT_TYPE_DESC, FACT_INVOLVEMENTS.END_DTTM, DIM_PERSON_ID, START_DTTM, DIM_WORKER_NAME, DIM_WORKER_ID,
ROW_NUMBER() over(partition by DIM_PERSON_ID order by START_DTTM desc) RN1
FROM FACT_INVOLVEMENTS WHERE FACT_INVOLVEMENTS.DIM_LOOKUP_INVOLVEMENT_TYPE_DESC = 'FF' AND END_DTTM IS NULL
) x WHERE RN1 = 1 ),
RecentCaseNote AS
(SELECT DIM_PERSON_ID, EVENT_DTTM, DIM_CREATED_BY_ID_DESC
FROM (SELECT c.DIM_PERSON_ID, c.EVENT_DTTM, c.DIM_CREATED_BY_ID_DESC, ROW_NUMBER() over(partition by c.DIM_PERSON_ID order by EVENT_DTTM desc) RN1
FROM Child_Social.FACT_CASENOTES c LEFT JOIN RecentInvolvement I ON C.DIM_PERSON_ID=I.DIM_PERSON_ID
WHERE C.DIM_CREATED_BY_ID_DESC = I.DIM_WORKER_NAME)
x WHERE RN1 = 1 )
and my joins:
LEFT JOIN RecentInvolvement ON (RecentInvolvement.DIM_PERSON_ID=P.DIM_PERSON_ID)
LEFT JOIN RecentCaseNote ON (RecentCaseNote.DIM_PERSON_ID=RecentInvolvement.DIM_PERSON_ID)
Thanks in advance!
