79713271

Date: 2025-07-24 11:33:06
Score: 0.5
Natty:
Report link

How about get your data first and do the RANK thing on temp table if possible?

Besides, your query is waiting for paralleism also, so i added OPTION (MAXDOP 1) to get rid of it. So your Clustered Index Seek operation also will be done serially (If you check the plan carefully it is also effecting)

Sorting is effecting too much so instead of doing it in a big query, it can be easier to do it in a temp table which your data is already taken there in this query. This can be accelareting the performance of RANK() operator also.

SELECT * INTO #MainData
 FROM
 (
SELECT
                                 REC.INPATIENT_DATA_ID
                               --, RANK() over (Partition by PATS.PAT_ENC_CSN_ID, MEAS.FLO_MEAS_ID order by RECORDED_TIME) 'VITALS_RANK' 
                               , MEAS.RECORDED_TIME
                               , PATS.PAT_ENC_CSN_ID
                               , PATS.PAT_ID
                               , PATS.CONTACT_DATE
                               , MEAS.FLO_MEAS_ID
                               , PATS.DEPARTMENT_ID
                               , PAT.IS_TEST_PAT_YN
                               , PATS.HOSP_DISCH_TIME
                               , PATS.HOSP_ADMSN_TIME
                         FROM CLARITY.DBO.IP_FLWSHT_REC  REC
                         LEFT OUTER JOIN CLARITY.DBO.PAT_ENC_HSP PATS ON PATS.INPATIENT_DATA_ID = REC.INPATIENT_DATA_ID 
                         LEFT OUTER JOIN CLARITY.DBO.CLARITY_DEP AS DEP ON PATS.DEPARTMENT_ID = DEP.DEPARTMENT_ID
                         LEFT OUTER JOIN CLARITY.DBO.PATIENT_3 PAT ON PAT.PAT_ID = PATS.PAT_ID  
                         LEFT OUTER JOIN CLARITY.DBO.IP_FLWSHT_MEAS MEAS ON REC.FSD_ID = MEAS.FSD_ID
)

SELECT INPATIENT_DATA_ID
                               , RANK() over (Partition by PAT_ENC_CSN_ID, FLO_MEAS_ID order by RECORDED_TIME) 'VITALS_RANK' 
                               , RECORDED_TIME
                               , PAT_ENC_CSN_ID
                               , PAT_ID
                               , CONTACT_DATE
                               , FLO_MEAS_ID
                               , DEPARTMENT_ID
                               , S_TEST_PAT_YN
                               , HOSP_DISCH_TIME
                               , HOSP_ADMSN_TIME
                               FROM #MainData
                               OPTION (MAXDOP 1)
                               
DROP TABLE #MainData
Reasons:
  • Long answer (-1):
  • Has code block (-0.5):
  • Contains question mark (0.5):
  • Starts with a question (0.5): How
  • Low reputation (1):
Posted by: Can Yıldırmaz