Início / dba / Perguntas / 135697
Accepted
Kirk Saunders
Asked: 2016-04-19 11:00:27 +0800 CST

Instrução de mesclagem SQL gastando muito tempo classificando na chave primária

  • 772

Estamos procurando usar a instrução SQL MERGE no SQL Server 2012 para lidar com a replicação de dados para um data warehouse ao qual temos acesso do fabricante de um aplicativo de terceiros que usamos para entrada de dados.

A origem do data warehouse é um ambiente Oracle e estamos usando o SQL Connector para fazer referência ao banco de dados Oracle em nosso ambiente como um servidor vinculado. Os dados são carregados/atualizados na fonte de dados Oracle em um dump noturno.

Estamos analisando a instrução MERGE devido à falta de confiabilidade/instabilidade que vimos com a replicação transacional e de instantâneo.

Abaixo está a estrutura da tabela para fazer a mesclagem:

    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]

E esta é a consulta que estamos usando para concluir nossa mesclagem: 

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;

Eis o Plano de execução: Plano de execução

Que opções para configuração de consulta ou estrutura de tabela (índices, estatísticas, etc...) poderíamos fazer para tentar otimizar esse processo?

sql-server sql-server-2012

1 respostas

  1. Best Answer

    Já olhou o plano de execução? Parece-me que esse tipo de consulta terá que retirar toda a tabela do Oracle pelo servidor vinculado antes de fazer a mesclagem localmente. Qual o tamanho dessa mesa? Esta provavelmente seria a parte "lenta".

    Modifique a parte de origem da mesclagem para trazer de volta apenas registros novos ou modificados; em seguida, faça outra instrução para trazer de volta apenas as chaves para fazer as exclusões. Ele ainda precisa verificar tudo (duas vezes), mas significa embaralhar muito menos dados.

    • 2

relate perguntas