Sql Server execution statitics - How do read these

Simon Evans 81 Reputation points
2021-04-06T08:15:36.44+00:00

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.

84841-image.png 

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
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,653 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,716 Reputation points
    2021-04-09T17:26:49.937+00:00

    The stats you posted are not really very useful, other than information.

    To diagnose why your query is actually slow you should look at the query plan created.

    A big red flag is your last 2 joins are likely not using an index due to the use of functions in your join condition.

     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] )
    0 comments