Как повысить производительность оператора SQL MERGE

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

Наш первый подход к работе был примерно таким: запрашивать все данные из таблиц базы данных HEAT, которые нам нужны, и заполнять локальные временные таблицы. Затем скопируйте эти данные в соответствующую таблицу. Это сработает, но они каждый раз используют TRUNCATE для таблицы и повторно заполняют ее, ничего не делая для индексов или фрагментации. Поэтому я подумал, что это может быть хорошим кандидатом на использование оператора слияния SQL.

Итак, наш второй подход использовал оператор слияния для каждой из таблиц. Это значительно повысило скорость процесса, но, похоже, исходная таблица блокируется, поэтому пользователи замечают 15-30-секундные задержки при попытке сохранить информацию. Чтобы слияние обрабатывало только те записи, которые были изменены или являются новыми, я добавил функцию BINARY_CHECKSUM для выбора и сохранил ее на своей стороне, чтобы избежать обновления записей, которые не изменились. Хотя называть это для каждой записи кажется дорогим. В этой таблице около 300 тыс. записей.

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

Вот мой оператор mege, который я использую для таблицы CallLog:

-- Merge CallLog
MERGE INTO [CallLog] AS T
USING (
        SELECT
            [CallID], [CustID], [CustType], [CallType], [Tracker], [CallStatus], [Priority], [CDuration], [CallCount], [StopWatch], [ClosedBy],
            [ClosedDate], [ClosedTime], [Cause], [CallDesc], [CloseDesc], [RecvdBy], [RecvdDate], [RecvdTime], [ModBy], [ModDate], [ModTime],
            [DTLastMod], [CallSource], [PriorityName], [QuickCall], [Category], [TotalAsgnmntTime], [CatHeading], [TotalJournalTime],
            [TotalTime], [SL_Warn_Goal], [SL_Warn_Date], [SL_Warn_Time], [SL_Complete_Goal], [SL_Complete_Date], [SL_Complete_Time],
            [SL_Clock_Status], [SL_Button_Status], [FirstResolution], [SL_Complete_Status], [SubCallType], [ImpactValue], [ImpactName],
            [UrgencyValue], [UrgencyName], [LinkedToProblem], [LinkedToProblemCustID], [LinkedToProblemName], [LinkedToProblemBy],
            [LinkedToProblemDate], [LinkedToProblemTime], [SLAStatus], [issue_text], [issue_number], [ResCheck], [AsgnAckBy], [AsgnAckDate],
            [AsgnAckTime], [Resolvedby], [ResolvedDate], [ResolvedTime], [ACheck], [ACKEmail], [LinkedToChange], [LinkedToChangeCustID],
            [LinkedToChangeName], [LinkedToChangeBy], [LInkedToChangeDate], [LinkedToChangeTime], [IssueTypeProblem], [IssueTypeChange],
            [RespWarningD], [RespWarningT], [RespMissedD], [RespMissedT], [ResoWarningD], [ResoWarningT], [ResoMissedD], [ResoMissedT],
            [IssueType], [SubCategory], [Diagnosis], [HSSAlert], [ErrorMessage], [ProblemType], [diagnosising], [KB], [CloseStatus],
            [SuggestedAssignGrp], [DefaultGrp], [DefaultGrpTF], [OtherAssign], [WorkAround], [ChangeReason], [CloseProblem], [AssgnApp],
            [AssgnAppRes], [DenyChk], [ImplementationApp], [ImplementationAppRes], [WorkAroundChk], [NoDenyChk], [ImpNoDenyChk],
            [ImpDenyChk], [ChangeStatus], [ReadyToClose], [ResolveOrReAssign], [TicketLabel], [CatCallType], [IssueType_PK], [Category_PK],
            [SubCategory_PK], [CallType_PK], [SubCallType_PK], BINARY_CHECKSUM(*) AS [Checksum]
        FROM
            [CHLA-HEATDB].SDIT.dbo.calllog
    ) AS S
ON (T.[CallID] = S.[CallID])
WHEN MATCHED AND T.[Checksum] <> S.[Checksum] THEN 
    UPDATE SET
        T.[CallID] = S.[CallID], T.[CustID] = S.[CustID], T.[CustType] = S.[CustType], T.[CallType] = S.[CallType],
        T.[Tracker] = S.[Tracker], t.[CallStatus] = S.[CallStatus], T.[Priority] = S.[Priority], T.[CDuration] = S.[CDuration],
        T.[CallCount] = S.[CallCount], T.[StopWatch] = S.[StopWatch], T.[ClosedBy] = S.[ClosedBy],
        T.[ClosedDate] = S.[ClosedDate], T.[ClosedTime] = S.[ClosedTime], T.[Cause] = S.[Cause], T.[CallDesc] = S.[CallDesc],
        T.[CloseDesc] = S.[CloseDesc], T.[RecvdBy] = S.[RecvdBy], T.[RecvdDate] = S.[RecvdDate], T.[RecvdTime] = S.[RecvdTime],
        T.[ModBy] = S.[ModBy], T.[ModDate] = S.[ModDate], T.[ModTime] = S.[ModTime], T.[DTLastMod] = S.[DTLastMod],
        T.[CallSource] = S.[CallSource], T.[PriorityName] = S.[PriorityName], T.[QuickCall] = S.[QuickCall],
        T.[Category] = S.[Category], T.[TotalAsgnmntTime] = S.[TotalAsgnmntTime], T.[CatHeading] = S.[CatHeading],
        T.[TotalJournalTime] = S.[TotalJournalTime], T.[TotalTime] = S.[TotalTime], T.[SL_Warn_Goal] = S.[SL_Warn_Goal],
        T.[SL_Warn_Date] = S.[SL_Warn_Date], T.[SL_Warn_Time] = S.[SL_Warn_Time], T.[SL_Complete_Goal] = S.[SL_Complete_Goal],
        T.[SL_Complete_Date] = S.[SL_Complete_Date], T.[SL_Complete_Time] = S.[SL_Complete_Time],
        T.[SL_Clock_Status] = S.[SL_Clock_Status], T.[SL_Button_Status] = S.[SL_Button_Status],
        T.[FirstResolution] = S.[FirstResolution], T.[SL_Complete_Status] = S.[SL_Complete_Status],
        T.[SubCallType] = S.[SubCallType], T.[ImpactValue] = S.[ImpactValue], T.[ImpactName] = S.[ImpactName],
        T.[UrgencyValue] = S.[UrgencyValue], T.[UrgencyName] = S.[UrgencyName], T.[LinkedToProblem] = S.[LinkedToProblem],
        T.[LinkedToProblemCustID] = S.[LinkedToProblemCustID], T.[LinkedToProblemName] = S.[LinkedToProblemName],
        T.[LinkedToProblemBy] = S.[LinkedToProblemBy], T.[LinkedToProblemDate] = S.[LinkedToProblemDate],
        T.[LinkedToProblemTime] = S.[LinkedToProblemTime], T.[SLAStatus] = S.[SLAStatus], T.[issue_text] = S.[issue_text],
        T.[issue_number] = S.[issue_number], T.[ResCheck] = S.[ResCheck], T.[AsgnAckBy] = S.[AsgnAckBy],
        T.[AsgnAckDate] = S.[AsgnAckDate], T.[AsgnAckTime] = S.[AsgnAckTime], T.[Resolvedby] = S.[Resolvedby],
        T.[ResolvedDate] = S.[ResolvedDate], T.[ResolvedTime] = S.[ResolvedTime], T.[ACheck] = S.[ACheck],
        T.[ACKEmail] = S.[ACKEmail], T.[LinkedToChange] = S.[LinkedToChange], T.[LinkedToChangeCustID] = S.[LinkedToChangeCustID],
        T.[LinkedToChangeName] = S.[LinkedToChangeName], T.[LinkedToChangeBy] = S.[LinkedToChangeBy],
        T.[LInkedToChangeDate] = S.[LInkedToChangeDate], T.[LinkedToChangeTime] = S.[LinkedToChangeTime],
        T.[IssueTypeProblem] = S.[IssueTypeProblem], T.[IssueTypeChange] = S.[IssueTypeChange],
        T.[RespWarningD] = S.[RespWarningD], T.[RespWarningT] = S.[RespWarningT], T.[RespMissedD] = S.[RespMissedD],
        T.[RespMissedT] = S.[RespMissedT], T.[ResoWarningD] = S.[ResoWarningD], T.[ResoWarningT] = S.[ResoWarningT],
        T.[ResoMissedD] = S.[ResoMissedD], T.[ResoMissedT] = S.[ResoMissedT], T.[IssueType] = S.[IssueType],
        T.[SubCategory] = S.[SubCategory], T.[Diagnosis] = S.[Diagnosis], T.[HSSAlert] = S.[HSSAlert],
        T.[ErrorMessage] = S.[ErrorMessage], T.[ProblemType] = S.[ProblemType], T.[diagnosising] = S.[diagnosising],
        T.[KB] = S.[KB], T.[CloseStatus] = S.[CloseStatus], T.[SuggestedAssignGrp] = S.[SuggestedAssignGrp],
        T.[DefaultGrp] = S.[DefaultGrp], T.[DefaultGrpTF] = S.[DefaultGrpTF], T.[OtherAssign] = S.[OtherAssign],
        T.[WorkAround] = S.[WorkAround], T.[ChangeReason] = S.[ChangeReason], T.[CloseProblem] = S.[CloseProblem],
        T.[AssgnApp] = S.[AssgnApp], T.[AssgnAppRes] = S.[AssgnAppRes], T.[DenyChk] = S.[DenyChk],
        T.[ImplementationApp] = S.[ImplementationApp], T.[ImplementationAppRes] = S.[ImplementationAppRes],
        T.[WorkAroundChk] = S.[WorkAroundChk], T.[NoDenyChk] = S.[NoDenyChk], T.[ImpNoDenyChk] = S.[ImpNoDenyChk],
        T.[ImpDenyChk] = S.[ImpDenyChk], T.[ChangeStatus] = S.[ChangeStatus], T.[ReadyToClose] = S.[ReadyToClose],
        T.[ResolveOrReAssign] = S.[ResolveOrReAssign], T.[TicketLabel] = S.[TicketLabel], T.[CatCallType] = S.[CatCallType],
        T.[IssueType_PK] = S.[IssueType_PK], T.[Category_PK] = S.[Category_PK], T.[SubCategory_PK] = S.[SubCategory_PK],
        T.[CallType_PK] = S.[CallType_PK], T.[SubCallType_PK] = S.[SubCallType_PK], T.[Checksum] = S.[Checksum]
WHEN NOT MATCHED 
    THEN INSERT VALUES
    (
        S.[CallID], S.[CustID], S.[CustType], S.[CallType], S.[Tracker], S.[CallStatus], S.[Priority], S.[CDuration],
        S.[CallCount], S.[StopWatch], S.[ClosedBy], S.[ClosedDate], S.[ClosedTime], S.[Cause], S.[CallDesc], S.[CloseDesc],
        S.[RecvdBy], S.[RecvdDate], S.[RecvdTime], S.[ModBy], S.[ModDate], S.[ModTime], S.[DTLastMod], S.[CallSource],
        S.[PriorityName], S.[QuickCall], S.[Category], S.[TotalAsgnmntTime], S.[CatHeading], S.[TotalJournalTime], S.[TotalTime],
        S.[SL_Warn_Goal], S.[SL_Warn_Date], S.[SL_Warn_Time], S.[SL_Complete_Goal], S.[SL_Complete_Date], S.[SL_Complete_Time],
        S.[SL_Clock_Status], S.[SL_Button_Status], S.[FirstResolution], S.[SL_Complete_Status], S.[SubCallType], S.[ImpactValue],
        S.[ImpactName], S.[UrgencyValue], S.[UrgencyName], S.[LinkedToProblem], S.[LinkedToProblemCustID], S.[LinkedToProblemName],
        S.[LinkedToProblemBy], S.[LinkedToProblemDate], S.[LinkedToProblemTime], S.[SLAStatus], S.[issue_text], S.[issue_number],
        S.[ResCheck], S.[AsgnAckBy], S.[AsgnAckDate], S.[AsgnAckTime], S.[Resolvedby], S.[ResolvedDate], S.[ResolvedTime], S.[ACheck],
        S.[ACKEmail], S.[LinkedToChange], S.[LinkedToChangeCustID], S.[LinkedToChangeName], S.[LinkedToChangeBy],
        S.[LInkedToChangeDate], S.[LinkedToChangeTime], S.[IssueTypeProblem], S.[IssueTypeChange], S.[RespWarningD],
        S.[RespWarningT], S.[RespMissedD], S.[RespMissedT], S.[ResoWarningD], S.[ResoWarningT], S.[ResoMissedD], S.[ResoMissedT],
        S.[IssueType], S.[SubCategory], S.[Diagnosis], S.[HSSAlert], S.[ErrorMessage], S.[ProblemType], S.[diagnosising], S.[KB],
        S.[CloseStatus], S.[SuggestedAssignGrp], S.[DefaultGrp], S.[DefaultGrpTF], S.[OtherAssign], S.[WorkAround], S.[ChangeReason],
        S.[CloseProblem], S.[AssgnApp], S.[AssgnAppRes], S.[DenyChk], S.[ImplementationApp], S.[ImplementationAppRes],
        S.[WorkAroundChk], S.[NoDenyChk], S.[ImpNoDenyChk], S.[ImpDenyChk], S.[ChangeStatus], S.[ReadyToClose],
        S.[ResolveOrReAssign], S.[TicketLabel], S.[CatCallType], S.[IssueType_PK], S.[Category_PK], S.[SubCategory_PK],
        S.[CallType_PK], S.[SubCallType_PK], S.[Checksum]
    );
GO

spinon 14.04.2011 источник

comment

Я только что обнаружил, что исходная база данных на самом деле является SQL 2005, а не 2008, поэтому ответ, представленный ниже, хотя и очень хороший вариант, к сожалению, недоступен для меня для этого сценария. - spinon 15.04.2011

Ответы (2)

arrow_upward
1
arrow_downward

Поскольку вы используете SQL 2008, как насчет Change Сбор данных? Вы можете получать чистые изменения за определенный период времени и иметь дело только с ними (в отличие от репликации, которая подталкивает каждое изменение, даже если вы обновляете одни и те же данные 10 раз).

Ben Thul 14.04.2011

comment

Звучит здорово. Не могли бы вы немного уточнить, в каком направлении я бы пошел, чтобы получить эту информацию. Я не знаком со всеми функциями SQL 2008. - spinon; 15.04.2011

comment

Я не знал, что Change Data Capture — это название функции, поскольку оно было написано строчными буквами. Я нашел некоторую информацию об этом и сейчас читаю ее. Но это выглядит великолепно. Спасибо за совет. - spinon; 15.04.2011

comment

Я также упомяну, что существует еще один вариант, помимо сбора данных об изменениях (CDC), который называется отслеживанием данных об изменениях (CDT). Он менее интенсивен, чем CDC, потому что не отслеживает все исторические изменения. Он просто хранит первичные ключи записей, которые изменились в отслеживаемых таблицах. Если вы просто пытаетесь синхронизировать текущие данные, это, вероятно, лучший вариант. - spinon; 15.04.2011

comment

Я думаю, что моя проблема была в первую очередь связана с плохо проиндексированной исходной базой данных, которую мы не могли изменить. Но я отметил это как ответ, поскольку вы указали мне направление чего-то, с чем я не был знаком и буду использовать в будущем. - spinon; 07.09.2012

arrow_upward
0
arrow_downward

Я думаю, вы можете улучшить производительность, выяснив, какие строки нужно изменить/вставить перед оператором слияния.

Вы можете создать временную таблицу, содержащую CallID, которые должны быть затронуты. Возможно что-то вроде этого.

;with cteSource as
(
  select
    S.CallID,
    binary_checksum(*) as [CheckSum]
  from [CHLA-HEATDB].SDIT.dbo.calllog
)  
select
  S.CallID,
  S.[CheckSum]
into #TmpSource
from cteSource as S    
  left outer join CallLog as T
    on S.CallID = T.CallID and
       S.[CheckSum] = T.[CheckSum]
where T.CallID is null

Затем вы можете использовать эту таблицу в своем операторе using select.

MERGE INTO [CallLog] AS T
USING (
        SELECT
            [CallID], 

            -- A lot of fields

            T.[CheckSum] AS [Checksum]
        FROM
            [CHLA-HEATDB].SDIT.dbo.calllog as C
              inner join #TmpSource as Tmp
                on C.CallID = Tmp.CallID
    ) AS S
ON (T.[CallID] = S.[CallID])
WHEN MATCHED THEN -- Probably don't need this AND T.[Checksum] <> S.[Checksum] THEN 
    UPDATE SET
        T.[CallID] = S.[CallID],
        -- More fields here
WHEN NOT MATCHED 
    THEN INSERT VALUES
    (
        S.[CallID], 
        -- More fields here
    );

Mikael Eriksson 15.04.2011

Как повысить производительность оператора SQL MERGE

Ответы (2)

Похожие вопросы