SQL Merge Statement Spending Large Amount of Time Sorting on Primary Key

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,

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.

StackExchange Database Administrators Q#135697, answer score: 2