Hi,
I have a slow running query, and trying to pinpoint the issue and not familiar with these stats and what they mean (beginner). If you were to see this where would you concentrate your efforts in trying to resolve and why.
SELECT
NULL AS 'ACTUAL_DELIVERY_PLACE',
NULL AS [ACTUAL_DELIVERY_PLACE_DESCRIPTION],
NULL AS [ANAESTHETIC_GIVEN_DURING_LABOUR_OR_DELIVERY],
NULL AS [ANAESTHETIC_GIVEN_DURING_LABOUR_OR_DELIVERY_DESCRIPTION],
NULL AS [ANAESTHETIC_GIVEN_POST_LABOUR_OR_DELIVERY],
NULL AS [ANAESTHETIC_GIVEN_POST_LABOUR_OR_DELIVERY_DESCRIPTION],
TRY_CAST([BABY_DETAILS].[Birth_Order] AS INT) AS BIRTH_ORDER,
TRY_CAST(REPLACE([BABY_DETAILS].[Birth_weight_in_grams], 'grams', '') AS INT) AS BIRTH_WEIGHT,
[dbo].[udf_TR_PROTOS_JulianDateToSQLDateTime]([DELIVERY_DELIVERY].[Date_of_delivery]) AS [DELIVERY_DATE],
NULL AS [DELIVERY_METHOD],
NULL AS [DELIVERY_METHOD_DESCRIPTION],
NULL AS [DELIVERY_PLACE_CHANGE_REASON],
NULL AS [DELIVERY_PLACE_CHANGE_REASON_DESCRIPTION],
NULL AS [DELIVERY_PLACE_LOCATION_TYPE],
NULL AS [DELIVERY_PLACE_LOCATION_TYPE_DESCRIPTION],
[MATERNITY_EPISODES_BABY].[EPISODE_NUMBER] AS [EPISODE_NUMBER_BABY],
[MATERNITY_EPISODES_MOTHER].[EPISODE_NUMBER] AS [EPISODE_NUMBER_MOTHER],
TRY_CAST(REPLACE([BIRTH].[Estimate_of_gestation], 'weeks', '') AS INT) AS [GESTATION_LENGTH],
[MATERNITY_EPISODES_BABY].[HOSPITAL_PROVIDER_SPELL_NUMBER] AS [HOSPITAL_PROVIDER_SPELL_NUMBER_BABY],
NULL AS [HOSPITAL_PROVIDER_SPELL_NUMBER_BABY_P14N],
[MATERNITY_EPISODES_MOTHER].[HOSPITAL_PROVIDER_SPELL_NUMBER] AS [HOSPITAL_PROVIDER_SPELL_NUMBER_MOTHER],
NULL AS [HOSPITAL_PROVIDER_SPELL_NUMBER_MOTHER_P14N],
NULL AS [LABOUR_ONSET_FETUS_PRESENTATION],
NULL AS [LABOUR_ONSET_FETUS_PRESENTATION_DESCRIPTION],
NULL AS [LABOUR_OR_DELIVERY_ONSET_METHOD],
NULL AS [LABOUR_OR_DELIVERY_ONSET_METHOD_DESCRIPTION],
NULL AS [LIVE_OR_STILL_BIRTH],
NULL AS [LIVE_OR_STILL_BIRTH_DESCRIPTION],
[ACTUAL_DELIVERY_PLACE].[DATA_WAREHOUSE_CODE_VALUE] AS [LOCAL_ACTUAL_DELIVERY_PLACE],
NULL AS [LOCAL_ACTUAL_DELIVERY_PLACE_DESCRIPTION],
[ANAESTHETIC_OR_ANALGESIC].[DATA_WAREHOUSE_CODE_VALUE] AS [LOCAL_ANAESTHETIC_GIVEN_DURING_LABOUR_OR_DELIVERY],
NULL AS [LOCAL_ANAESTHETIC_GIVEN_DURING_LABOUR_OR_DELIVERY_DESCRIPTION],
[ANAESTHETIC_OR_ANALGESIC_POST].[DATA_WAREHOUSE_CODE_VALUE] AS [LOCAL_ANAESTHETIC_GIVEN_POST_LABOUR_OR_DELIVERY],
NULL AS [LOCAL_ANAESTHETIC_GIVEN_POST_LABOUR_OR_DELIVERY_DESCRIPTION],
[DELIVERY_METHOD].[DATA_WAREHOUSE_CODE_VALUE] AS [LOCAL_DELIVERY_METHOD],
NULL AS [LOCAL_DELIVERY_METHOD_DESCRIPTION],
[CHANGE_REASON].[DATA_WAREHOUSE_CODE_VALUE] AS [LOCAL_DELIVERY_PLACE_CHANGE_REASON],
NULL AS [LOCAL_DELIVERY_PLACE_CHANGE_REASON_DESCRIPTION],
[ACTUAL DELIVERY LOCATION TYPE].DATA_WAREHOUSE_CODE_VALUE AS [LOCAL_DELIVERY_PLACE_LOCATION_TYPE],
NULL AS [LOCAL_DELIVERY_PLACE_LOCATION_TYPE_DESCRIPTION],
[PRESENTATION_OF_FETUS].[DATA_WAREHOUSE_CODE_VALUE] AS [LOCAL_LABOUR_ONSET_FETUS_PRESENTATION],
NULL AS [LOCAL_LABOUR_ONSET_FETUS_PRESENTATION_DESCRIPTION],
[DELIVERY_ONSET_METHOD].[DATA_WAREHOUSE_CODE_VALUE] AS [LOCAL_LABOUR_OR_DELIVERY_ONSET_METHOD],
NULL AS [LOCAL_LABOUR_OR_DELIVERY_ONSET_METHOD_DESCRIPTION],
[LIVE_OR_STILL_BIRTH].[DATA_WAREHOUSE_CODE_VALUE] AS [LOCAL_LIVE_OR_STILL_BIRTH],
NULL AS [LOCAL_LIVE_OR_STILL_BIRTH_DESCRIPTION],
[ACTUAL_DELIVERY_LOCATION_CLASS].[DATA_WAREHOUSE_CODE_VALUE] AS [LOCAL_LOCATION_CLASS],
NULL AS [LOCAL_LOCATION_CLASS_DESCRIPTION],
[PATIENTBABY].[OID] AS [LOCAL_PATIENT_IDENTIFIER_BABY],
NULL AS [LOCAL_PATIENT_IDENTIFIER_BABY_P14N],
[PROTOSMODEL_Pregnancy_Record].[LOCAL_PATIENT_IDENTIFIER] AS [LOCAL_PATIENT_IDENTIFIER_MOTHER],
NULL AS [LOCAL_PATIENT_IDENTIFIER_MOTHER_P14N],
[STATUS_OF_PERSON_CONDUCTING_DELIVERY].[DATA_WAREHOUSE_CODE_VALUE] AS [LOCAL_STATUS_OF_PERSON_CONDUCTING_DELIVERY],
NULL AS [LOCAL_STATUS_OF_PERSON_CONDUCTING_DELIVERY_DESCRIPTION],
NULL AS [LOCATION_CLASS],
NULL AS [LOCATION_CLASS_DESCRIPTION],
NULL AS [MODIFIED_DATE],
CASE
WHEN [RESUS_DRUGS].[DATA_WAREHOUSE_CODE_VALUE] IS NOT NULL AND [RESUS_DRUGS].[DATA_WAREHOUSE_CODE_VALUE] <> 'MAT_MATNONE' THEN 'MAT_DRUGS'
ELSE 'MAT_NOTAPPLICABLE'
END + ISNULL([RESUS_PRESSURE].[DATA_WAREHOUSE_CODE_VALUE],'MAT_NOTAPPLICABLE') AS [LOCAL_RESUSCITATION_METHOD],
NULL AS [LOCAL_RESUSCITATION_METHOD_DESCRIPTION],
NULL AS [RESUSCITATION_METHOD],
NULL AS [RESUSCITATION_METHOD_DESCRIPTION],
[RESUS_DRUGS].[DATA_WAREHOUSE_CODE_VALUE] AS [LOCAL_RESUSCITATION_METHOD_DRUGS],
NULL AS [LOCAL_RESUSCITATION_METHOD_DRUGS_DESCRIPTION],
NULL AS [RESUSCITATION_METHOD_DRUGS],
NULL AS [RESUSCITATION_METHOD_DRUGS_DESCRIPTION],
[RESUS_PRESSURE].[DATA_WAREHOUSE_CODE_VALUE] AS [LOCAL_RESUSCITATION_METHOD_POSITIVE_PRESSURE],
NULL AS [LOCAL_RESUSCITATION_METHOD_POSITIVE_PRESSURE_DESCRIPTION],
NULL AS [RESUSCITATION_METHOD_POSITIVE_PRESSURE],
NULL AS [RESUSCITATION_METHOD_POSITIVE_PRESSURE_DESCRIPTION],
'PAS' AS [SOURCE_SYSTEM],
NULL AS [START_DATE_EPISODE_MOTHER],
NULL AS [STATUS_OF_PERSON_CONDUCTING_DELIVERY],
NULL AS [STATUS_OF_PERSON_CONDUCTING_DELIVERY_DESCRIPTION]
FROM
[PROTOSMODEL_Pregnancy_Record] [PROTOSMODEL_Pregnancy_Record]
INNER JOIN [PROTOSMODEL_Pregnancy_Record_Delivery] [PREGNANCY_RECORD_DELIVERY]
ON [PROTOSMODEL_Pregnancy_Record].[ID] = [PREGNANCY_RECORD_DELIVERY].[Pregnancy_RecordID]
INNER JOIN [PROTOSMODEL_Delivery_Delivery] [DELIVERY_DELIVERY]
ON [PREGNANCY_RECORD_DELIVERY].[ID] = [DELIVERY_DELIVERY].[Pregnancy_Record_DeliveryID]
LEFT JOIN [PROTOSMODEL_Antenatal_Status] [ANTENATAL_STATUS]
ON [PROTOSMODEL_Pregnancy_Record].[ID] = [ANTENATAL_STATUS].[Pregnancy_RecordID]
INNER JOIN [PROTOSMODEL_Baby] [BABY]
ON [PREGNANCY_RECORD_DELIVERY].[ID] = [BABY].[Pregnancy_Record_DeliveryID]
INNER JOIN [PROTOSMODEL_Baby_Details] [BABY_DETAILS]
ON [BABY].[ID] = [BABY_DETAILS].[BabyID]
LEFT JOIN [LZO_PATIENT] [PATIENTBABY]
ON RTRIM(LTRIM([BABY_DETAILS].[Babys_Hospital_number])) = [PATIENTBABY].[PASID]
AND 'A' = [PATIENTBABY].[STATUS]
INNER JOIN [PROTOSMODEL_Birth] [BIRTH]
ON [BABY].[ID] = [BIRTH].[BabyID]
LEFT JOIN [PROTOSMODEL_Pregnancy_Record_Obstetric_Summary] [PREGNANCY_RECORD_OBSTETRIC_SUMMARY]
ON [PROTOSMODEL_Pregnancy_Record].[ID] = [PREGNANCY_RECORD_OBSTETRIC_SUMMARY].[Pregnancy_RecordID]
LEFT JOIN [PROTOSMODEL_Resuscitation] [RESUSCITATION]
ON BABY.ID = RESUSCITATION.BabyID
LEFT JOIN [TR_HEY_LOCAL_CODE_MAP] [ACTUAL_DELIVERY_PLACE]
ON [BIRTH].[Korner_Actual_Place_of_Birth] = [ACTUAL_DELIVERY_PLACE].[SOURCE_SYSTEM_CODE_VALUE]
AND 'ACTUAL DELIVERY PLACE' = [ACTUAL_DELIVERY_PLACE].[CODE_DOMAIN]
LEFT JOIN [TR_HEY_LOCAL_CODE_MAP] [ACTUAL_DELIVERY_LOCATION_CLASS]
ON [BIRTH].[Korner_Actual_Place_of_Birth] = [ACTUAL_DELIVERY_LOCATION_CLASS].[SOURCE_SYSTEM_CODE_VALUE]
AND 'LOCATION CLASS' = [ACTUAL_DELIVERY_LOCATION_CLASS].[CODE_DOMAIN]
LEFT JOIN [TR_HEY_LOCAL_CODE_MAP] [ACTUAL DELIVERY LOCATION TYPE]
ON [BIRTH].[Korner_Actual_Place_of_Birth] = [ACTUAL DELIVERY LOCATION TYPE].[SOURCE_SYSTEM_CODE_VALUE]
AND 'LOCATION TYPE CODE' = [ACTUAL DELIVERY LOCATION TYPE].[CODE_DOMAIN]
LEFT JOIN [TR_HEY_LOCAL_CODE_MAP] [ANAESTHETIC_OR_ANALGESIC]
ON [BIRTH].[Korner_Analgesia_Anaesthesia] = [ANAESTHETIC_OR_ANALGESIC].[SOURCE_SYSTEM_CODE_VALUE]
AND 'ANAESTHETIC OR ANALGESIC CATEGORY' = [ANAESTHETIC_OR_ANALGESIC].[CODE_DOMAIN]
LEFT JOIN [TR_HEY_LOCAL_CODE_MAP] [ANAESTHETIC_OR_ANALGESIC_POST]
ON [BIRTH].[Korner_Post_Delivery_Analgesia] = [ANAESTHETIC_OR_ANALGESIC_POST].[SOURCE_SYSTEM_CODE_VALUE]
AND 'ANAESTHETIC OR ANALGESIC CATEGORY' = [ANAESTHETIC_OR_ANALGESIC_POST].[CODE_DOMAIN]
LEFT JOIN [TR_HEY_LOCAL_CODE_MAP] [CHANGE_REASON]
ON [BIRTH].[Korner_Reason_for_Change] = [CHANGE_REASON].[SOURCE_SYSTEM_CODE_VALUE]
AND 'DELIVERY PLACE CHANGE REASON' = [CHANGE_REASON].[CODE_DOMAIN]
LEFT JOIN [TR_HEY_LOCAL_CODE_MAP] [INTENDED_DELIVERY_LOCATION_CLASS]
ON [BIRTH].[Korner_Intended_Place_of_Birth] = [INTENDED_DELIVERY_LOCATION_CLASS].[SOURCE_SYSTEM_CODE_VALUE]
AND 'INTENDED DELIVERY LOCATION CLASS' = [INTENDED_DELIVERY_LOCATION_CLASS].[CODE_DOMAIN]
LEFT JOIN [TR_HEY_LOCAL_CODE_MAP] [PRESENTATION_OF_FETUS]
ON [BIRTH].[Presentation_prior_to_Labour_Caesarean] = [PRESENTATION_OF_FETUS].[SOURCE_SYSTEM_CODE_VALUE]
AND 'PRESENTATION OF FETUS' = [PRESENTATION_OF_FETUS].[CODE_DOMAIN]
LEFT JOIN [TR_HEY_LOCAL_CODE_MAP] [DELIVERY_ONSET_METHOD]
ON [BIRTH].[Korner_Method_of_Labour_Onset] = [DELIVERY_ONSET_METHOD].[SOURCE_SYSTEM_CODE_VALUE]
AND 'LABOUR OR DELIVERY ONSET METHOD' = [DELIVERY_ONSET_METHOD].[CODE_DOMAIN]
LEFT JOIN [TR_HEY_LOCAL_CODE_MAP] [STATUS_OF_PERSON_CONDUCTING_DELIVERY]
ON [BIRTH].[Type_of_person_delivering] = [STATUS_OF_PERSON_CONDUCTING_DELIVERY].[SOURCE_SYSTEM_CODE_VALUE]
AND 'STATUS OF PERSON CONDUCTING DELIVERY' = [STATUS_OF_PERSON_CONDUCTING_DELIVERY].[CODE_DOMAIN]
LEFT JOIN [TR_HEY_LOCAL_CODE_MAP] [DELIVERY_METHOD]
ON [BIRTH].[Korner_Method_of_delivery] = [DELIVERY_METHOD].[SOURCE_SYSTEM_CODE_VALUE]
AND 'DELIVERY METHOD' = [DELIVERY_METHOD].[CODE_DOMAIN]
LEFT JOIN [TR_HEY_LOCAL_CODE_MAP] [LIVE_OR_STILL_BIRTH]
ON [BIRTH].[Outcome_of_birth] = [LIVE_OR_STILL_BIRTH].[SOURCE_SYSTEM_CODE_VALUE]
AND 'LIVE OR STILL BIRTH' = [LIVE_OR_STILL_BIRTH].[CODE_DOMAIN]
LEFT JOIN [TR_HEY_LOCAL_CODE_MAP] [RESUS_DRUGS]
ON [RESUSCITATION].[Drugs_given_for_Resuscitation] = [RESUS_DRUGS].[SOURCE_SYSTEM_CODE_VALUE]
AND 'RESUSCITATION METHOD DRUGS' = [RESUS_DRUGS].[CODE_DOMAIN]
LEFT JOIN [TR_HEY_LOCAL_CODE_MAP] [RESUS_PRESSURE]
ON [RESUSCITATION].[Method_of_Resuscitation_1] = [RESUS_PRESSURE].[SOURCE_SYSTEM_CODE_VALUE]
AND 'RESUSCITATION METHOD POSITIVE PRESSURE' = [RESUS_PRESSURE].[CODE_DOMAIN]
LEFT JOIN [TMP_MATERNITY_PREGNANCY_LIST] [PREGNANCY_LIST]
ON [PROTOSMODEL_Pregnancy_Record].[LOCAL_PATIENT_IDENTIFIER] = [PREGNANCY_LIST].[LOCAL_PATIENT_IDENTIFIER]
AND [PROTOSMODEL_Pregnancy_Record].[REFERRAL_REFERENCE_NUMBER] = [PREGNANCY_LIST].[REFERRAL_REFERENCE_NUMBER]
LEFT JOIN [tblRefDWParameter] [GO_LIVE]
ON 'Lorenzo Maternity Go-Live Date' = [GO_LIVE].[Parameter]
LEFT JOIN [tblRefDWParameter] [PROTOS_START]
ON 'Lorenzo Maternity Earliest PROTOS Date' = [PROTOS_START].[Parameter]
LEFT JOIN WRK_MPI_MERGED_RECORDS MUM_MERGED
ON [PROTOSMODEL_Pregnancy_Record].[LOCAL_PATIENT_IDENTIFIER] = MUM_MERGED.OLD_LOCAL_PATIENT_IDENTIFIER
LEFT JOIN WRK_CDI_LOCAL_PATIENT_IDENTIFIER_TO_PROCESS MUM_LPI_CHECK
ON ISNULL(MUM_MERGED.NEW_LOCAL_PATIENT_IDENTIFIER, [PROTOSMODEL_Pregnancy_Record].[LOCAL_PATIENT_IDENTIFIER]) = MUM_LPI_CHECK.LOCAL_PATIENT_IDENTIFIER
LEFT JOIN WRK_MPI_MERGED_RECORDS BABY_MERGE
ON [PATIENTBABY].[OID] = BABY_MERGE.OLD_LOCAL_PATIENT_IDENTIFIER
LEFT JOIN WRK_CDI_LOCAL_PATIENT_IDENTIFIER_TO_PROCESS BABY_LPI_CHECK
ON ISNULL(BABY_MERGE.NEW_LOCAL_PATIENT_IDENTIFIER, [PATIENTBABY].[OID]) = BABY_LPI_CHECK.LOCAL_PATIENT_IDENTIFIER
INNER JOIN [TMP_MATERNITY_EPISODES] [MATERNITY_EPISODES_MOTHER]
ON ISNULL(MUM_MERGED.NEW_LOCAL_PATIENT_IDENTIFIER, [PROTOSMODEL_Pregnancy_Record].[LOCAL_PATIENT_IDENTIFIER]) = [MATERNITY_EPISODES_MOTHER].[LOCAL_PATIENT_IDENTIFIER]
AND 'DELIVERY' = [MATERNITY_EPISODES_MOTHER].[RECORD_TYPE]
AND CONCAT(LTRIM(RTRIM(Date_of_Birth)),Time_of_Birth) >= CONCAT([MATERNITY_EPISODES_MOTHER].[START_DATE_EPISODE_JULIAN],[MATERNITY_EPISODES_MOTHER].[START_TIME_EPISODE_JULIAN] )
AND CONCAT(LTRIM(RTRIM(Date_of_Birth)),Time_of_Birth) < CONCAT([MATERNITY_EPISODES_MOTHER].[END_DATE_EPISODE_JULIAN],[MATERNITY_EPISODES_MOTHER].[END_TIME_EPISODE_JULIAN] )
LEFT JOIN [TMP_MATERNITY_EPISODES] [MATERNITY_EPISODES_BABY]
ON ISNULL(BABY_MERGE.NEW_LOCAL_PATIENT_IDENTIFIER, [PATIENTBABY].[OID]) = [MATERNITY_EPISODES_BABY].[LOCAL_PATIENT_IDENTIFIER]
AND 'BIRTH' = [MATERNITY_EPISODES_BABY].[RECORD_TYPE]
AND CONCAT(LTRIM(RTRIM(Date_of_Birth)),Time_of_Birth) >= CONCAT([MATERNITY_EPISODES_BABY].[START_DATE_EPISODE_JULIAN],[MATERNITY_EPISODES_BABY].[START_TIME_EPISODE_JULIAN] )
AND CONCAT(LTRIM(RTRIM(Date_of_Birth)),Time_of_Birth) < CONCAT([MATERNITY_EPISODES_BABY].[END_DATE_EPISODE_JULIAN],[MATERNITY_EPISODES_BABY].[END_TIME_EPISODE_JULIAN] )
WHERE
[PROTOSMODEL_Pregnancy_Record].[Source_CreatedDate] >= ISNULL([PROTOS_START].[ParameterValue], 'Jan 1 2013')
AND [PROTOSMODEL_Pregnancy_Record].[Source_CreatedDate] <= ISNULL([GO_LIVE].[ParameterValue], 'May 15 2017')
AND [PREGNANCY_LIST].[LOCAL_PATIENT_IDENTIFIER] IS NULL
AND
(
CASE
WHEN (SELECT COUNT(*) FROM __tblOrchestratorVariable
WHERE __tblOrchestratorVariable.VariableValue = '06n+pMwfaok='
AND __tblOrchestratorVariable.Variable = 'PROCESS_PATIENT_CHANGES_INCREMENTAL_DECIPHER') > 0 AND
ISNULL((SELECT tblRefDWParameter.ParameterValue
FROM tblRefDWParameter
WHERE tblRefDWParameter.Parameter = 'Full AHDE reprocess day of week')
, '') <> IsNull(DATENAME(WEEKDAY, (SELECT tblRefBuildModifiedDate.ModifiedDate FROM tblRefBuildModifiedDate)
), 'A') THEN MUM_LPI_CHECK.LOCAL_PATIENT_IDENTIFIER ELSE ''
END IS NOT NULL
OR
CASE
WHEN (SELECT COUNT(*) FROM __tblOrchestratorVariable
WHERE __tblOrchestratorVariable.VariableValue = '06n+pMwfaok='
AND __tblOrchestratorVariable.Variable = 'PROCESS_PATIENT_CHANGES_INCREMENTAL_DECIPHER') > 0 AND
ISNULL((SELECT tblRefDWParameter.ParameterValue
FROM tblRefDWParameter
WHERE tblRefDWParameter.Parameter = 'Full AHDE reprocess day of week')
, '') <> IsNull(DATENAME(WEEKDAY, (SELECT tblRefBuildModifiedDate.ModifiedDate FROM tblRefBuildModifiedDate)
), 'A') THEN BABY_LPI_CHECK.LOCAL_PATIENT_IDENTIFIER ELSE ''
END IS NOT NULL
)
GO