主页 / dba / 问题 / 135697
Accepted
Kirk Saunders
Asked: 2016-04-19 11:00:27 +0800 CST

SQL Merge 语句花费大量时间对主键进行排序

  • 772

我们希望使用 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;

这是执行计划： 执行计划

我们可以为查询配置或表结构（索引、统计信息等）做出哪些选择来尝试优化此过程？

sql-server sql-server-2012

1 个回答

  1. Best Answer

    你看过执行计划了吗？在我看来，这种查询必须在本地进行合并之前通过链接服务器从 Oracle 中拉回整个表。那张桌子有多大？这可能是“缓慢”的部分。

    修改合并的源部分，只带回新的或修改过的记录；然后执行另一条语句以仅带回密钥以进行删除。它仍然必须扫描所有内容（两次），但这意味着洗牌的数据要少得多。

    • 2

相关问题