Sql-Server
SQL 合併語句在主鍵上花費大量時間排序
我們希望使用 SQL Server 2012 中的 SQL MERGE 語句來處理數據倉庫的數據複製，我們可以從我們用於數據輸入的第 3 方應用程序的製造商處訪問該數據倉庫。
數據倉庫的來源是 Oracle 環境，我們使用 SQL 連接器將環境中的 Oracle 數據庫引用為連結伺服器。數據在每晚一次的轉儲中載入/更新到 Oracle 數據源中。
由於我們在事務和快照複製中看到的不可靠性/不穩定性，我們正在查看 MERGE 語句。
下面是進行合併的表結構：
CREATE TABLE [dbo].[FACT_WIP_2]( [WIP_KEY] [float] NOT NULL, [PATIENT_KEY] [float] NULL, [PHARMACY_KEY] [float] NULL, [LINE_PHARM_KEY] [float] NULL, [DELIVERY_ADDRESS_KEY] [float] NULL, [INVENTORY_TYPE_KEY] [float] NULL, [PHYSICIAN_LOCATION_KEY] [float] NULL, [PRIMARY_ORIGINAL_INS_PLAN_KEY] [float] NULL, [PRIMARY_INSURANCE_PLAN_KEY] [float] NULL, [REFERRAL_PRIORITY_KEY] [float] NULL, [REIMBURSEMENT_STATUS_REF_KEY] [float] NULL, [REIMBURSEMENT_STATUS_STAGE_KEY] [float] NULL, [REFERRAL_SOURCE_TYPE_KEY] [float] NULL, [REFERRAL_START_DATE_KEY] [float] NULL, [STAGE_START_DATE_KEY] [float] NULL, [STAGE_END_DATE_KEY] [float] NULL, [ASSIGNED_EMPLOYEE_KEY] [float] NULL, [REFERRAL_COMPLETION_DATE_KEY] [float] NULL, [REFERRAL_CYCLE_KEY] [float] NULL, [SHIP_DATE_KEY] [float] NULL, [SHIP_MODE_KEY] [float] NULL, [PLACE_OF_SERVICE_KEY] [float] NULL, [REF_BUSINESS_DRIVERS_KEY] [float] NULL, [REF_OUTCOME_STATUS_KEY] [float] NULL, [STAGE_OUTCOME_STATUS_KEY] [float] NULL, [REF_REFERENCE_CATEGORY_KEY] [float] NULL, [THERAPY_GROUP_KEY] [float] NULL, [FORWARD_REASON_KEY] [float] NULL, [FORWARDED_TO_PHARMACY_KEY] [float] NULL, [IMAGE_RECEIPT_DATE_KEY] [float] NULL, [DATA_SOURCE_KEY] [float] NULL, [ORDER_START_DATE_KEY] [float] NULL, [DRUG_KEY] [float] NULL, [WORK_STAGE_KEY] [float] NULL, [REFERRAL_NEED_DATE_KEY] [float] NULL, [ORDER_NEED_DATE_KEY] [float] NULL, [WIP_TYPE] [varchar](1) NULL, [REFERRAL_ID] [varchar](32) NULL, [REFERRAL_LINE_NUM] [float] NULL, [ORDER_ID] [varchar](150) NULL, [ORDER_LINE_NUM] [float] NULL, [DIAGNOSIS_CODE] [varchar](10) NULL, [DIAGNOSIS_DESCRIPTION] [varchar](40) NULL, [QTY_WRITTEN] [float] NULL, [METRIC_QTY] [float] NULL, [DELETED_VOID_INDICATOR] [varchar](1) NULL, [DAY_SUPPLY] [float] NULL, [PROFILED_RX_INDICATOR] [numeric](1, 0) NULL, [PAT_PRIMARY_INS_CARDHOLDER_ID] [varchar](20) NULL, [PAT_PRIMARY_INS_EMPLOYER] [varchar](30) NULL, [PAT_PRIMARY_INS_GROUP_NUMBER] [varchar](30) NULL, [RX_NUMBER] [float] NULL, [REFILL_NUMBER] [float] NULL, [REFERRAL_START_TIMESTAMP] [datetime] NULL, [STAGE_START_TIMESTAMP] [datetime] NULL, [STAGE_END_TIMESTAMP] [datetime] NULL, [WORK_STAGE_ASSIGN_STATUS] [varchar](10) NULL, [REFERRAL_COMPLETION_TIMESTAMP] [datetime] NULL, [ORDER_START_TIMESTAMP] [datetime] NULL, [PRIOR_AUTH_APPEAL_INDICATOR] [varchar](20) NULL, [BV_MISSING_INFO_INDICATOR] [numeric](1, 0) NULL, [ORDER_COMPLETION_TIMESTAMP] [datetime] NULL, [SUPPLY_ITEM_INDICATOR] [numeric](1, 0) NULL, [FORWARDED_REFERRAL_INDICATOR] [numeric](1, 0) NULL, [NOGO_INDICATOR] [numeric](1, 0) NULL, [CREATE_UPDATE_DATE] [datetime] NULL, [ORDER_STATUS] [varchar](50) NULL, [REFERRAL_STATUS] [varchar](50) NULL, [VIRTUAL_STAGE_OUTCOME] [varchar](40) NULL, [VIRTUAL_STAGE_OUTCOME_REASON] [varchar](40) NULL, [UNIQUE_ID] [varchar](250) NULL, [STAGED_DATE_TIMESTAMP] [datetime] NULL, [REWORK_COUNT] [float] NULL, CONSTRAINT [MSHREPL_290_PK_2] PRIMARY KEY CLUSTERED ( [WIP_KEY] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]這是我們用來完成合併的查詢：
MERGE FACT_WIP_2 AS S USING RXCSMAP..SMA.FACT_WIP AS O ON (S.WIP_KEY = O.WIP_KEY) WHEN MATCHED AND O.CREATE_UPDATE_DATE > @DateToStartLooking AND O.CREATE_UPDATE_DATE <> S.CREATE_UPDATE_DATE THEN UPDATE SET S.PATIENT_KEY = O.PATIENT_KEY, S.PHARMACY_KEY = O.PHARMACY_KEY, S.LINE_PHARM_KEY = O.LINE_PHARM_KEY, S.DELIVERY_ADDRESS_KEY = O.DELIVERY_ADDRESS_KEY, S.INVENTORY_TYPE_KEY = O.INVENTORY_TYPE_KEY, S.PHYSICIAN_LOCATION_KEY = O.PHYSICIAN_LOCATION_KEY, S.PRIMARY_ORIGINAL_INS_PLAN_KEY = O.PRIMARY_ORIGINAL_INS_PLAN_KEY, S.PRIMARY_INSURANCE_PLAN_KEY = O.PRIMARY_INSURANCE_PLAN_KEY, S.REFERRAL_PRIORITY_KEY = O.REFERRAL_PRIORITY_KEY, S.REIMBURSEMENT_STATUS_REF_KEY = O.REIMBURSEMENT_STATUS_REF_KEY, S.REIMBURSEMENT_STATUS_STAGE_KEY = O.REIMBURSEMENT_STATUS_STAGE_KEY, S.REFERRAL_SOURCE_TYPE_KEY = O.REFERRAL_SOURCE_TYPE_KEY, S.REFERRAL_START_DATE_KEY = O.REFERRAL_START_DATE_KEY, S.STAGE_START_DATE_KEY = O.STAGE_START_DATE_KEY, S.STAGE_END_DATE_KEY = O.STAGE_END_DATE_KEY, S.ASSIGNED_EMPLOYEE_KEY = O.ASSIGNED_EMPLOYEE_KEY, S.REFERRAL_COMPLETION_DATE_KEY = O.REFERRAL_COMPLETION_DATE_KEY, S.REFERRAL_CYCLE_KEY = O.REFERRAL_CYCLE_KEY, S.SHIP_DATE_KEY = O.SHIP_DATE_KEY, S.SHIP_MODE_KEY = O.SHIP_MODE_KEY, S.PLACE_OF_SERVICE_KEY = O.PLACE_OF_SERVICE_KEY, S.REF_BUSINESS_DRIVERS_KEY = O.REF_BUSINESS_DRIVERS_KEY, S.REF_OUTCOME_STATUS_KEY = O.REF_OUTCOME_STATUS_KEY, S.STAGE_OUTCOME_STATUS_KEY = O.STAGE_OUTCOME_STATUS_KEY, S.REF_REFERENCE_CATEGORY_KEY = O.REF_REFERENCE_CATEGORY_KEY, S.THERAPY_GROUP_KEY = O.THERAPY_GROUP_KEY, S.FORWARD_REASON_KEY = O.FORWARD_REASON_KEY, S.FORWARDED_TO_PHARMACY_KEY = O.FORWARDED_TO_PHARMACY_KEY, S.IMAGE_RECEIPT_DATE_KEY = O.IMAGE_RECEIPT_DATE_KEY, S.DATA_SOURCE_KEY = O.DATA_SOURCE_KEY, S.ORDER_START_DATE_KEY = O.ORDER_START_DATE_KEY, S.DRUG_KEY = O.DRUG_KEY, S.WORK_STAGE_KEY = O.WORK_STAGE_KEY, S.REFERRAL_NEED_DATE_KEY = O.REFERRAL_NEED_DATE_KEY, S.ORDER_NEED_DATE_KEY = O.ORDER_NEED_DATE_KEY, S.WIP_TYPE = O.WIP_TYPE, S.REFERRAL_ID = O.REFERRAL_ID, S.REFERRAL_LINE_NUM = O.REFERRAL_LINE_NUM, S.ORDER_ID = O.ORDER_ID, S.ORDER_LINE_NUM = O.ORDER_LINE_NUM, S.DIAGNOSIS_CODE = O.DIAGNOSIS_CODE, S.DIAGNOSIS_DESCRIPTION = O.DIAGNOSIS_DESCRIPTION, S.QTY_WRITTEN = O.QTY_WRITTEN, S.METRIC_QTY = O.METRIC_QTY, S.DELETED_VOID_INDICATOR = O.DELETED_VOID_INDICATOR, S.DAY_SUPPLY = O.DAY_SUPPLY, S.PROFILED_RX_INDICATOR = O.PROFILED_RX_INDICATOR, S.PAT_PRIMARY_INS_CARDHOLDER_ID = O.PAT_PRIMARY_INS_CARDHOLDER_ID, S.PAT_PRIMARY_INS_EMPLOYER = O.PAT_PRIMARY_INS_EMPLOYER, S.PAT_PRIMARY_INS_GROUP_NUMBER = O.PAT_PRIMARY_INS_GROUP_NUMBER, S.RX_NUMBER = O.RX_NUMBER, S.REFILL_NUMBER = O.REFILL_NUMBER, S.REFERRAL_START_TIMESTAMP = O.REFERRAL_START_TIMESTAMP, S.STAGE_START_TIMESTAMP = O.STAGE_START_TIMESTAMP, S.STAGE_END_TIMESTAMP = O.STAGE_END_TIMESTAMP, S.WORK_STAGE_ASSIGN_STATUS = O.WORK_STAGE_ASSIGN_STATUS, S.REFERRAL_COMPLETION_TIMESTAMP = O.REFERRAL_COMPLETION_TIMESTAMP, S.ORDER_START_TIMESTAMP = O.ORDER_START_TIMESTAMP, S.PRIOR_AUTH_APPEAL_INDICATOR = O.PRIOR_AUTH_APPEAL_INDICATOR, S.BV_MISSING_INFO_INDICATOR = O.BV_MISSING_INFO_INDICATOR, S.ORDER_COMPLETION_TIMESTAMP = O.ORDER_COMPLETION_TIMESTAMP, S.SUPPLY_ITEM_INDICATOR = O.SUPPLY_ITEM_INDICATOR, S.FORWARDED_REFERRAL_INDICATOR = O.FORWARDED_REFERRAL_INDICATOR, S.NOGO_INDICATOR = O.NOGO_INDICATOR, S.CREATE_UPDATE_DATE = O.CREATE_UPDATE_DATE, S.ORDER_STATUS = O.ORDER_STATUS, S.REFERRAL_STATUS = O.REFERRAL_STATUS, S.VIRTUAL_STAGE_OUTCOME = O.VIRTUAL_STAGE_OUTCOME, S.VIRTUAL_STAGE_OUTCOME_REASON = O.VIRTUAL_STAGE_OUTCOME_REASON, S.UNIQUE_ID = O.UNIQUE_ID, S.STAGED_DATE_TIMESTAMP = O.STAGED_DATE_TIMESTAMP, S.REWORK_COUNT = O.REWORK_COUNT --When no records are matched, insert --the incoming records from Oracle Table --to our SQL environment table WHEN NOT MATCHED BY TARGET THEN INSERT ( WIP_KEY, PATIENT_KEY, PHARMACY_KEY, LINE_PHARM_KEY, DELIVERY_ADDRESS_KEY, INVENTORY_TYPE_KEY, PHYSICIAN_LOCATION_KEY, PRIMARY_ORIGINAL_INS_PLAN_KEY, PRIMARY_INSURANCE_PLAN_KEY, REFERRAL_PRIORITY_KEY, REIMBURSEMENT_STATUS_REF_KEY, REIMBURSEMENT_STATUS_STAGE_KEY, REFERRAL_SOURCE_TYPE_KEY, REFERRAL_START_DATE_KEY, STAGE_START_DATE_KEY, STAGE_END_DATE_KEY, ASSIGNED_EMPLOYEE_KEY, REFERRAL_COMPLETION_DATE_KEY, REFERRAL_CYCLE_KEY, SHIP_DATE_KEY, SHIP_MODE_KEY, PLACE_OF_SERVICE_KEY, REF_BUSINESS_DRIVERS_KEY, REF_OUTCOME_STATUS_KEY, STAGE_OUTCOME_STATUS_KEY, REF_REFERENCE_CATEGORY_KEY, THERAPY_GROUP_KEY, FORWARD_REASON_KEY, FORWARDED_TO_PHARMACY_KEY, IMAGE_RECEIPT_DATE_KEY, DATA_SOURCE_KEY, ORDER_START_DATE_KEY, DRUG_KEY, WORK_STAGE_KEY, REFERRAL_NEED_DATE_KEY, ORDER_NEED_DATE_KEY, WIP_TYPE, REFERRAL_ID, REFERRAL_LINE_NUM, ORDER_ID, ORDER_LINE_NUM, DIAGNOSIS_CODE, DIAGNOSIS_DESCRIPTION, QTY_WRITTEN, METRIC_QTY, DELETED_VOID_INDICATOR, DAY_SUPPLY, PROFILED_RX_INDICATOR, PAT_PRIMARY_INS_CARDHOLDER_ID, PAT_PRIMARY_INS_EMPLOYER, PAT_PRIMARY_INS_GROUP_NUMBER, RX_NUMBER, REFILL_NUMBER, REFERRAL_START_TIMESTAMP, STAGE_START_TIMESTAMP, STAGE_END_TIMESTAMP, WORK_STAGE_ASSIGN_STATUS, REFERRAL_COMPLETION_TIMESTAMP, ORDER_START_TIMESTAMP, PRIOR_AUTH_APPEAL_INDICATOR, BV_MISSING_INFO_INDICATOR, ORDER_COMPLETION_TIMESTAMP, SUPPLY_ITEM_INDICATOR, FORWARDED_REFERRAL_INDICATOR, NOGO_INDICATOR, CREATE_UPDATE_DATE, ORDER_STATUS, REFERRAL_STATUS, VIRTUAL_STAGE_OUTCOME, VIRTUAL_STAGE_OUTCOME_REASON, UNIQUE_ID, STAGED_DATE_TIMESTAMP, REWORK_COUNT ) VALUES ( O.WIP_KEY, O.PATIENT_KEY, O.PHARMACY_KEY, O.LINE_PHARM_KEY, O.DELIVERY_ADDRESS_KEY, O.INVENTORY_TYPE_KEY, O.PHYSICIAN_LOCATION_KEY, O.PRIMARY_ORIGINAL_INS_PLAN_KEY, O.PRIMARY_INSURANCE_PLAN_KEY, O.REFERRAL_PRIORITY_KEY, O.REIMBURSEMENT_STATUS_REF_KEY, O.REIMBURSEMENT_STATUS_STAGE_KEY, O.REFERRAL_SOURCE_TYPE_KEY, O.REFERRAL_START_DATE_KEY, O.STAGE_START_DATE_KEY, O.STAGE_END_DATE_KEY, O.ASSIGNED_EMPLOYEE_KEY, O.REFERRAL_COMPLETION_DATE_KEY, O.REFERRAL_CYCLE_KEY, O.SHIP_DATE_KEY, O.SHIP_MODE_KEY, O.PLACE_OF_SERVICE_KEY, O.REF_BUSINESS_DRIVERS_KEY, O.REF_OUTCOME_STATUS_KEY, O.STAGE_OUTCOME_STATUS_KEY, O.REF_REFERENCE_CATEGORY_KEY, O.THERAPY_GROUP_KEY, O.FORWARD_REASON_KEY, O.FORWARDED_TO_PHARMACY_KEY, O.IMAGE_RECEIPT_DATE_KEY, O.DATA_SOURCE_KEY, O.ORDER_START_DATE_KEY, O.DRUG_KEY, O.WORK_STAGE_KEY, O.REFERRAL_NEED_DATE_KEY, O.ORDER_NEED_DATE_KEY, O.WIP_TYPE, O.REFERRAL_ID, O.REFERRAL_LINE_NUM, O.ORDER_ID, O.ORDER_LINE_NUM, O.DIAGNOSIS_CODE, O.DIAGNOSIS_DESCRIPTION, O.QTY_WRITTEN, O.METRIC_QTY, O.DELETED_VOID_INDICATOR, O.DAY_SUPPLY, O.PROFILED_RX_INDICATOR, O.PAT_PRIMARY_INS_CARDHOLDER_ID, O.PAT_PRIMARY_INS_EMPLOYER, O.PAT_PRIMARY_INS_GROUP_NUMBER, O.RX_NUMBER, O.REFILL_NUMBER, O.REFERRAL_START_TIMESTAMP, O.STAGE_START_TIMESTAMP, O.STAGE_END_TIMESTAMP, O.WORK_STAGE_ASSIGN_STATUS, O.REFERRAL_COMPLETION_TIMESTAMP, O.ORDER_START_TIMESTAMP, O.PRIOR_AUTH_APPEAL_INDICATOR, O.BV_MISSING_INFO_INDICATOR, O.ORDER_COMPLETION_TIMESTAMP, O.SUPPLY_ITEM_INDICATOR, O.FORWARDED_REFERRAL_INDICATOR, O.NOGO_INDICATOR, O.CREATE_UPDATE_DATE, O.ORDER_STATUS, O.REFERRAL_STATUS, O.VIRTUAL_STAGE_OUTCOME, O.VIRTUAL_STAGE_OUTCOME_REASON, O.UNIQUE_ID, O.STAGED_DATE_TIMESTAMP, O.REWORK_COUNT ) --When there is a row that exists in our SQL table and --same record does not exist in Oracle table --then delete this record from our SQL table WHEN NOT MATCHED BY SOURCE THEN DELETE;以下是執行計劃：
我們可以為查詢配置或表結構（索引、統計資訊等）做出哪些選擇來嘗試優化這個過程？
你看過執行計劃嗎？在我看來，在本地進行合併之前，這種查詢必須通過連結伺服器從 Oracle 拉回整個表。那張桌子有多大？這可能是“緩慢”的部分。
修改合併的源部分，只帶回新的或修改過的記錄；然後執行另一條語句以僅帶回密鑰以進行刪除。它仍然必須掃描所有內容（兩次），但這意味著洗牌的數據要少得多。