如何提高SQL MERGE语句的性能

| 我目前正在运行要在不同内部服务器上的两个数据库之间同步数据的工作。一个服务器是FrontRange的HEAT产品的后端数据库。第二个是我们自己的报告数据库，用于报告编写和其他内部使用。我们在工作中的第一种方法是这样的：从所需的HEAT数据库表中查询所有数据，并填充本地临时表。然后将该数据复制到适当的表中。那会起作用，但是他们每次都使用TRUNCATE表并重新填充表，而无需为索引或碎片做任何事情。因此，我认为这可能是使用SQL merge语句的不错选择。因此，我们的第二种方法是对每个表使用合并语句。它极大地提高了处理速度，但似乎将源表锁定了，因此用户在尝试保存信息时会注意到15到30秒的延迟。为了使合并仅处理已更改或新的记录，我在select上添加了BINARY_CHECKSUM函数，并将其存储在我的身边，这样我就可以避免更新未更改的记录。似乎为每个记录都调用它很昂贵。该表大约有30万条记录。我想知道是否有更好的方法来尝试同步我忽略的这两个表。我唯一的约束是我真的不想更改源表上的任何内容，因为它是第三方应用程序。这是我用于CallLog表的mege语句：

-- 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

已邀请:

2 个回复

亲奋漏

由于您使用的是SQL 2008，因此如何更改数据捕获？您可以在一段时间内获得净更改，并且仅处理该更改（与之相反，复制会推送每个更改，即使您更新相同的数据10次）。

笛驮型迸

我认为您可以通过在merge语句之前弄清楚要修改/插入哪些行来提高性能。您可以创建一个临时表，其中包含应受到影响的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

然后，您可以在usingselect语句中使用该表。

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
    );

要回复问题请先登录或注册

如何提高SQL MERGE语句的性能

2 个回复

发起人

heat

sql_server_2008

data_synchronization

sql

sql_server

问题状态

如何提高SQL MERGE语句的性能

与内容相关的链接

2 个回复

发起人

heat

sql_server_2008

data_synchronization

sql

sql_server

问题状态