如何提高SQL MERGE语句的性能 [英] How to improve performance of SQL MERGE statement

问题描述

我目前正在运行一项工作，以在不同内部服务器上的两个数据库之间同步数据.一个服务器是FrontRange的HEAT产品的后端数据库.第二个是我们自己的报告数据库，用于编写报告和其他内部用途.

I have a job that I am currenlty running to sync data between two databases on different internal servers. The one server is the backend database for the HEAT product from FrontRange. The second is our own reporting database we are using for report writing and other internal uses.

我们在工作中的第一种方法是这样的: 从所需的HEAT数据库表中查询所有数据，并填充本地临时表.然后将该数据复制到适当的表中.那行得通，但是它们每次都使用TRUNCATE该表并重新填充，而无需为索引或碎片做任何事情.因此，我认为这可能是使用SQL merge语句的不错选择.

Our first approach at the job went something like this: Query all the data from the HEAT database tables we wanted and populate local temp tables. Then copy that data out to the appropriate table. That would work but they use to TRUNCATE the table every time and repopulate without doing anything for indexes or fragmentation. So I thought well this could be a good candidate to use the SQL merge statement.

因此，我们的第二种方法是对每个表使用合并语句.它极大地提高了处理速度，但似乎将源表锁定了，因此用户在尝试保存信息时会注意到15到30秒的延迟.为了使合并仅处理已更改或新的记录，我在select上添加了BINARY_CHECKSUM函数并将其存储在我的身边，这样我就可以避免更新未更改的记录.似乎为每个记录都调用它很昂贵.该表大约有30万条记录.

So our second approach used a merge statement for each of the tables. It greatly improved the speed of the process but it seems to be locking the source table up so that users are noticing 15-30 second delays when they try to save information. To make the merge only handle records that have had changes or are new I added the BINARY_CHECKSUM function on the select and store it on my side so that I can avoid updating records that haven't changed. Seems expensive though to call that for every record. This table has about ~300k records.

我想知道是否有更好的方法来尝试同步我忽略的两个表.我唯一的限制是我真的不想更改源表上的任何内容，因为它是第三方应用程序.

I am wondering is there a better approach to try and synchronize these two tables that I am overlooking. My only constraint is that I really do not want to change anything on the source tables because it is a third party application.

这是我用于CallLog表的mege语句:

Here is my mege statement I am using for the CallLog table:

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

推荐答案

由于您使用的是SQL 2008，因此

Since you're on SQL 2008, how about Change Data Capture? You can get the net changes over a period of time and deal only with that (as opposed to replication which pushes every change, even if you update the same data 10 times).

这篇关于如何提高SQL MERGE语句的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！