Optimizando el rendimiento de un largo recorrido de una consulta SQL -- performance campo con sql campo con t-sql camp codereview Relacionados El problema

Optimising performance of long running a sql query


2
vote

problema

Español

El código a continuación toma 5-6 horas por un valor de los años (850,000 registros). Lleva mucho menos tiempo al ejecutar un solo mes.

Para empezar, hay algo evidente en este script que puede explicar la lentitud. ¿Hay alguna prueba que pueda hacer para determinar dónde están las fallas? Me encantará quitarse (actualmente) 5 horas solo para mover datos de un conjunto de tablas a otra tabla.

¿Hay un enfoque diferente que se puede usar otro entonces ejecutar este script cada mes?

La gran declaración SELECT al principio toma segundos para que el problema no está allí.

      public int IntValue     {         get { return m_IntValue; }         set         {             m_IntValue = value;             AddOrUpdateProperty(value);         }     }     public string StringValue     {         get { return m_StringValue; }         set         {             m_StringValue = value;             AddOrUpdateProperty((IComparable)value);         }     }     public byte[] ByteArray     {         get { return m_ByteArray; }         set         {             m_ByteArray = value;             AddOrUpdateProperty(value);         }     } 3  
Original en ingles

The code below takes 5-6 hours for a years worth of data (850,000 records). It takes much less time when running a single month.

To start with is there anything glaringly obvious within this script which may account for the slowness. Are there any tests I can do to determine where the flaws are. I kind of find it off it takes (currently) 5 hours just to move data from a set of tables into another table.

Is there a different approach which can be used other then running this script each month?

The big select statement at the beginning takes seconds so the issue isn't there.

USE HealthBI GO  -- basic statement returns 292520 rows on 20/07/2015 -- basic statement returns 318249 rows on 05/08/2015 /** 28/09/2015 (PJ) Updated statement to pull CCG of Res from CDO_CDS_62_OPS_020_02_PATIENT_IDENTITY  rather than CDO_ODS_EXTENDED_POSTCODE table (Note: HA still coming from CDO_ODS_EXTENDED_POSTCODE)  Replaced CAST(POSTCODE.CCG_OF_RESIDENCE AS VARCHAR(5)) with CAST(PATIENT_IDENTITY.ORGANISATION_CODE_RESIDENCE_RESPONSIBILITY AS VARCHAR(5)) **/ -- 01/12/2015 (PJ) Added population of OldCDSIdentifier field with the attendance OID to enable easy linking between data and HealthBI tables -- 02/08/2016 (JB) Tidied/formatted code -- 07/11/2016 (PJ) Added diagnosis code into statements below -- Note: May need to add additional code below to suppress fictitious 'Z000' code.  INSERT INTO [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientData ([CDSType],[CDSIdentifier], [ProviderCode],[PCTOfResidence],[PCT],[HACodeOfResidence],[PostcodeOfUsualAddress], [SiteCodeOfTreatment],[AdministrativeCategory],[NAC_HRG],[LocalClinicCode], [LocalPatientIdentifier],[NHSNumber],[NNNStatusIndicator],[CarerSupportIndicator],[Sex], [BirthDate],[CodeOfRegisteredGP],[PracticeCodeOfRegisteredGP],[AttendanceTime],[AttendanceDate], [AttendedDidNotAttend],[FirstAttendance],[MedicalStaffTypeSeeingPatient],[OutcomeOfAttendance], [LocationTypeCode],[SpecialtyFunctionCode],[ConsultantSpecialtyFunctionCode],[CodeOfConsultant], [PrimaryDiagnosisICD],[OperationStatus],[PrimaryProcedureOPCS],[PriorityType],[ReferrerCode], [ReferringOrganisationCode],[ServiceTypeRequested],[SourceOfReferral], [ReferralRequestReceivedDate],[LastDNACancelledDate],[CasenoteNumber],[LocalSubSpecialty],[UBRN], [PPI],[OrgCodePPI],[RTTPeriodStatus],[RTTPeriodStart],[RTTPeriodEnd],[AgeAtCDSDate],[ERO_Date], [UpdateType],[EthnicGroup],[OutpatientID],[SerialNumber],[CDSUpdateDate],[CDSUpdateTime], [FinancialYear],[FileNumber],[NationalSpecialty],[InternalNo],[EpisodeNo],[OldCDSIdentifier])   SELECT  CAST(TRANSACTION_HEADER.CDS_TYPE AS VARCHAR(3))         ,CAST(TRANSACTION_HEADER.CDS_UNIQUE_ID AS VARCHAR(40))         ,CAST(SERVICE_AGREEMENT.ORGANISATION_CODE_CODE_OF_PROVIDER AS VARCHAR(5))         ,CAST(PATIENT_IDENTITY.ORGANISATION_CODE_RESIDENCE_RESPONSIBILITY AS VARCHAR(5))         ,CAST(GP_PRACTICE.CCG_PARENT_ORGANISATION_CODE AS VARCHAR(5))         ,CAST(POSTCODE.ORGANISATION_CODE_HA AS VARCHAR(3))         -- FIND THE PATIENT WHO HAS POSTCODE HU12  0HE (NOTE THE TWO SPACES!)         ,CAST(REPLACE(PATIENT_IDENTITY.POSTCODE_OF_USUAL_ADDRESS,'  ',' ') AS VARCHAR(8)) USUAL_POSTCODE         ,CAST(LOCATION.SITE_CODE_OF_TREATMENT AS VARCHAR(5))         ,CAST(MAIN.ADMINISTRATIVE_CATEGORY_CODE AS VARCHAR(2))         ,CAST(OP_APPT.HRG_CODE AS VARCHAR(5))         ,CAST(  CASE    WHEN SUBSTRING(LOCATION.CLINIC_CODE,1,3) = 'HWS' THEN SUBSTRING(LOCATION.CLINIC_CODE,4,LEN(LOCATION.CLINIC_CODE))                          ELSE LOCATION.CLINIC_CODE                  END AS VARCHAR(8)) AS LOCAL_CLINIC_CODE         ,CAST(PATIENT_IDENTITY.LOCAL_PATIENT_IDENTIFIER AS VARCHAR(10))         ,CAST(PATIENT_IDENTITY.NHS_NUMBER AS VARCHAR(10))         ,CAST(PATIENT_IDENTITY.NHS_NUMBER_STATUS_INDICATOR_CODE AS VARCHAR(2))         ,CAST(PATIENT_CHARACTERISTICS.CARER_SUPPORT_INDICATOR AS VARCHAR(2))         ,CAST(PATIENT_CHARACTERISTICS.PERSON_GENDER_CODE_CURRENT AS VARCHAR(1))         ,CAST(REPLACE(PATIENT_IDENTITY.PERSON_BIRTH_DATE,'-','') AS VARCHAR(8)) DOB         ,CAST(GP_REGISTRATION.GENERAL_MEDICAL_PRACTITIONER_SPECIFIED AS VARCHAR(8))         ,CAST(GP_REGISTRATION.GENERAL_MEDICAL_PRACTICE_CODE_PATIENT_REGISTRATION AS VARCHAR(6))         ,CAST(REPLACE(SUBSTRING(MAIN.APPOINTMENT_TIME,1,5),':','') AS VARCHAR(4)) APPT_TIME         ,CAST(REPLACE(MAIN.APPOINTMENT_DATE,'-','') AS VARCHAR(8)) APPT_DATE         ,CAST(MAIN.ATTENDED_OR_DID_NOT_ATTEND_CODE AS VARCHAR(1))         ,CAST(MAIN.FIRST_ATTENDANCE_CODE AS VARCHAR(1))         ,CAST(MAIN.MEDICAL_STAFF_TYPE_SEEING_PATIENT AS VARCHAR(2))         ,CAST(MAIN.OUTCOME_OF_ATTENDANCE_CODE AS VARCHAR(1))         ,NULL AS ACTIVITY_LOCATION_TYPE_CODE         ,CAST(CONSULTANT.CARE_PROFESSIONAL_MAIN_SPECIALTY_CODE AS VARCHAR(3))         ,CAST(  CASE    WHEN CONSULTANT.ACTIVITY_TREATMENT_FUNCTION_CODE = '313' THEN '316'                          WHEN CONSULTANT.ACTIVITY_TREATMENT_FUNCTION_CODE = '172' THEN '170'                                                              WHEN CONSULTANT.ACTIVITY_TREATMENT_FUNCTION_CODE = '173' THEN '170'                                                                  ELSE CONSULTANT.ACTIVITY_TREATMENT_FUNCTION_CODE                  END AS VARCHAR(3))         ,CAST(CONSULTANT.CONSULTANT_CODE AS VARCHAR(8))         ,CAST(ICD_PRIMARY.PRIMARY_DIAGNOSIS_ICD AS VARCHAR(4))               ,CAST(MAIN.OPERATION_STATUS_CODE AS VARCHAR(1))         ,CAST(OPCS_PRIMARY.PRIMARY_PROCEDURE_CODE_OPCS AS VARCHAR(4))         ,CAST(REFERRAL.PRIORITY_TYPE_CODE AS VARCHAR(1))         ,CAST(REFERRER.REFERRER_CODE AS VARCHAR(8))         ,CAST(  CASE    WHEN REFERRER.REFERRING_ORGANISATION_CODE = 'SPIREHERH' THEN NULL                          ELSE REFERRER.REFERRING_ORGANISATION_CODE                  END AS VARCHAR(6)) AS REF_ORG         ,CAST(REFERRAL.SERVICE_TYPE_REQUESTED_CODE AS VARCHAR(1))         ,CAST(REFERRAL.SOURCE_OF_REFERRAL_FOR_OUT_PATIENTS AS VARCHAR(2))         ,CAST(REPLACE(REFERRAL.REFERRAL_REQUEST_RECEIVED_DATE,'-','') AS VARCHAR(8)) REF_REC_DATE         ,CAST(REPLACE(MISSED_APPOINTMENT.LAST_DNA_OR_PATIENT_CANCELLED_DATE,'-','') AS VARCHAR(8)) LAST_DNA_CANC_DATE         ,CAST(PATIENT_IDENTITY.LOCAL_PATIENT_IDENTIFIER AS VARCHAR(10)) CASENOTE_NUMBER         -- ward         -- funding         -- cancdate         -- cancby         -- dind         -- attcat         -- appreq         -- gprdiag         -- refhosp         -- appthosp         -- gpfh         ,CAST(CONSULTANT.LOCAL_SUB_SPECIALTY_CODE AS VARCHAR(3))         ,CAST(PATIENT_PATHWAY.UNIQUE_BOOKING_REFERENCE_NUMBER AS VARCHAR(12))         -- SOME ARE UP TO 23 CHARS LONG         ,SUBSTRING(PATIENT_PATHWAY.PATIENT_PATHWAY_IDENTIFIER,1,20) AS PPI         ,CAST(PATIENT_PATHWAY.ORGANISATION_CODE_PATIENT_PATHWAY_IDENTIFIER AS VARCHAR(5))         ,CAST(PATIENT_PATHWAY.REFERRAL_TO_TREATMENT_PERIOD_STATUS AS VARCHAR(2))         ,CAST(REPLACE(PATIENT_PATHWAY.REFERRAL_TO_TREATMENT_PERIOD_START_DATE,'-','') AS VARCHAR(8)) RTT_START         ,CAST(REPLACE(PATIENT_PATHWAY.REFERRAL_TO_TREATMENT_PERIOD_END_DATE,'-','') AS VARCHAR(8)) RTT_END         ,CAST(MAIN.AGE_AT_CDS_ACTIVITY_DATE AS VARCHAR(3))         ,CAST(REPLACE(MAIN.EARLIEST_REASONABLE_OFFER_DATE,'-','') AS VARCHAR(8)) ERO_DATE         ,CAST('9' AS VARCHAR(1)) UPDATETYPE         -- localappttype         ,CAST(PATIENT_CHARACTERISTICS.ETHNIC_CATEGORY AS VARCHAR(2))         ,OP_APPT.UNIQUE_ID  -- outpatientID         ,CAST('HEY' AS VARCHAR(6)) SERIALNUMBER         ,CAST(CONVERT(CHAR(8), GETDATE(), 112) AS VARCHAR(8)) CDSUpdateDate         ,CAST(REPLACE(CONVERT(CHAR(5), GETDATE(), 108),':','') AS VARCHAR(4)) CDSUpdateTime          -- 02/06/2016 (JB) Not needed, but left in as useful around fiscal change when processing multiple months together (M12 Freeze + M01 Flex)         ,   CASE    WHEN CONVERT(DATE,MAIN.APPOINTMENT_DATE) >= 'APRIL 1 2016' THEN '2016/2017'                     ELSE '2015/2016'             END AS FinancialYear          ,(SELECT        MAX(FileNumber) + 100         FROM        [CHH-SLAMDB].[SLAMS_SQL].[dbo].[ImportLog]         WHERE       ActivityType = 'Outpatient') -- File Number, used to be manually changed every time (EIR & Live)          ,CAST(  CASE    WHEN CONSULTANT.ACTIVITY_TREATMENT_FUNCTION_CODE = '313' THEN '316'                          WHEN CONSULTANT.ACTIVITY_TREATMENT_FUNCTION_CODE = '172' THEN '170'                         WHEN CONSULTANT.ACTIVITY_TREATMENT_FUNCTION_CODE = '173' THEN '170'                         ELSE CONSULTANT.ACTIVITY_TREATMENT_FUNCTION_CODE                  END AS VARCHAR(3))         ,'NA' InternalNo         ,'NA' EpisodeNo         ,CAST(MAIN.ATTENDANCE_IDENTIFIER AS VARCHAR(35)) FROM    dbo.CDO_CDS_62_OPS_020_00_TRANSACTION_HEADER TRANSACTION_HEADER LEFT JOIN         dbo.CDO_CDS_62_OPS_020_07_ATTENDANCE_OCCURRENCE_ACTIVITY [MAIN] ON                  TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = MAIN.JOIN_APPOINTMENT_IDENTIFIER                 AND TRANSACTION_HEADER.EXTRACT_ID = MAIN.EXTRACT_ID LEFT JOIN          dbo.CDO_CDS_62_OPS_020_08_ATTENDANCE_OCCURRENCE_SERVICE_AGREEMENT SERVICE_AGREEMENT ON                  TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = SERVICE_AGREEMENT.JOIN_APPOINTMENT_IDENTIFIER                 AND TRANSACTION_HEADER.EXTRACT_ID = SERVICE_AGREEMENT.EXTRACT_ID LEFT JOIN         dbo.CDO_CDS_62_OPS_020_02_PATIENT_IDENTITY PATIENT_IDENTITY ON                 TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = PATIENT_IDENTITY.JOIN_APPOINTMENT_IDENTIFIER                 AND TRANSACTION_HEADER.EXTRACT_ID = PATIENT_IDENTITY.EXTRACT_ID LEFT JOIN         dbo.CDO_ODS_EXTENDED_POSTCODE POSTCODE ON                 PATIENT_IDENTITY.POSTCODE_OF_USUAL_ADDRESS = POSTCODE.POST_CODE_VARYING LEFT JOIN         dbo.CDO_CDS_62_OPS_020_12_GP_REGISTRATION GP_REGISTRATION ON                 TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = GP_REGISTRATION.JOIN_APPOINTMENT_IDENTIFIER                 AND TRANSACTION_HEADER.EXTRACT_ID = GP_REGISTRATION.EXTRACT_ID LEFT JOIN          dbo.CDO_ODS_GENERAL_MEDICAL_PRACTICE GP_PRACTICE ON                  GP_REGISTRATION.GENERAL_MEDICAL_PRACTICE_CODE_PATIENT_REGISTRATION = GP_PRACTICE.ORGANISATION_CODE LEFT JOIN          dbo.CDO_CDS_62_OPS_020_11_ATTENDANCE_OCCURRENCE_LOCATION_GROUP LOCATION ON                  TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = LOCATION.JOIN_APPOINTMENT_IDENTIFIER                 AND TRANSACTION_HEADER.EXTRACT_ID = LOCATION.EXTRACT_ID LEFT JOIN          dbo.CDO_OP_APPOINTMENT OP_APPT ON                  TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = OP_APPT.APPOINTMENT_IDENTIFIER LEFT JOIN         dbo.CDO_CDS_62_OPS_020_03_PATIENT_CHARACTERISTICS PATIENT_CHARACTERISTICS ON                 TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = PATIENT_CHARACTERISTICS.JOIN_APPOINTMENT_IDENTIFIER                 AND TRANSACTION_HEADER.EXTRACT_ID = PATIENT_CHARACTERISTICS.EXTRACT_ID LEFT JOIN         dbo.CDO_CDS_62_OPS_020_04_CARE_EPISODE_PERSON CONSULTANT ON                  TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = CONSULTANT.JOIN_APPOINTMENT_IDENTIFIER                 AND TRANSACTION_HEADER.EXTRACT_ID = CONSULTANT.EXTRACT_ID LEFT JOIN          dbo.CDO_CDS_62_OPS_020_09A_ATTENDANCE_OCCURRENCE_CLINICAL_ACTIVITY_OPCS_PRIMARY OPCS_PRIMARY ON                  TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = OPCS_PRIMARY.JOIN_APPOINTMENT_IDENTIFIER                 AND TRANSACTION_HEADER.EXTRACT_ID = OPCS_PRIMARY.EXTRACT_ID LEFT JOIN          dbo.CDO_CDS_62_OPS_020_13_REFERRAL_ACTIVITY REFERRAL ON                  TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = REFERRAL.JOIN_APPOINTMENT_IDENTIFIER                 AND TRANSACTION_HEADER.EXTRACT_ID = REFERRAL.EXTRACT_ID LEFT JOIN         dbo.CDO_CDS_62_OPS_020_14_REFERRER REFERRER ON                  TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = REFERRER.JOIN_APPOINTMENT_IDENTIFIER                 AND TRANSACTION_HEADER.EXTRACT_ID = REFERRER.EXTRACT_ID LEFT JOIN          dbo.CDO_CDS_62_OPS_020_15_MISSED_APPOINTMENT MISSED_APPOINTMENT ON                  TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = MISSED_APPOINTMENT.JOIN_APPOINTMENT_IDENTIFIER                 AND TRANSACTION_HEADER.EXTRACT_ID = MISSED_APPOINTMENT.EXTRACT_ID LEFT JOIN          dbo.CDO_CDS_62_OPS_020_01_PATIENT_PATHWAY PATIENT_PATHWAY ON                  TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = PATIENT_PATHWAY.JOIN_APPOINTMENT_IDENTIFIER                 AND TRANSACTION_HEADER.EXTRACT_ID = PATIENT_PATHWAY.EXTRACT_ID LEFT JOIN         dbo.CDO_CDS_62_OPS_020_05A_CARE_EPISODE_CLINICAL_DIAGNOSIS_ICD_PRIMARY ICD_PRIMARY ON                  TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = ICD_PRIMARY.JOIN_APPOINTMENT_IDENTIFIER                 AND TRANSACTION_HEADER.EXTRACT_ID = ICD_PRIMARY.EXTRACT_ID WHERE   TRANSACTION_HEADER.EXTRACT_ID = (   SELECT  MAX(EXTRACT_ID)                                              FROM    dbo.CDO_CDS_62_OPS_020_00_TRANSACTION_HEADER)         AND TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER NOT LIKE '%NDC'         AND MAIN.ATTENDED_OR_DID_NOT_ATTEND_CODE IN ('3','5','6','7')         AND TRANSACTION_HEADER.CDS_ACTIVITY_DATE < CONVERT(CHAR(10), GETDATE(), 120)         AND CONVERT(DATE,MAIN.APPOINTMENT_DATE) >= 'APRIL 1 2016'         AND CONVERT(DATE,MAIN.APPOINTMENT_DATE) < 'APRIL 1 2017' -- END DATE, Needs Changing Every (EIR) Time         AND SUBSTRING(LOCATION.CLINIC_CODE,1,3) <> 'HWS'  -- 16/11/15 (JB) Added end date where condition, to prevent additional data for current month and additional deletion step after this script -- 24/08/15 (PJ) Removed ATTENDED_OR_DID_NOT_ATTEND_CODE '2' (cancelled by patient) from WHERE clause above -- 24/08/15 (PJ) Only very small number of cancellations were previously included in CDS and submitted to SUS -- as the majority had a NULL attend code field which meant the clinicom CDS ignored these.  IF OBJECT_ID('tempdb..#TMP_PROC_PIVOT') IS NOT NULL BEGIN     DROP TABLE #TMP_PROC_PIVOT END  SELECT      TRANSACTION_HEADER.CDS_UNIQUE_ID             ,MAX(CASE WHEN OPCS_SECONDARY.SEQUENCE_NUMBER = 1 THEN OPCS_SECONDARY.SECONDARY_PROCEDURE_CODE_OPCS ELSE NULL END) AS operatn2             ,MAX(CASE WHEN OPCS_SECONDARY.SEQUENCE_NUMBER = 2 THEN OPCS_SECONDARY.SECONDARY_PROCEDURE_CODE_OPCS ELSE NULL END) AS operatn3             ,MAX(CASE WHEN OPCS_SECONDARY.SEQUENCE_NUMBER = 3 THEN OPCS_SECONDARY.SECONDARY_PROCEDURE_CODE_OPCS ELSE NULL END) AS operatn4             ,MAX(CASE WHEN OPCS_SECONDARY.SEQUENCE_NUMBER = 4 THEN OPCS_SECONDARY.SECONDARY_PROCEDURE_CODE_OPCS ELSE NULL END) AS operatn5             ,MAX(CASE WHEN OPCS_SECONDARY.SEQUENCE_NUMBER = 5 THEN OPCS_SECONDARY.SECONDARY_PROCEDURE_CODE_OPCS ELSE NULL END) AS operatn6             ,MAX(CASE WHEN OPCS_SECONDARY.SEQUENCE_NUMBER = 6 THEN OPCS_SECONDARY.SECONDARY_PROCEDURE_CODE_OPCS ELSE NULL END) AS operatn7             ,MAX(CASE WHEN OPCS_SECONDARY.SEQUENCE_NUMBER = 7 THEN OPCS_SECONDARY.SECONDARY_PROCEDURE_CODE_OPCS ELSE NULL END) AS operatn8             ,MAX(CASE WHEN OPCS_SECONDARY.SEQUENCE_NUMBER = 8 THEN OPCS_SECONDARY.SECONDARY_PROCEDURE_CODE_OPCS ELSE NULL END) AS operatn9             ,MAX(CASE WHEN OPCS_SECONDARY.SEQUENCE_NUMBER = 9 THEN OPCS_SECONDARY.SECONDARY_PROCEDURE_CODE_OPCS ELSE NULL END) AS operatn10             ,MAX(CASE WHEN OPCS_SECONDARY.SEQUENCE_NUMBER = 10 THEN OPCS_SECONDARY.SECONDARY_PROCEDURE_CODE_OPCS ELSE NULL END) AS operatn11             ,MAX(CASE WHEN OPCS_SECONDARY.SEQUENCE_NUMBER = 11 THEN OPCS_SECONDARY.SECONDARY_PROCEDURE_CODE_OPCS ELSE NULL END) AS operatn12 INTO        #TMP_PROC_PIVOT FROM        dbo.CDO_CDS_62_OPS_020_00_TRANSACTION_HEADER TRANSACTION_HEADER INNER JOIN             dbo.CDO_CDS_62_OPS_020_07_ATTENDANCE_OCCURRENCE_ACTIVITY [MAIN] ON                  TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = MAIN.JOIN_APPOINTMENT_IDENTIFIER                 AND TRANSACTION_HEADER.EXTRACT_ID = MAIN.EXTRACT_ID INNER JOIN              dbo.CDO_CDS_62_OPS_020_09B_ATTENDANCE_OCCURRENCE_CLINICAL_ACTIVITY_OPCS_SECONDARY OPCS_SECONDARY ON                  TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = OPCS_SECONDARY.JOIN_APPOINTMENT_IDENTIFIER                 AND TRANSACTION_HEADER.EXTRACT_ID = OPCS_SECONDARY.EXTRACT_ID WHERE       TRANSACTION_HEADER.EXTRACT_ID = (   SELECT  MAX(EXTRACT_ID)                                                  FROM    dbo.CDO_CDS_62_OPS_020_00_TRANSACTION_HEADER)             AND TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER NOT LIKE '%NDC'             AND MAIN.ATTENDED_OR_DID_NOT_ATTEND_CODE IN ('3','5','6','7')             AND TRANSACTION_HEADER.CDS_ACTIVITY_DATE < CONVERT(CHAR(10), GETDATE(), 120) GROUP BY    TRANSACTION_HEADER.CDS_UNIQUE_ID  -- 24/08/2015 (PJ) Removed ATTENDED_OR_DID_NOT_ATTEND_CODE '2' (cancelled by patient) from WHERE clause above UPDATE  [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientData  SET     ProcedureCode2OPCS = [PROC].operatn2,          ProcedureCode3OPCS = [PROC].operatn3,          ProcedureCode4OPCS = [PROC].operatn4,          ProcedureCode5OPCS = [PROC].operatn5,          ProcedureCode6OPCS = [PROC].operatn6,          ProcedureCode7OPCS = [PROC].operatn7,          ProcedureCode8OPCS = [PROC].operatn8,          ProcedureCode9OPCS = [PROC].operatn9,          ProcedureCode10OPCS = [PROC].operatn10,          ProcedureCode11OPCS = [PROC].operatn11,          ProcedureCode12OPCS = [PROC].operatn12 FROM    [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientData AS opa INNER JOIN         #TMP_PROC_PIVOT [PROC]  ON opa.CDSIdentifier COLLATE SQL_Latin1_General_CP1_CI_AS = [PROC].CDS_UNIQUE_ID COLLATE SQL_Latin1_General_CP1_CI_AS  -- 07/11/2016 (PJ) Added code below to populate any instances that have more than 1 diagnosis code (same process as utilised above) IF OBJECT_ID('tempdb..#TMP_DIAG_PIVOT') IS NOT NULL BEGIN     DROP TABLE #TMP_DIAG_PIVOT END  SELECT      TRANSACTION_HEADER.CDS_UNIQUE_ID             ,MAX(CASE WHEN ICD_SECONDARY.SEQUENCE_NUMBER = 1 THEN ICD_SECONDARY.SECONDARY_DIAGNOSIS_ICD ELSE NULL END) AS diag2             ,MAX(CASE WHEN ICD_SECONDARY.SEQUENCE_NUMBER = 2 THEN ICD_SECONDARY.SECONDARY_DIAGNOSIS_ICD ELSE NULL END) AS diag3 INTO        #TMP_DIAG_PIVOT FROM        dbo.CDO_CDS_62_OPS_020_00_TRANSACTION_HEADER TRANSACTION_HEADER INNER JOIN             dbo.CDO_CDS_62_OPS_020_07_ATTENDANCE_OCCURRENCE_ACTIVITY [MAIN] ON                  TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = MAIN.JOIN_APPOINTMENT_IDENTIFIER                 AND TRANSACTION_HEADER.EXTRACT_ID = MAIN.EXTRACT_ID INNER JOIN              dbo.CDO_CDS_62_OPS_020_05B_CARE_EPISODE_CLINICAL_DIAGNOSIS_ICD_SECONDARY ICD_SECONDARY ON                  TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER = ICD_SECONDARY.JOIN_APPOINTMENT_IDENTIFIER                 AND TRANSACTION_HEADER.EXTRACT_ID = ICD_SECONDARY.EXTRACT_ID WHERE       TRANSACTION_HEADER.EXTRACT_ID = (   SELECT  MAX(EXTRACT_ID)                                                  FROM    dbo.CDO_CDS_62_OPS_020_00_TRANSACTION_HEADER)             AND TRANSACTION_HEADER.JOIN_APPOINTMENT_IDENTIFIER NOT LIKE '%NDC'             AND MAIN.ATTENDED_OR_DID_NOT_ATTEND_CODE IN ('3','5','6','7')             AND TRANSACTION_HEADER.CDS_ACTIVITY_DATE < CONVERT(CHAR(10), GETDATE(), 120) GROUP BY    TRANSACTION_HEADER.CDS_UNIQUE_ID  UPDATE  [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientData  SET     SubsidiaryDiagnosisICD = [DIAG].diag2,          SecondaryDiagnosisICD = [DIAG].diag3 FROM    [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientData AS opa INNER JOIN         #TMP_DIAG_PIVOT [DIAG]  ON opa.CDSIdentifier COLLATE SQL_Latin1_General_CP1_CI_AS = [DIAG].CDS_UNIQUE_ID COLLATE SQL_Latin1_General_CP1_CI_AS   -- Added 20/07/2015 (PJ) Delete activity at these clinics as they were previously recorded as VAP local appointment types DELETE FROM [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientData  WHERE       LocalClinicCode IN('AIHVC','HLOVC','JDAVC','JEHVC','JGVC','KILVC')  -- Added 20/07/2015 (PJ) populates local diagnostic appt types to try to create consistency with first 2 months UPDATE  [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientData  SET     LocalApptType = CASE    WHEN FirstAttendance = 1 THEN 'NDI'                                 WHEN FirstAttendance = 2 THEN 'ODI'                                 WHEN FirstAttendance = 3 THEN 'NDI'                                 WHEN FirstAttendance = 4 THEN 'ODI'                                  ELSE NULL                          END FROM    [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientData AS op INNER JOIN          [HealthBI].[dbo].[CDO_CLINIC_OR_FACILITY] cl ON                 op.LocalClinicCode COLLATE SQL_Latin1_General_CP1_CI_AS = cl.CLINIC_CODE COLLATE SQL_Latin1_General_CP1_CI_AS WHERE   cl.TR_DIAGNOSTIC_CLINIC <> '0'  -- Added 20/07/2015 (PJ) populates local telephone appt types to try to create consistency with first 2 months UPDATE  [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientData  SET     LocalApptType = CASE    WHEN FirstAttendance = 3 THEN 'OTP'                                 WHEN FirstAttendance = 4 THEN 'NTP'                                  ELSE LocalApptType                          END FROM    [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientData  -- Added 20/07/2015 (PJ) populates local supplementary appt types to try to create consistency with first 2 months UPDATE  [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientData  SET     LocalApptType = 'SUP' WHERE   tblOutpatientData.LocalClinicCode IN('CAUD002','CAUDINIT','CF001','CF002','LEA00TEV','MACFC','OLS002','RVO00V')  -- Added 20/07/2015 (PJ) Delete therapies activity -- Added 02/03/2016 (JB) Delete therapies activity, added 'OR' as original code above compared  -- full length clinics in HealthBI.dbo.CDO_CLINIC_OR_FACILITY against 8 letter character  -- clinics in OutpatientData (as shortened when grabbed above) DELETE FROM [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientData  WHERE       LocalClinicCode IN  (SELECT CLINIC_CODE COLLATE SQL_Latin1_General_CP1_CI_AS                                  FROM    HealthBI.dbo.CDO_CLINIC_OR_FACILITY                                  WHERE   TR_IS_AHP_CLINIC <> '0') OR             LocalClinicCode IN  (SELECT     Clinic COLLATE SQL_Latin1_General_CP1_CI_AS                                  FROM        CommDB.dbo.tblLocalClinicExtension                                  WHERE       AHPClinic <> '0')  -- Added 13/01/2016 (PJ) Delete non-RWA activity DELETE FROM [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientData  WHERE       LocalClinicCode IN  (SELECT CLINIC_CODE COLLATE SQL_Latin1_General_CP1_CI_AS                                  FROM    HealthBI.dbo.CDO_CLINIC_OR_FACILITY                                  WHERE LOCAL_ORGANISATION_CODE_TRUST <> 'RWA')   -- Added 03/05/2016 (PJ) Updates Outcome of Attendance field for cancellations UPDATE  [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientData SET     OutcomeOfAttendance =   CASE    WHEN wl.BookingOutcomeCode = 'CC_REMOVEAPE' THEN '1'                                         WHEN wl.BookingOutcomeCode = 'CC_REMAINAPE' THEN '3'                                         ELSE OutcomeOfAttendance                                  END FROM    [CHH-SLAMDB].SLAMS_SQL.dbo.tblOutpatientData AS op INNER JOIN          [HealthBI_Views].[dbo].[OP_APPOINTMENT] AS opappt ON                  op.OldCDSIdentifier COLLATE SQL_Latin1_General_CP1_CI_AS = opappt.ApptID COLLATE SQL_Latin1_General_CP1_CI_AS LEFT JOIN          [HealthBI_Views].[dbo].[OP_WAITING_LIST_ENTRY_BOOKING] wl ON                 opappt.BookingOfferID = wl.BookingOfferID WHERE   op.AttendedDidNotAttend IN('2','4')  -- Update Added 02/08/2016 (JB), Set GP Code and Gp Practice Code to 'R9999981' and 'V81998' when the patient has a postcode that's a prison UPDATE  [CHH-SLAMDB].[SLAMS_SQL].[dbo].[tblOutpatientData] SET     [CodeOfRegisteredGP] = 'R9999981',         [PracticeCodeOfRegisteredGP] = 'V81998',         [pct] = 'Q72' WHERE   [PostcodeOfUsualAddress] COLLATE Latin1_General_CI_AS IN    (SELECT DISTINCT    [Postcode] COLLATE Latin1_General_CI_AS                                                                     FROM                [CHH-BILIVE].[CommDB].[dbo].[tblODSPrison]) 
        
     
     

Lista de respuestas

1
 
vote

El lugar donde está rompiendo la primera urgencia de la izquierda

Usted está obteniendo Max en cada fila. Consíguelo una vez y guárdelo en una variable.

 

The where is breaking the first left join

You are getting max on every row. Get it once and save it in a variable.

 
 

Relacionados problema

6  Top usuarios de Bodged Python de NYC  ( Top python badged users from nyc ) 
i bifurcado este explorador de datos de Stackexchange (SEDE) Consulta de otro, y lo hizo un poco más complejo. Estoy buscando comentarios sobre mi estilo, a...

3  ¿Es necesario reemplazar este cursor en SQL Server 2005?  ( Is it necessary to replace this cursor in sql server 2005 ) 
el problema Tengo un cursor que estoy tratando de reemplazar (tal vez innecesariamente) en un intento por limpiar un procedimiento almacenado. Esencialmen...

3  Generador de informes complejo usando una gran consulta SQL  ( Complex report generator using huge sql query ) 
Como dice el título, tengo una gran consulta de SQL que se construyó a lo largo del tiempo (a medida que llegaban las nuevas solicitudes, se exigían las soluc...

4  SQL Server 'Ejecutar como' / Revertir patrón en un bloque 'intento / catch'  ( Sql server execute as revert pattern in a try catch block ) 
Deseo asegurarme de que estoy usando el "mejor" patrón cuando se usa un ejecutivo como / revertir desde dentro de un bloque de prueba / catch en SQL Server 20...

2  T-SQL simplifica los sindicatos de consulta repetidos para cada día de la semana  ( T sql simplify repeated query unions for each day of the week ) 
He creado el código T-SQL a continuación para obtener el siguiente valor de datos más cercano durante los siguientes 7 días. El esquema de tabla ScheduleDet...

2  Totales rodantes en una vista  ( Rolling totals in a view ) 
Tengo una vista que realiza algunos totales rodantes. En este caso, debe estar en una vista. El problema es que tarda unos 10 minutos en ejecutar, devolviendo...

4  Etiqueta wikis bajo cierta longitud  ( Tag wikis under a certain length ) 
Escribí una consulta de sede para enumerar todas las etiquetas wikis con un cuerpo o un extracto bajo un número dado de caracteres. He usado esto para encon...

2  Implementación T-SQL TOTP (RFC6238 SHA2_256)  ( T sql totp rfc6238 sha2 256 implementation ) 
Usando mi Función anterior Como base que he subido lo siguiente: create function dbo.Totp ( @key varbinary(8000) , @timeStep int = 90 ) returns tab...

3  ¿Cuáles son los principales comentarios votados en las preguntas cerradas y que hicieron los comentarios?  ( What are the top voted comments on closed questions and who made the comments ) 
Siguiendo Esta pregunta vinculada y la retroalimentación que generó, coloco un nuevo consulta que utiliza un unión. 127.0.0.1/home/initialize/param1:va...

2  Fórmula Haversine en SQL  ( Haversine formula in sql ) 
Esta es una implementación de la haversine fórmula en Microsoft Transact SQL. ¿Cómo puedo simplificar la función? en1 Aquí hay una prueba de la func...




© 2022 respuesta.top Reservados todos los derechos. Centro de preguntas y respuestas reservados todos los derechos