нужна помощь в оптимизации запроса SQL Server

#sql #sql-server #query-optimization

#sql #sql-server #запрос-оптимизация

Вопрос:

Я работаю над нашей базой данных отчетов. У нас возникли большие проблемы с нашим текущим процессом сбора данных, и я пытаюсь его переработать.

Каждый час мы добавляем данные в наши таблицы из рабочей среды. Если говорить конкретно об одной таблице, то в ней чуть более миллиона строк (обычно меньше). Мне нужно захватить эти строки и добавить их в нашу таблицу сервера отчетов. В настоящее время я использую хранимую процедуру, которая использует процесс СЛИЯНИЯ (с использованием целевого метода источника). Для этого требуется около 16 минут каждый час. У кого-нибудь есть идея получше добавлять / обновлять строки? Производственная таблица, которую мы просматриваем, также содержит только СЕГОДНЯШНИЕ данные, поэтому мне даже не нужно вводить фильтр по дате, чтобы уменьшить нагрузку на данные. Какой может быть лучший способ оптимизировать производительность? Вот план выполнения:https://www.brentozar.com/pastetheplan/?id=HJrfFsTXP Ниже приведена процедура:

 CREATE PROCEDURE dbo.spINSERT_FULL_ORDERHEADER 
AS
BEGIN
WITH ORDER_DETAIL_V2 AS(
SELECT 
'DCMS' AS SOURCE,
 DC_ORDER_NUMBER,
 DCMS_ORDER_TYPE,
 CUSTOMER_PURCHASE_ORDER_ID,
 BILL_TO,
 CUSTOMER_MASTER_RECORD_TYPE,
 SHIP_TO,
 SALES_ORDER,
 MERCHANDISE_STYLE,
 MERCHANDISE_SIZE_1,
 CONCAT(SUBSTRING(MERCHANDISE_STYLE,3,6),'-',
 SUBSTRING(MERCHANDISE_STYLE,9,3)) AS MATERIAL,
 SUBSTRING(MERCHANDISE_STYLE,14,2) AS QUALITY,
 ORDER_QTY,
 ORDER_SELECTED_QTY,
 ORDER_SHIPPED_QTY,
 RELEASED_QTY,
 PICK_QTY,
 PACKED_QTY,
 RELEASED_CARTONS,
 FULL_CASE_PICK_COUNT,
 TOTE_COUNT,
 SPECIAL_PROCESS_CODE_1,
 SPECIAL_PROCESS_CODE_2,
 SPECIAL_PROCESS_CODE_3,
 ORDER_DETAIL_STATUS,
 ORDER_CARRIER,
 CARRIER_SERVICE_ID,
 CAST(CONVERT(VARCHAR, CREATE_DATE) AS DATE) AS CREATE_DATE,
 LEFT(cast(dateadd(hh,2,LEFT(RIGHT(CONCAT('000000', CREATE_TIME),6), 2) ':' SUBSTRING(RIGHT(CONCAT('000000', CREATE_TIME), 6), 3, 2) ':' RIGHT(RIGHT(CONCAT('000000', CREATE_TIME), 8), 2)) AS TIME),8)   AS CREATE_TIME,
 CAST(CONVERT(VARCHAR, ALLOCATION_DATE) AS DATE) AS ALLOCATION_DATE,
 CAST(CONVERT(VARCHAR, REQUESTED_SHIP_DATE) AS DATE) AS REQUESTED_SHIP_DATE,
 CAST(CONVERT(VARCHAR, CANCEL_DATE) AS DATE) AS CANCEL_DATE,
 CASE
    WHEN DISPATCH_DATE = 0
    THEN NULL
    ELSE CAST(CONVERT(VARCHAR, DISPATCH_DATE) AS DATE)
END AS DISPATCH_DATE,
CASE
    WHEN RELEASED_DATE = 0
    THEN NULL
    ELSE CAST(CONVERT(VARCHAR, RELEASED_DATE) AS DATE)
END AS RELEASED_DATE,
LEFT(RIGHT(CONCAT('000000', RELEASED_TIME), 6), 2) ':' SUBSTRING(RIGHT(CONCAT('000000', 
RELEASED_TIME), 6), 3, 2)  ':'  RIGHT(RIGHT(CONCAT('000000', RELEASED_TIME), 6), 2) AS RELEASED_TIME,
PRIORITY_ORDER,
PARTIAL_SHIP,
SHIPMENT_CARTONS,
SCANNED_CARTONS,
STAGED_CARTONS,
LOADED_CARTONS,
CASE
WHEN INVOICE_DATE = 0
THEN NULL
ELSE CAST(CONVERT(VARCHAR, INVOICE_DATE) AS DATE)
END AS INVOICE_DATE,
SHIPPING_LOAD_NUMBER,
STARTING_WAVE,
ENDING_WAVE,
CASE
    WHEN STAGED_DATE = 0
    THEN NULL
    ELSE CAST(CONVERT(VARCHAR, STAGED_DATE) AS DATE)
END AS STAGED_DATE,
ORDER_HDR_STATUS,
LEFT(HOST_ORDER_NUMBER, 9) AS DELIVERY_NUMBER,
CASE
    WHEN  NOT_SELECTED_DATE = 0
    THEN NULL
    ELSE CAST(CONVERT(VARCHAR, NOT_SELECTED_DATE) AS DATE)
END AS NOT_SELECTED_DATE,
LEFT(RIGHT(CONCAT('000000', NOT_SELECTED_TIME), 6), 2) ':' SUBSTRING(RIGHT(CONCAT('000000', NOT_SELECTED_TIME), 6), 3, 2) ':' RIGHT(RIGHT(CONCAT('000000', NOT_SELECTED_TIME), 6), 2) AS NOT_SELECTED_TIME,
NOT_SELECTED_REASON,
CAST(CONVERT(VARCHAR, EST_DEL_DT) AS DATE) AS EST_DEL_DATE,
CONCAT(DC_ORDER_NUMBER,MERCHANDISE_STYLE,MERCHANDISE_SIZE_1,CREATE_DATE) AS [KEY]

FROM OPENQUERY(MEMDWPR1,'
SELECT
OHORNO AS DC_ORDER_NUMBER,
OHORTY AS DCMS_ORDER_TYPE,
OHCUPO AS CUSTOMER_PURCHASE_ORDER_ID,
OHBLTO AS BILL_TO,
OHCURT AS CUSTOMER_MASTER_RECORD_TYPE,
OHSHTO AS SHIP_TO,
OHSAOR AS SALES_ORDER,
ODMRSY AS MERCHANDISE_STYLE,
ODMRS1 AS MERCHANDISE_SIZE_1,
ODORQT AS ORDER_QTY,
ODORSL AS ORDER_SELECTED_QTY,
ODORSH AS ORDER_SHIPPED_QTY,
OHRLQT AS RELEASED_QTY,
OHPKQT AS PICK_QTY,
OHPDQT AS PACKED_QTY,
OHRLCA AS RELEASED_CARTONS,
OHFCCT AS FULL_CASE_PICK_COUNT,
OHTOCT AS TOTE_COUNT,
ODSP01 AS SPECIAL_PROCESS_CODE_1,
ODSP02 AS SPECIAL_PROCESS_CODE_2,
ODSP03 AS SPECIAL_PROCESS_CODE_3,
ODODST AS ORDER_DETAIL_STATUS,
OHORCR AS ORDER_CARRIER,
OHCRSV AS CARRIER_SERVICE_ID,
OHCRDT AS CREATE_DATE,
OHCRTM AS CREATE_TIME,
OHALDT AS ALLOCATION_DATE,
OHRQDT AS REQUESTED_SHIP_DATE,
OHCNDT AS CANCEL_DATE,
OHDIDT AS DISPATCH_DATE,
OHRLDT AS RELEASED_DATE,
OHRLTM AS RELEASED_TIME,
OHORPY AS PRIORITY_ORDER,
OHPLSH AS PARTIAL_SHIP,
OHSHCA AS SHIPMENT_CARTONS,
OHSNCA AS SCANNED_CARTONS,
OHSGCA AS STAGED_CARTONS,
OHLDCA AS LOADED_CARTONS,
OHIVDT AS INVOICE_DATE,
OHLDNO AS SHIPPING_LOAD_NUMBER,
OHWVST AS STARTING_WAVE,
OHWVED AS ENDING_WAVE,
OHSGDT AS STAGED_DATE,
OHOHST AS ORDER_HDR_STATUS,
OHHSOR AS HOST_ORDER_NUMBER,
OHSLDT AS NOT_SELECTED_DATE,
OHSLTM AS NOT_SELECTED_TIME,
OHSLRS AS NOT_SELECTED_REASON,
OHEXDD AS EST_DEL_DT
FROM DSV052BDTA.OHORDR00@MEMWMS2.WORLD
JOIN DSV052BDTA.ODORDR00@MEMWMS2.WORLD 
ON ODORNO = OHORNO
')
GROUP BY
DC_ORDER_NUMBER,
DCMS_ORDER_TYPE,
CUSTOMER_PURCHASE_ORDER_ID,
BILL_TO,
CUSTOMER_MASTER_RECORD_TYPE,
SHIP_TO,
SALES_ORDER,
MERCHANDISE_STYLE,
MERCHANDISE_SIZE_1,
CONCAT(SUBSTRING(MERCHANDISE_STYLE,3,6),'-',
SUBSTRING(MERCHANDISE_STYLE,9,3)),
SUBSTRING(MERCHANDISE_STYLE,14,2),
ORDER_QTY,
ORDER_SELECTED_QTY,
ORDER_SHIPPED_QTY,
RELEASED_QTY,
PICK_QTY,
PACKED_QTY,
RELEASED_CARTONS,
FULL_CASE_PICK_COUNT,
TOTE_COUNT,
SPECIAL_PROCESS_CODE_1,
SPECIAL_PROCESS_CODE_2,
SPECIAL_PROCESS_CODE_3,
ORDER_DETAIL_STATUS,
ORDER_CARRIER,
CARRIER_SERVICE_ID,
 CAST(CONVERT(VARCHAR, CREATE_DATE) AS DATE),
LEFT(cast(dateadd(hh,2,LEFT(RIGHT(CONCAT('000000', CREATE_TIME),6), 2) ':' SUBSTRING(RIGHT(CONCAT('000000', CREATE_TIME), 6), 3, 2) ':' RIGHT(RIGHT(CONCAT('000000', CREATE_TIME), 8), 2)) AS TIME),8),
CAST(CONVERT(VARCHAR, ALLOCATION_DATE) AS DATE) ,
CAST(CONVERT(VARCHAR, REQUESTED_SHIP_DATE) AS DATE) ,
CAST(CONVERT(VARCHAR, CANCEL_DATE) AS DATE) ,
CASE
    WHEN DISPATCH_DATE = 0
    THEN NULL
    ELSE CAST(CONVERT(VARCHAR, DISPATCH_DATE) AS DATE)
END,
CASE
    WHEN RELEASED_DATE = 0
    THEN NULL
    ELSE CAST(CONVERT(VARCHAR, RELEASED_DATE) AS DATE)
END,
LEFT(RIGHT(CONCAT('000000', RELEASED_TIME), 6), 2) ':' SUBSTRING(RIGHT(CONCAT('000000', 

RELEASED_TIME), 6), 3, 2)  ':'  RIGHT(RIGHT(CONCAT('000000', RELEASED_TIME), 6), 2) ,
PRIORITY_ORDER,
PARTIAL_SHIP,
SHIPMENT_CARTONS,
SCANNED_CARTONS,
STAGED_CARTONS,
LOADED_CARTONS,
CASE
WHEN INVOICE_DATE = 0
THEN NULL
ELSE CAST(CONVERT(VARCHAR, INVOICE_DATE) AS DATE)
END,
SHIPPING_LOAD_NUMBER,
STARTING_WAVE,
ENDING_WAVE,
CASE
    WHEN STAGED_DATE = 0
    THEN NULL
    ELSE CAST(CONVERT(VARCHAR, STAGED_DATE) AS DATE)
END,
ORDER_HDR_STATUS,
LEFT(HOST_ORDER_NUMBER, 9),
CASE
    WHEN  NOT_SELECTED_DATE = 0
    THEN NULL
    ELSE CAST(CONVERT(VARCHAR, NOT_SELECTED_DATE) AS DATE)
END,
LEFT(RIGHT(CONCAT('000000', NOT_SELECTED_TIME), 6), 2) ':' SUBSTRING(RIGHT(CONCAT('000000', NOT_SELECTED_TIME), 6), 3, 2) ':' RIGHT(RIGHT(CONCAT('000000', NOT_SELECTED_TIME), 6), 2),
NOT_SELECTED_REASON,
CAST(CONVERT(VARCHAR, EST_DEL_DT) AS DATE),
CONCAT(DC_ORDER_NUMBER,MERCHANDISE_STYLE,MERCHANDISE_SIZE_1,CREATE_DATE)
)

MERGE BUSINESSPLANNING_TEST.dbo.[FullOrderHeader] AS TARGET
USING ORDER_DETAIL_V2 AS SOURCE
ON TARGET.[KEY] = SOURCE.[KEY]

WHEN MATCHED THEN
UPDATE SET 

TARGET.[SOURCE] = SOURCE.[SOURCE],
TARGET.DC_ORDER_NUMBER = SOURCE.DC_ORDER_NUMBER,
TARGET.DCMS_ORDER_TYPE = SOURCE.DCMS_ORDER_TYPE,
TARGET.CUSTOMER_PURCHASE_ORDER_ID = SOURCE.CUSTOMER_PURCHASE_ORDER_ID,
TARGET.BILL_TO = SOURCE.BILL_TO,
TARGET.CUSTOMER_MASTER_RECORD_TYPE = SOURCE.CUSTOMER_MASTER_RECORD_TYPE,
TARGET.SHIP_TO = SOURCE.SHIP_TO,
TARGET.SALES_ORDER = SOURCE.SALES_ORDER,
TARGET.MERCHANDISE_STYLE = SOURCE.MERCHANDISE_STYLE,
TARGET.MERCHANDISE_SIZE_1 = SOURCE.MERCHANDISE_SIZE_1,
TARGET.MATERIAL = SOURCE.MATERIAL,
TARGET.QUALITY = SOURCE.QUALITY,
TARGET.ORDER_QTY = SOURCE.ORDER_QTY,
TARGET.ORDER_SELECTED_QTY = SOURCE.ORDER_SELECTED_QTY,
TARGET.ORDER_SHIPPED_QTY = SOURCE.ORDER_SHIPPED_QTY,
TARGET.RELEASED_QTY = SOURCE.RELEASED_QTY,
TARGET.PICK_QTY = SOURCE.PICK_QTY,
TARGET.PACKED_QTY = SOURCE.PACKED_QTY,
TARGET.RELEASED_CARTONS = SOURCE.RELEASED_CARTONS,
TARGET.FULL_CASE_PICK_COUNT = SOURCE.FULL_CASE_PICK_COUNT,
TARGET.TOTE_COUNT = SOURCE.TOTE_COUNT,
TARGET.SPECIAL_PROCESS_CODE_1 = SOURCE.SPECIAL_PROCESS_CODE_1,
TARGET.SPECIAL_PROCESS_CODE_2 = SOURCE.SPECIAL_PROCESS_CODE_2,
TARGET.SPECIAL_PROCESS_CODE_3 = SOURCE.SPECIAL_PROCESS_CODE_3,
TARGET.ORDER_DETAIL_STATUS = SOURCE.ORDER_DETAIL_STATUS,
TARGET.ORDER_CARRIER = SOURCE.ORDER_CARRIER,
TARGET.CARRIER_SERVICE_ID = SOURCE.CARRIER_SERVICE_ID,
TARGET.CREATE_DATE = SOURCE.CREATE_DATE,
TARGET.CREATE_TIME = SOURCE.CREATE_TIME,
TARGET.ALLOCATION_DATE = SOURCE.ALLOCATION_DATE,
TARGET.REQUESTED_SHIP_DATE = SOURCE.REQUESTED_SHIP_DATE,
TARGET.CANCEL_DATE = SOURCE.CANCEL_DATE,
TARGET.DISPATCH_DATE = SOURCE.DISPATCH_DATE,
TARGET.RELEASED_DATE = SOURCE.RELEASED_DATE,
TARGET.RELEASED_TIME = SOURCE.RELEASED_TIME,
TARGET.PRIORITY_ORDER = SOURCE.PRIORITY_ORDER,
TARGET.PARTIAL_SHIP = SOURCE.PARTIAL_SHIP,
TARGET.SHIPMENT_CARTONS = SOURCE.SHIPMENT_CARTONS,
TARGET.SCANNED_CARTONS = SOURCE.SCANNED_CARTONS,
TARGET.STAGED_CARTONS = SOURCE.STAGED_CARTONS,
TARGET.LOADED_CARTONS = SOURCE.LOADED_CARTONS,
TARGET.INVOICE_DATE = SOURCE.INVOICE_DATE,
TARGET.SHIPPING_LOAD_NUMBER = SOURCE.SHIPPING_LOAD_NUMBER,
TARGET.STARTING_WAVE = SOURCE.STARTING_WAVE,
TARGET.ENDING_WAVE = SOURCE.ENDING_WAVE,
TARGET.STAGED_DATE = SOURCE.STAGED_DATE,
TARGET.ORDER_HDR_STATUS = SOURCE.ORDER_HDR_STATUS,
TARGET.DELIVERY_NUMBER = SOURCE.DELIVERY_NUMBER,
TARGET.NOT_SELECTED_DATE = SOURCE.NOT_SELECTED_DATE,
TARGET.NOT_SELECTED_TIME = SOURCE.NOT_SELECTED_TIME,
TARGET.NOT_SELECTED_REASON = SOURCE.NOT_SELECTED_REASON,
TARGET.EST_DEL_DATE = SOURCE.EST_DEL_DATE,
TARGET.LAST_UPDATED = GETDATE(),
TARGET.[KEY] = SOURCE.[KEY]

WHEN NOT MATCHED BY TARGET
THEN INSERT
(
[SOURCE],
DC_ORDER_NUMBER,
DCMS_ORDER_TYPE,
CUSTOMER_PURCHASE_ORDER_ID,
BILL_TO,
CUSTOMER_MASTER_RECORD_TYPE,
SHIP_TO,
SALES_ORDER,
MERCHANDISE_STYLE,
MERCHANDISE_SIZE_1,
MATERIAL,
QUALITY,
ORDER_QTY,
ORDER_SELECTED_QTY,
ORDER_SHIPPED_QTY,
RELEASED_QTY,
PICK_QTY,
PACKED_QTY,
RELEASED_CARTONS,
FULL_CASE_PICK_COUNT,
TOTE_COUNT,
SPECIAL_PROCESS_CODE_1,
SPECIAL_PROCESS_CODE_2,
SPECIAL_PROCESS_CODE_3,
ORDER_DETAIL_STATUS,
ORDER_CARRIER,
CARRIER_SERVICE_ID,
CREATE_DATE,
CREATE_TIME,
ALLOCATION_DATE,
REQUESTED_SHIP_DATE,
CANCEL_DATE,
DISPATCH_DATE,
RELEASED_DATE,
RELEASED_TIME,
PRIORITY_ORDER,
PARTIAL_SHIP,
SHIPMENT_CARTONS,
SCANNED_CARTONS,
STAGED_CARTONS,
LOADED_CARTONS,
INVOICE_DATE,
SHIPPING_LOAD_NUMBER,
STARTING_WAVE,
ENDING_WAVE,
STAGED_DATE,
ORDER_HDR_STATUS,
DELIVERY_NUMBER,
NOT_SELECTED_DATE,
NOT_SELECTED_TIME,
NOT_SELECTED_REASON,
EST_DEL_DATE,
LAST_UPDATED,
[KEY]
)
VALUES
(
SOURCE.SOURCE,
SOURCE.DC_ORDER_NUMBER,
SOURCE.DCMS_ORDER_TYPE,
SOURCE.CUSTOMER_PURCHASE_ORDER_ID,
SOURCE.BILL_TO,
SOURCE.CUSTOMER_MASTER_RECORD_TYPE,
SOURCE.SHIP_TO,
SOURCE.SALES_ORDER,
SOURCE.MERCHANDISE_STYLE,
SOURCE.MERCHANDISE_SIZE_1,
SOURCE.MATERIAL,
SOURCE.QUALITY,
SOURCE.ORDER_QTY,
SOURCE.ORDER_SELECTED_QTY,
SOURCE.ORDER_SHIPPED_QTY,
SOURCE.RELEASED_QTY,
SOURCE.PICK_QTY,
SOURCE.PACKED_QTY,
SOURCE.RELEASED_CARTONS,
SOURCE.FULL_CASE_PICK_COUNT,
SOURCE.TOTE_COUNT,
SOURCE.SPECIAL_PROCESS_CODE_1,
SOURCE.SPECIAL_PROCESS_CODE_2,
SOURCE.SPECIAL_PROCESS_CODE_3,
SOURCE.ORDER_DETAIL_STATUS,
SOURCE.ORDER_CARRIER,
SOURCE.CARRIER_SERVICE_ID,
SOURCE.CREATE_DATE,
SOURCE.CREATE_TIME,
SOURCE.ALLOCATION_DATE,
SOURCE.REQUESTED_SHIP_DATE,
SOURCE.CANCEL_DATE,
SOURCE.DISPATCH_DATE,
SOURCE.RELEASED_DATE,
SOURCE.RELEASED_TIME,
SOURCE.PRIORITY_ORDER,
SOURCE.PARTIAL_SHIP,
SOURCE.SHIPMENT_CARTONS,
SOURCE.SCANNED_CARTONS,
SOURCE.STAGED_CARTONS,
SOURCE.LOADED_CARTONS,
SOURCE.INVOICE_DATE,
SOURCE.SHIPPING_LOAD_NUMBER,
SOURCE.STARTING_WAVE,
SOURCE.ENDING_WAVE,
SOURCE.STAGED_DATE,
SOURCE.ORDER_HDR_STATUS,
SOURCE.DELIVERY_NUMBER,
SOURCE.NOT_SELECTED_DATE,
SOURCE.NOT_SELECTED_TIME,
SOURCE.NOT_SELECTED_REASON,
SOURCE.EST_DEL_DATE,
GETDATE(),
SOURCE.[KEY]
);
END

1. Вот ссылка: brentozar.com/pastetheplan

2. Хорошо, я добавил ссылку на план выполнения. Спасибо за это!

3. 71% затрат приходится на сканирование таблицы FullOrderHeader, что говорит о том, что индекс в порядке, если только вашему запросу не нужны все из них.

4. Вам следует попробовать скопировать удаленную таблицу локально в базу данных tempdb вместо использования cte. Я подозреваю, что ваша основная таблица также может использовать columnstore

5. @DaleK спасибо за вашу идею. Я уменьшил количество столбцов, которые были в инструкции UPDATE, и понял, что по крайней мере половина из них не обновляется, поэтому я их удалил. Сократил время запроса на 5 минут!! Большая разница. Спасибо за эту идею

Ответ №1:

Прежде всего, извините, что не использовал комментарий, но моя репутация недостаточно высока, поэтому я собираюсь ответить таким образом.

Ваш запрос довольно прост с точки зрения задействованных таблиц, поэтому пробовать особо нечего (возможно, нестандартное мышление, но сейчас я понятия не имею).

Согласно плану, он показывает, что он использует хэш-соединение, а предполагаемое количество строк для цели и источника составляет 1 М и 10 Тыс. соответственно (мне не ясно из вашего описания). Также показано, что большая часть расчетных затрат связана со сканированием в целевой таблице и хэшем (71 15 %). Итак, в зависимости от количества строк в цели и источнике и от того, есть ли у вас индекс в dbo.Полный заголовок.Ключ, возможно, принудительное объединение в цикл может работать лучше (при условии, что оценки точны, так и должно быть). Чем больше строк в исходном коде, тем менее удобным будет цикл.

Вы можете сделать это, добавив OPTION (LOOP JOIN) в конце MERGE инструкции перед ; .

Если объем исходного

 CREATE PROCEDURE dbo.spINSERT_FULL_ORDERHEADER
AS
BEGIN
    WITH ORDER_DETAIL_V2
    AS (
        SELECT 'DCMS' AS SOURCE
            ,DC_ORDER_NUMBER
            ,DCMS_ORDER_TYPE
            ,CUSTOMER_PURCHASE_ORDER_ID
            ,BILL_TO
            ,CUSTOMER_MASTER_RECORD_TYPE
            ,SHIP_TO
            ,SALES_ORDER
            ,MERCHANDISE_STYLE
            ,MERCHANDISE_SIZE_1
            ,CONCAT (
                SUBSTRING(MERCHANDISE_STYLE, 3, 6)
                ,'-'
                ,SUBSTRING(MERCHANDISE_STYLE, 9, 3)
                ) AS MATERIAL
            ,SUBSTRING(MERCHANDISE_STYLE, 14, 2) AS QUALITY
            ,ORDER_QTY
            ,ORDER_SELECTED_QTY
            ,ORDER_SHIPPED_QTY
            ,RELEASED_QTY
            ,PICK_QTY
            ,PACKED_QTY
            ,RELEASED_CARTONS
            ,FULL_CASE_PICK_COUNT
            ,TOTE_COUNT
            ,SPECIAL_PROCESS_CODE_1
            ,SPECIAL_PROCESS_CODE_2
            ,SPECIAL_PROCESS_CODE_3
            ,ORDER_DETAIL_STATUS
            ,ORDER_CARRIER
            ,CARRIER_SERVICE_ID
            ,CAST(CONVERT(VARCHAR, CREATE_DATE) AS DATE) AS CREATE_DATE
            ,LEFT(cast(dateadd(hh, 2, LEFT(RIGHT(CONCAT (
                                    '000000'
                                    ,CREATE_TIME
                                    ), 6), 2)   ':'   SUBSTRING(RIGHT(CONCAT (
                                    '000000'
                                    ,CREATE_TIME
                                    ), 6), 3, 2)   ':'   RIGHT(RIGHT(CONCAT (
                                    '000000'
                                    ,CREATE_TIME
                                    ), 8), 2)) AS TIME), 8) AS CREATE_TIME
            ,CAST(CONVERT(VARCHAR, ALLOCATION_DATE) AS DATE) AS ALLOCATION_DATE
            ,CAST(CONVERT(VARCHAR, REQUESTED_SHIP_DATE) AS DATE) AS REQUESTED_SHIP_DATE
            ,CAST(CONVERT(VARCHAR, CANCEL_DATE) AS DATE) AS CANCEL_DATE
            ,CASE 
                WHEN DISPATCH_DATE = 0
                    THEN NULL
                ELSE CAST(CONVERT(VARCHAR, DISPATCH_DATE) AS DATE)
                END AS DISPATCH_DATE
            ,CASE 
                WHEN RELEASED_DATE = 0
                    THEN NULL
                ELSE CAST(CONVERT(VARCHAR, RELEASED_DATE) AS DATE)
                END AS RELEASED_DATE
            ,LEFT(RIGHT(CONCAT (
                        '000000'
                        ,RELEASED_TIME
                        ), 6), 2)   ':'   SUBSTRING(RIGHT(CONCAT (
                        '000000'
                        ,RELEASED_TIME
                        ), 6), 3, 2)   ':'   RIGHT(RIGHT(CONCAT (
                        '000000'
                        ,RELEASED_TIME
                        ), 6), 2) AS RELEASED_TIME
            ,PRIORITY_ORDER
            ,PARTIAL_SHIP
            ,SHIPMENT_CARTONS
            ,SCANNED_CARTONS
            ,STAGED_CARTONS
            ,LOADED_CARTONS
            ,CASE 
                WHEN INVOICE_DATE = 0
                    THEN NULL
                ELSE CAST(CONVERT(VARCHAR, INVOICE_DATE) AS DATE)
                END AS INVOICE_DATE
            ,SHIPPING_LOAD_NUMBER
            ,STARTING_WAVE
            ,ENDING_WAVE
            ,CASE 
                WHEN STAGED_DATE = 0
                    THEN NULL
                ELSE CAST(CONVERT(VARCHAR, STAGED_DATE) AS DATE)
                END AS STAGED_DATE
            ,ORDER_HDR_STATUS
            ,LEFT(HOST_ORDER_NUMBER, 9) AS DELIVERY_NUMBER
            ,CASE 
                WHEN NOT_SELECTED_DATE = 0
                    THEN NULL
                ELSE CAST(CONVERT(VARCHAR, NOT_SELECTED_DATE) AS DATE)
                END AS NOT_SELECTED_DATE
            ,LEFT(RIGHT(CONCAT (
                        '000000'
                        ,NOT_SELECTED_TIME
                        ), 6), 2)   ':'   SUBSTRING(RIGHT(CONCAT (
                        '000000'
                        ,NOT_SELECTED_TIME
                        ), 6), 3, 2)   ':'   RIGHT(RIGHT(CONCAT (
                        '000000'
                        ,NOT_SELECTED_TIME
                        ), 6), 2) AS NOT_SELECTED_TIME
            ,NOT_SELECTED_REASON
            ,CAST(CONVERT(VARCHAR, EST_DEL_DT) AS DATE) AS EST_DEL_DATE
            ,CONCAT (
                DC_ORDER_NUMBER
                ,MERCHANDISE_STYLE
                ,MERCHANDISE_SIZE_1
                ,CREATE_DATE
                ) AS [KEY]
        FROM OPENQUERY(MEMDWPR1, 
                '
SELECT
OHORNO AS DC_ORDER_NUMBER,
OHORTY AS DCMS_ORDER_TYPE,
OHCUPO AS CUSTOMER_PURCHASE_ORDER_ID,
OHBLTO AS BILL_TO,
OHCURT AS CUSTOMER_MASTER_RECORD_TYPE,
OHSHTO AS SHIP_TO,
OHSAOR AS SALES_ORDER,
ODMRSY AS MERCHANDISE_STYLE,
ODMRS1 AS MERCHANDISE_SIZE_1,
ODORQT AS ORDER_QTY,
ODORSL AS ORDER_SELECTED_QTY,
ODORSH AS ORDER_SHIPPED_QTY,
OHRLQT AS RELEASED_QTY,
OHPKQT AS PICK_QTY,
OHPDQT AS PACKED_QTY,
OHRLCA AS RELEASED_CARTONS,
OHFCCT AS FULL_CASE_PICK_COUNT,
OHTOCT AS TOTE_COUNT,
ODSP01 AS SPECIAL_PROCESS_CODE_1,
ODSP02 AS SPECIAL_PROCESS_CODE_2,
ODSP03 AS SPECIAL_PROCESS_CODE_3,
ODODST AS ORDER_DETAIL_STATUS,
OHORCR AS ORDER_CARRIER,
OHCRSV AS CARRIER_SERVICE_ID,
OHCRDT AS CREATE_DATE,
OHCRTM AS CREATE_TIME,
OHALDT AS ALLOCATION_DATE,
OHRQDT AS REQUESTED_SHIP_DATE,
OHCNDT AS CANCEL_DATE,
OHDIDT AS DISPATCH_DATE,
OHRLDT AS RELEASED_DATE,
OHRLTM AS RELEASED_TIME,
OHORPY AS PRIORITY_ORDER,
OHPLSH AS PARTIAL_SHIP,
OHSHCA AS SHIPMENT_CARTONS,
OHSNCA AS SCANNED_CARTONS,
OHSGCA AS STAGED_CARTONS,
OHLDCA AS LOADED_CARTONS,
OHIVDT AS INVOICE_DATE,
OHLDNO AS SHIPPING_LOAD_NUMBER,
OHWVST AS STARTING_WAVE,
OHWVED AS ENDING_WAVE,
OHSGDT AS STAGED_DATE,
OHOHST AS ORDER_HDR_STATUS,
OHHSOR AS HOST_ORDER_NUMBER,
OHSLDT AS NOT_SELECTED_DATE,
OHSLTM AS NOT_SELECTED_TIME,
OHSLRS AS NOT_SELECTED_REASON,
OHEXDD AS EST_DEL_DT
FROM DSV052BDTA.OHORDR00@MEMWMS2.WORLD
JOIN DSV052BDTA.ODORDR00@MEMWMS2.WORLD 
ON ODORNO = OHORNO
'
            )
        GROUP BY DC_ORDER_NUMBER
            ,DCMS_ORDER_TYPE
            ,CUSTOMER_PURCHASE_ORDER_ID
            ,BILL_TO
            ,CUSTOMER_MASTER_RECORD_TYPE
            ,SHIP_TO
            ,SALES_ORDER
            ,MERCHANDISE_STYLE
            ,MERCHANDISE_SIZE_1
            ,CONCAT (
                SUBSTRING(MERCHANDISE_STYLE, 3, 6)
                ,'-'
                ,SUBSTRING(MERCHANDISE_STYLE, 9, 3)
                )
            ,SUBSTRING(MERCHANDISE_STYLE, 14, 2)
            ,ORDER_QTY
            ,ORDER_SELECTED_QTY
            ,ORDER_SHIPPED_QTY
            ,RELEASED_QTY
            ,PICK_QTY
            ,PACKED_QTY
            ,RELEASED_CARTONS
            ,FULL_CASE_PICK_COUNT
            ,TOTE_COUNT
            ,SPECIAL_PROCESS_CODE_1
            ,SPECIAL_PROCESS_CODE_2
            ,SPECIAL_PROCESS_CODE_3
            ,ORDER_DETAIL_STATUS
            ,ORDER_CARRIER
            ,CARRIER_SERVICE_ID
            ,CAST(CONVERT(VARCHAR, CREATE_DATE) AS DATE)
            ,LEFT(cast(dateadd(hh, 2, LEFT(RIGHT(CONCAT (
                                    '000000'
                                    ,CREATE_TIME
                                    ), 6), 2)   ':'   SUBSTRING(RIGHT(CONCAT (
                                    '000000'
                                    ,CREATE_TIME
                                    ), 6), 3, 2)   ':'   RIGHT(RIGHT(CONCAT (
                                    '000000'
                                    ,CREATE_TIME
                                    ), 8), 2)) AS TIME), 8)
            ,CAST(CONVERT(VARCHAR, ALLOCATION_DATE) AS DATE)
            ,CAST(CONVERT(VARCHAR, REQUESTED_SHIP_DATE) AS DATE)
            ,CAST(CONVERT(VARCHAR, CANCEL_DATE) AS DATE)
            ,CASE 
                WHEN DISPATCH_DATE = 0
                    THEN NULL
                ELSE CAST(CONVERT(VARCHAR, DISPATCH_DATE) AS DATE)
                END
            ,CASE 
                WHEN RELEASED_DATE = 0
                    THEN NULL
                ELSE CAST(CONVERT(VARCHAR, RELEASED_DATE) AS DATE)
                END
            ,LEFT(RIGHT(CONCAT (
                        '000000'
                        ,RELEASED_TIME
                        ), 6), 2)   ':'   SUBSTRING(RIGHT(CONCAT (
                        '000000'
                        ,RELEASED_TIME
                        ), 6), 3, 2)   ':'   RIGHT(RIGHT(CONCAT (
                        '000000'
                        ,RELEASED_TIME
                        ), 6), 2)
            ,PRIORITY_ORDER
            ,PARTIAL_SHIP
            ,SHIPMENT_CARTONS
            ,SCANNED_CARTONS
            ,STAGED_CARTONS
            ,LOADED_CARTONS
            ,CASE 
                WHEN INVOICE_DATE = 0
                    THEN NULL
                ELSE CAST(CONVERT(VARCHAR, INVOICE_DATE) AS DATE)
                END
            ,SHIPPING_LOAD_NUMBER
            ,STARTING_WAVE
            ,ENDING_WAVE
            ,CASE 
                WHEN STAGED_DATE = 0
                    THEN NULL
                ELSE CAST(CONVERT(VARCHAR, STAGED_DATE) AS DATE)
                END
            ,ORDER_HDR_STATUS
            ,LEFT(HOST_ORDER_NUMBER, 9)
            ,CASE 
                WHEN NOT_SELECTED_DATE = 0
                    THEN NULL
                ELSE CAST(CONVERT(VARCHAR, NOT_SELECTED_DATE) AS DATE)
                END
            ,LEFT(RIGHT(CONCAT (
                        '000000'
                        ,NOT_SELECTED_TIME
                        ), 6), 2)   ':'   SUBSTRING(RIGHT(CONCAT (
                        '000000'
                        ,NOT_SELECTED_TIME
                        ), 6), 3, 2)   ':'   RIGHT(RIGHT(CONCAT (
                        '000000'
                        ,NOT_SELECTED_TIME
                        ), 6), 2)
            ,NOT_SELECTED_REASON
            ,CAST(CONVERT(VARCHAR, EST_DEL_DT) AS DATE)
            ,CONCAT (
                DC_ORDER_NUMBER
                ,MERCHANDISE_STYLE
                ,MERCHANDISE_SIZE_1
                ,CREATE_DATE
                )
        )
    MERGE BUSINESSPLANNING_TEST.dbo.[FullOrderHeader] AS TARGET
    USING ORDER_DETAIL_V2 AS SOURCE
        ON TARGET.[KEY] = SOURCE.[KEY]
    WHEN MATCHED
        THEN
            UPDATE
            SET TARGET.[SOURCE] = SOURCE.[SOURCE]
                ,TARGET.DC_ORDER_NUMBER = SOURCE.DC_ORDER_NUMBER
                ,TARGET.DCMS_ORDER_TYPE = SOURCE.DCMS_ORDER_TYPE
                ,TARGET.CUSTOMER_PURCHASE_ORDER_ID = SOURCE.CUSTOMER_PURCHASE_ORDER_ID
                ,TARGET.BILL_TO = SOURCE.BILL_TO
                ,TARGET.CUSTOMER_MASTER_RECORD_TYPE = SOURCE.CUSTOMER_MASTER_RECORD_TYPE
                ,TARGET.SHIP_TO = SOURCE.SHIP_TO
                ,TARGET.SALES_ORDER = SOURCE.SALES_ORDER
                ,TARGET.MERCHANDISE_STYLE = SOURCE.MERCHANDISE_STYLE
                ,TARGET.MERCHANDISE_SIZE_1 = SOURCE.MERCHANDISE_SIZE_1
                ,TARGET.MATERIAL = SOURCE.MATERIAL
                ,TARGET.QUALITY = SOURCE.QUALITY
                ,TARGET.ORDER_QTY = SOURCE.ORDER_QTY
                ,TARGET.ORDER_SELECTED_QTY = SOURCE.ORDER_SELECTED_QTY
                ,TARGET.ORDER_SHIPPED_QTY = SOURCE.ORDER_SHIPPED_QTY
                ,TARGET.RELEASED_QTY = SOURCE.RELEASED_QTY
                ,TARGET.PICK_QTY = SOURCE.PICK_QTY
                ,TARGET.PACKED_QTY = SOURCE.PACKED_QTY
                ,TARGET.RELEASED_CARTONS = SOURCE.RELEASED_CARTONS
                ,TARGET.FULL_CASE_PICK_COUNT = SOURCE.FULL_CASE_PICK_COUNT
                ,TARGET.TOTE_COUNT = SOURCE.TOTE_COUNT
                ,TARGET.SPECIAL_PROCESS_CODE_1 = SOURCE.SPECIAL_PROCESS_CODE_1
                ,TARGET.SPECIAL_PROCESS_CODE_2 = SOURCE.SPECIAL_PROCESS_CODE_2
                ,TARGET.SPECIAL_PROCESS_CODE_3 = SOURCE.SPECIAL_PROCESS_CODE_3
                ,TARGET.ORDER_DETAIL_STATUS = SOURCE.ORDER_DETAIL_STATUS
                ,TARGET.ORDER_CARRIER = SOURCE.ORDER_CARRIER
                ,TARGET.CARRIER_SERVICE_ID = SOURCE.CARRIER_SERVICE_ID
                ,TARGET.CREATE_DATE = SOURCE.CREATE_DATE
                ,TARGET.CREATE_TIME = SOURCE.CREATE_TIME
                ,TARGET.ALLOCATION_DATE = SOURCE.ALLOCATION_DATE
                ,TARGET.REQUESTED_SHIP_DATE = SOURCE.REQUESTED_SHIP_DATE
                ,TARGET.CANCEL_DATE = SOURCE.CANCEL_DATE
                ,TARGET.DISPATCH_DATE = SOURCE.DISPATCH_DATE
                ,TARGET.RELEASED_DATE = SOURCE.RELEASED_DATE
                ,TARGET.RELEASED_TIME = SOURCE.RELEASED_TIME
                ,TARGET.PRIORITY_ORDER = SOURCE.PRIORITY_ORDER
                ,TARGET.PARTIAL_SHIP = SOURCE.PARTIAL_SHIP
                ,TARGET.SHIPMENT_CARTONS = SOURCE.SHIPMENT_CARTONS
                ,TARGET.SCANNED_CARTONS = SOURCE.SCANNED_CARTONS
                ,TARGET.STAGED_CARTONS = SOURCE.STAGED_CARTONS
                ,TARGET.LOADED_CARTONS = SOURCE.LOADED_CARTONS
                ,TARGET.INVOICE_DATE = SOURCE.INVOICE_DATE
                ,TARGET.SHIPPING_LOAD_NUMBER = SOURCE.SHIPPING_LOAD_NUMBER
                ,TARGET.STARTING_WAVE = SOURCE.STARTING_WAVE
                ,TARGET.ENDING_WAVE = SOURCE.ENDING_WAVE
                ,TARGET.STAGED_DATE = SOURCE.STAGED_DATE
                ,TARGET.ORDER_HDR_STATUS = SOURCE.ORDER_HDR_STATUS
                ,TARGET.DELIVERY_NUMBER = SOURCE.DELIVERY_NUMBER
                ,TARGET.NOT_SELECTED_DATE = SOURCE.NOT_SELECTED_DATE
                ,TARGET.NOT_SELECTED_TIME = SOURCE.NOT_SELECTED_TIME
                ,TARGET.NOT_SELECTED_REASON = SOURCE.NOT_SELECTED_REASON
                ,TARGET.EST_DEL_DATE = SOURCE.EST_DEL_DATE
                ,TARGET.LAST_UPDATED = GETDATE()
                ,TARGET.[KEY] = SOURCE.[KEY]
    WHEN NOT MATCHED BY TARGET
        THEN
            INSERT (
                [SOURCE]
                ,DC_ORDER_NUMBER
                ,DCMS_ORDER_TYPE
                ,CUSTOMER_PURCHASE_ORDER_ID
                ,BILL_TO
                ,CUSTOMER_MASTER_RECORD_TYPE
                ,SHIP_TO
                ,SALES_ORDER
                ,MERCHANDISE_STYLE
                ,MERCHANDISE_SIZE_1
                ,MATERIAL
                ,QUALITY
                ,ORDER_QTY
                ,ORDER_SELECTED_QTY
                ,ORDER_SHIPPED_QTY
                ,RELEASED_QTY
                ,PICK_QTY
                ,PACKED_QTY
                ,RELEASED_CARTONS
                ,FULL_CASE_PICK_COUNT
                ,TOTE_COUNT
                ,SPECIAL_PROCESS_CODE_1
                ,SPECIAL_PROCESS_CODE_2
                ,SPECIAL_PROCESS_CODE_3
                ,ORDER_DETAIL_STATUS
                ,ORDER_CARRIER
                ,CARRIER_SERVICE_ID
                ,CREATE_DATE
                ,CREATE_TIME
                ,ALLOCATION_DATE
                ,REQUESTED_SHIP_DATE
                ,CANCEL_DATE
                ,DISPATCH_DATE
                ,RELEASED_DATE
                ,RELEASED_TIME
                ,PRIORITY_ORDER
                ,PARTIAL_SHIP
                ,SHIPMENT_CARTONS
                ,SCANNED_CARTONS
                ,STAGED_CARTONS
                ,LOADED_CARTONS
                ,INVOICE_DATE
                ,SHIPPING_LOAD_NUMBER
                ,STARTING_WAVE
                ,ENDING_WAVE
                ,STAGED_DATE
                ,ORDER_HDR_STATUS
                ,DELIVERY_NUMBER
                ,NOT_SELECTED_DATE
                ,NOT_SELECTED_TIME
                ,NOT_SELECTED_REASON
                ,EST_DEL_DATE
                ,LAST_UPDATED
                ,[KEY]
                )
            VALUES (
                SOURCE.SOURCE
                ,SOURCE.DC_ORDER_NUMBER
                ,SOURCE.DCMS_ORDER_TYPE
                ,SOURCE.CUSTOMER_PURCHASE_ORDER_ID
                ,SOURCE.BILL_TO
                ,SOURCE.CUSTOMER_MASTER_RECORD_TYPE
                ,SOURCE.SHIP_TO
                ,SOURCE.SALES_ORDER
                ,SOURCE.MERCHANDISE_STYLE
                ,SOURCE.MERCHANDISE_SIZE_1
                ,SOURCE.MATERIAL
                ,SOURCE.QUALITY
                ,SOURCE.ORDER_QTY
                ,SOURCE.ORDER_SELECTED_QTY
                ,SOURCE.ORDER_SHIPPED_QTY
                ,SOURCE.RELEASED_QTY
                ,SOURCE.PICK_QTY
                ,SOURCE.PACKED_QTY
                ,SOURCE.RELEASED_CARTONS
                ,SOURCE.FULL_CASE_PICK_COUNT
                ,SOURCE.TOTE_COUNT
                ,SOURCE.SPECIAL_PROCESS_CODE_1
                ,SOURCE.SPECIAL_PROCESS_CODE_2
                ,SOURCE.SPECIAL_PROCESS_CODE_3
                ,SOURCE.ORDER_DETAIL_STATUS
                ,SOURCE.ORDER_CARRIER
                ,SOURCE.CARRIER_SERVICE_ID
                ,SOURCE.CREATE_DATE
                ,SOURCE.CREATE_TIME
                ,SOURCE.ALLOCATION_DATE
                ,SOURCE.REQUESTED_SHIP_DATE
                ,SOURCE.CANCEL_DATE
                ,SOURCE.DISPATCH_DATE
                ,SOURCE.RELEASED_DATE
                ,SOURCE.RELEASED_TIME
                ,SOURCE.PRIORITY_ORDER
                ,SOURCE.PARTIAL_SHIP
                ,SOURCE.SHIPMENT_CARTONS
                ,SOURCE.SCANNED_CARTONS
                ,SOURCE.STAGED_CARTONS
                ,SOURCE.LOADED_CARTONS
                ,SOURCE.INVOICE_DATE
                ,SOURCE.SHIPPING_LOAD_NUMBER
                ,SOURCE.STARTING_WAVE
                ,SOURCE.ENDING_WAVE
                ,SOURCE.STAGED_DATE
                ,SOURCE.ORDER_HDR_STATUS
                ,SOURCE.DELIVERY_NUMBER
                ,SOURCE.NOT_SELECTED_DATE
                ,SOURCE.NOT_SELECTED_TIME
                ,SOURCE.NOT_SELECTED_REASON
                ,SOURCE.EST_DEL_DATE
                ,GETDATE()
                ,SOURCE.[KEY]
                )  OPTION (LOOP JOIN);
END

1. Я попробовал это, на самом деле это ухудшило ситуацию. прошло более 30 минут, и он все еще выполнялся, хотя слияние снизилось на 2% до 69%. Что я сделал для повышения производительности, так это удалил примерно половину столбцов моего отчета об ОБНОВЛЕНИИ, что улучшило производительность на 5 минут (первоначально 16 минут).

2. Да, это было возможно, если предположения были неверными, индекс на BUSINESSPLANNING_TEST.dbo. [Полный заголовок]. Ключ и небольшое количество строк в исходном коде. Не могли бы вы подтвердить это? Не могли бы вы также перечислить все индексы для целевой таблицы?