question

SimonEvans-7752 avatar image
0 Votes"
SimonEvans-7752 asked TomPhillips-1744 edited

Sql Server execution statitics - How do read these

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-generalsql-server-transact-sql
image.png (63.8 KiB)
· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Can you please let me know what query you used to get this output

0 Votes 0 ·

Added to the original post now!

0 Votes 0 ·

Hi,
What tool(report) or query is used to get the content in above picture. Is the query below the picture what you call a "slow running query"

0 Votes 0 ·
Show more comments

Before you spend too much time on tuning this query: have you verified on smaller data set that it produces the correct result? I have found that slow queries often are slow, because there logical issues in them.

Else I would look into breaking up this query into several with intermediate temp tables. This is a beast even for a season SQL Server query tuner.

0 Votes 0 ·

1 Answer

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered TomPhillips-1744 edited

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] )
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.