3

We are looking to use the SQL MERGE statement in SQL Server 2012 to handle data replication for a data warehouse that we have access to from the manufacturer of a 3rd party application we use for data entry.

The source of the data warehouse is an Oracle environment and we are using SQL Connector to reference the Oracle database in our environment as a linked server. The data is loaded/updated into the Oracle data source on in a once nightly dump.

We are looking at the MERGE statement due to unreliability/instability we have seen with transactional and snapshot replication.

Below is the table structure to do the merge on:

    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]

And this is the query we are using to complete our merge: 

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;

Here is the execution Plan: Execution Plan

What options for either query configuration or table structure (indexes, statistics, etc...) could we make on our end to try and optimize this process?

Improve this question
3
  • 1
    Have you tried loading the Oracle data into a staging table before starting to MERGE? Doing a process like this across a linked server is not usually going to yield good performance. SSIS or a bulk load option would be ideal to achieve this.
    – LowlyDBA - John M
    Apr 18, 2016 at 19:09
  • Pair up the staging table (as per John's suggestion) with the Oracle Attunity Connectors in SSIS. Superb performance on pull from Oracle in my experience.
    – wBob
    Apr 18, 2016 at 21:20
  • Currently we have not. The only two other methods we have tried is transnational and snapshot replication. I am not totally sure if we would be able to stand up an Oracle database server but we could definitely give that a try.
    – Kirk Saunders
    Apr 19, 2016 at 15:31

1 Answer 1

Reset to default
2

Have you looked at the execution plan? It seems to me that this kind of query will have to pull back the entire table from Oracle over the linked server before doing the merge locally. How big is that table? This would probably be the "slow" part.

Modify the source part of the merge to only bring back new or modified records; then do another statement to only bring back the keys in order to do the deletions. It still has to scan everything (twice) but it means shuffling far less data.

Improve this answer
6
  • We have the CREATE_UPDATE_DATE column on the table which enables us to only work with the rows that have been updated per the data provider. The table currently is about 4.4 or 4.5 million rows. This is my first time working with a MERGE function would are you suggesting an update like: MERGE FACT_WIP_2 AS S USING RXCSMAP..SMA.FACT_WIP AS O ON (S.WIP_KEY = O.WIP_KEY AND O.CREATE_UPDATE_DATE > @DateToStartLooking) WHEN MATCHED AND O.CREATE_UPDATE_DATE <> S.CREATE_UPDATE_DATE
    – Kirk Saunders
    Apr 19, 2016 at 15:34
  • Instead of: 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
    – Kirk Saunders
    Apr 19, 2016 at 15:37
  • I was thinking more along the lines of MERGE FACT_WIP_2 AS S USING (SELECT * FROM RXCSMAP..SMA.FACT_WIP WHERE O.CREATE_UPDATE_DATE > @DateToStartLooking) 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... You would have to check on the Oracle side to confirm but SQL Server is usually smart enough to send the where part to the Oracle side and so limit the amount of returned data. It cannot do the same with just the merge/join.
    – Cody Konior
    Apr 20, 2016 at 5:01
  • But it then means you need a second statement to pick up the deleted rows. This will need to return all rows but if you use USING (SELECT O.WIP_KEY FROM RXCSMAP..SMA.FACT_WIP) only those small keys have to traverse the network and not all the other associated columns. So together the two should be faster, but maybe not fast enough for your purposes.
    – Cody Konior
    Apr 20, 2016 at 5:06
  • I will have to give that a try. We just need to be able to run the operation against all ~60 tables (most of which are actually fairly small) in about a 4 hour time frame. Now we obviously want to be able to complete this a quickly as possible but I will play around with that idea. Thank you very much.
    – Kirk Saunders
    Apr 20, 2016 at 13:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.