在存储进程中执行查询时，通过 Merge 执行的 UPSERT 操作会消耗大量时间

UPSERT operation via Merge consumes lot of time when query executed inside Stored Proc

提问人：xorpower 提问时间：11/4/2023 最后编辑：xorpower 更新时间：11/5/2023 访问量：93

问：

我有一个存储过程，可以执行从阶段表到事实表的 UPSERT。SSIS 作业运行此过程，存储过程只是其中一个组件。执行存储过程时，阶段表最多有 5000 条记录，但将记录从阶段插入到事实数据表需要 15 到 20 分钟。

原始存储过程：

ALTER PROCEDURE Fact.USP_PopulateFactCost @LogID INT = NULL
AS  BEGIN

DELETE FROM [Fact].[Cost]
WHERE [hashkey] IN (SELECT x.[hashkey] FROM [Stage].[FactCost] x WHERE x.action = 'DELETED');

INSERT INTO [Fact].[Cost]
           ([CostDateSID]
           ,[ProjectSID]
           ,[ProjectActivitySID]
           ,[IPCSID]
           ,[CustomerSID]
           ,[TeamSID]
           ,[ProjectTeamSID]
           ,[ProjectSubsidiarySID]
           ,[EmployeeTeamSID]
           ,[EmployeeSubsidiarySID]
           , [CapabilitySID]
           ,[streamSID]
           ,[TimeSheetEntrySID]
           ,[EmployeeSID]
           ,[FromTime]
           ,[ToTime]
           ,[BaseCurrencySID]
           ,[minutes]
           ,[hours]
           ,[days]
           ,[ProjectDays]
           ,[timeAndMaterialminutes]
           ,[timeAndMaterialhours]
           ,[timeAndMaterialdays]
           ,[NonChargeableminutes]
           ,[NonChargeablehours]
           ,[NonChargeabledays]
           ,[FixedBidMilestoneminutes]
           ,[FixedBidMilestonehours]
           ,[FixedBidMilestonedays]
           ,[Supportminutes]
           ,[Supporthours]
           ,[Supportdays]
           ,[SupportAdditionalminutes]
           ,[SupportAdditionalhours]
           ,[SupportAdditionaldays]
           ,[OvertimeMinutes]
           ,[OvertimeHours]
           ,[OvertimeDays]
           ,[ProjectManagerSID]
           ,[EmployeeRateCurrency]
           ,[EmployeeDayLength]
           ,[EmployeeRate]
           ,[EmployeeRateConverted]
           ,[CompareEmployeeRate]
           ,[CostAmount]
           ,[CompareCostAmount]
           ,[CosttimeAndMaterialAmount]
           ,[CostNonChargeableAmount]
           ,[CostFixedBidMilestoneAmount]
           ,[CostSupportAmount]
           ,[CostSupportAdditionalAmount]
           ,[CostOvertimeAmount]
           ,[HolidaysInd]
           ,[IsProjectChargeable]
           ,[IsIPCChargeable]
           ,ProjectRate
           ,ProjectRateConverted
           ,[DailyProjectRate]
           ,[DailyProjectRateConverted]
           ,BillingUnits
           ,billingDayLength
           ,ProjectRoleSID
           ,TimeAndMaterialCharge
           ,AdditionalSupportCharge
           ,ProjectChannelSID
           ,StrategicPillarSID
           ,[auInsertedLogID]
           ,[HashKey])
SELECT [CostDateSID]
           ,[ProjectSID]
           ,[ProjectActivitySID]
           ,[IPCSID]
           ,[CustomerSID]
           ,[TeamSID]
           ,[ProjectTeamSID]
           ,[ProjectSubsidiarySID]
           ,[EmployeeTeamSID]
           ,[EmployeeSubsidiarySID]
           , [CapabilitySID]
           ,[streamSID]
           ,[TimeSheetEntrySID]
           ,[EmployeeSID]
           ,[FromTime]
           ,[ToTime]
           ,[BaseCurrencySID]
           ,[minutes]
           ,[hours]
           ,[days]
           ,[ProjectDays]
           ,[timeAndMaterialminutes]
           ,[timeAndMaterialhours]
           ,[timeAndMaterialdays]
           ,[NonChargeableminutes]
           ,[NonChargeablehours]
           ,[NonChargeabledays]
           ,[FixedBidMilestoneminutes]
           ,[FixedBidMilestonehours]
           ,[FixedBidMilestonedays]
           ,[Supportminutes]
           ,[Supporthours]
           ,[Supportdays]
           ,[SupportAdditionalminutes]
           ,[SupportAdditionalhours]
           ,[SupportAdditionaldays]
           ,[OvertimeMinutes]
           ,[OvertimeHours]
           ,[OvertimeDays]
           ,[ProjectManagerSID]
           ,[EmployeeRateCurrency]
           ,[EmployeeDayLength]
           ,[EmployeeRate]
           ,[EmployeeRateConverted]
           ,[CompareEmployeeRate]
           ,[CostAmount]
           ,[CompareCostAmount]
           ,[CosttimeAndMaterialAmount]
           ,[CostNonChargeableAmount]
           ,[CostFixedBidMilestoneAmount]
           ,[CostSupportAmount]
           ,[CostSupportAdditionalAmount]
           ,[CostOvertimeAmount]
           ,[HolidaysInd]
           ,[IsProjectChargeable]
           ,[IsIPCChargeable]
           ,ProjectRate
           ,[ProjectRateConverted]
           ,[DailyProjectRate]
           ,[DailyProjectRateConverted]
           ,BillingUnits
           ,billingDayLength
           ,ProjectRoleSID
           ,TimeAndMaterialCharge
           ,AdditionalSupportCharge
           ,ProjectChannelSID
           ,StrategicPillarSID
     ,@logID
    ,[HashKey]
FROM  [Stage].[FactCost]
WHERE action = 'CREATED';

UPDATE [Fact].[Cost]
   SET     [CostDateSID] = x.[CostDateSID]
           ,[ProjectSID] = x.[ProjectSID]
           ,[ProjectActivitySID] = x.[ProjectActivitySID]
           ,[IPCSID] = x.[IPCSID]
           ,[CustomerSID] = x.[CustomerSID]
           ,[TeamSID] = x.[TeamSID]
           ,[ProjectTeamSID] = x.[ProjectTeamSID]
           ,[EmployeeTeamSID] = x.[EmployeeTeamSID]
           ,[ProjectSubsidiarySID] = x.ProjectSubsidiarySID
           ,[EmployeeSubsidiarySID] = x.EmployeeSubsidiarySID
           , [CapabilitySID] = x. [CapabilitySID]
           ,[streamSID] = x.[streamSID]
           ,[TimeSheetEntrySID] = x.[TimeSheetEntrySID]
           ,[EmployeeSID] = x.[EmployeeSID]
           ,[FromTime] = x.[FromTime]
           ,[ToTime] = x.[ToTime]
           ,[BaseCurrencySID] = x.[BaseCurrencySID]
           ,[minutes] = x.[minutes]
           ,[hours] = x.[hours]
           ,[days] = x.[days]
           ,[ProjectDays] = x.[ProjectDays]
           ,[timeAndMaterialminutes] = x.[timeAndMaterialminutes]
           ,[timeAndMaterialhours] = x.[timeAndMaterialhours]
           ,[timeAndMaterialdays] = x.[timeAndMaterialdays]
           ,[NonChargeableminutes] = x.[NonChargeableminutes]
           ,[NonChargeablehours] = x.[NonChargeablehours]
           ,[NonChargeabledays] = x.[NonChargeabledays]
           ,[FixedBidMilestoneminutes] = x.[FixedBidMilestoneminutes]
           ,[FixedBidMilestonehours] = x.[FixedBidMilestonehours]
           ,[FixedBidMilestonedays] = x.[FixedBidMilestonedays]
           ,[Supportminutes] = x.[Supportminutes]
           ,[Supporthours] = x.[Supporthours]
           ,[Supportdays] = x.[Supportdays]
           ,[SupportAdditionalminutes] = x.[SupportAdditionalminutes]
           ,[SupportAdditionalhours] = x.[SupportAdditionalhours]
           ,[SupportAdditionaldays] = x.[SupportAdditionaldays]
           ,[OverTimeMinutes] = x.OvertimeMinutes
           ,OvertimeHours = x.OvertimeHours
           ,OvertimeDays = x.OvertimeDays
           ,[ProjectManagerSID] = x.[ProjectManagerSID]
           ,[EmployeeRateCurrency] = x.[EmployeeRateCurrency]
           ,[EmployeeDayLength] = x.[EmployeeDayLength]
           ,[EmployeeRate] = x.[EmployeeRate]
           ,[EmployeeRateConverted] = x.EmployeeRateConverted
           ,[CompareEmployeeRate] = x.[CompareEmployeeRate]
           ,[CostAmount] = x.[CostAmount]
           ,[CompareCostAmount] = x.CompareCostAmount
           ,[CosttimeAndMaterialAmount] = x.[CosttimeAndMaterialAmount]
           ,[CostNonChargeableAmount] = x.[CostNonChargeableAmount]
           ,[CostFixedBidMilestoneAmount] = x.[CostFixedBidMilestoneAmount]
           ,[CostSupportAmount] = x.[CostSupportAmount]
           ,[CostSupportAdditionalAmount] = x.[CostSupportAdditionalAmount]
           ,[CostOvertimeAmount] = x.[CostOvertimeAmount]             
           ,[HolidaysInd]   = x.[HolidaysInd]   
           ,[IsProjectChargeable] = x.[IsProjectChargeable]
           ,[IsIPCChargeable]  = x.[IsIPCChargeable]
           ,ProjectRate = x.ProjectRate
           ,ProjectRateConverted = x.ProjectRateConverted
           ,[DailyProjectRate] = x.DailyProjectRate
           ,[DailyProjectRateConverted] = x.DailyProjectRateConverted
           ,BillingUnits = x.BillingUnits
           ,billingDayLength = x.billingDayLength              
           ,ProjectRoleSID = x.ProjectRoleSID
           ,TimeAndMaterialCharge = x.TimeAndMaterialCharge
           ,AdditionalSupportCharge = x.AdditionalSupportCharge
           ,ProjectChannelSID = x.ProjectChannelSID
           ,StrategicPillarSID = x.StrategicPillarSID
      ,[auUpdatedLogID] =  @LogID
FROM [fact].[Cost] f
JOIN [Stage].[FactCost] x ON f.[HashKey] = x.[HashKey]  AND action = 'UPDATED'
END
GO

然后，我用语句修改了存储过程，但事实证明，该逻辑需要 40 分钟才能运行。MERGEMERGE

MERGE的两个逻辑：

一个。使用不删除的 MERGE：

  ALTER PROCEDURE Fact.USP_PopulateFactCost
@LogID INT = NULL
AS
BEGIN

---New SP
MERGE [Fact].[Cost] WITH (HOLDLOCK)  TARGET
USING (SELECT * FROM Stage.FactCost)   SourceTbl 
ON (SourceTbl.HashKey= TARGET.HashKey AND SourceTbl.CostSID = Target.CostSID)
WHEN MATCHED AND SourceTbl.Action='UPDATED' THEN 
    UPDATE 
       SET  TARGET.[CostDateSID] = SourceTbl.[CostDateSID]
           ,TARGET.[ProjectSID] = SourceTbl.[ProjectSID]
               ,TARGET.[ProjectActivitySID] = SourceTbl.[ProjectActivitySID]
               ,TARGET.[IPCSID] = SourceTbl.[IPCSID]
               ,TARGET.[CustomerSID] = SourceTbl.[CustomerSID]
               ,TARGET.[TeamSID] = SourceTbl.[TeamSID]
               ,TARGET.[ProjectTeamSID] = SourceTbl.[ProjectTeamSID]
               ,TARGET.[EmployeeTeamSID] = SourceTbl.[EmployeeTeamSID]
               ,TARGET.[ProjectSubsidiarySID] = SourceTbl.ProjectSubsidiarySID
               ,TARGET.[EmployeeSubsidiarySID] = SourceTbl.EmployeeSubsidiarySID
               ,TARGET.[CapabilitySID] = SourceTbl. [CapabilitySID]
               ,TARGET.[streamSID] = SourceTbl.[streamSID]
               ,TARGET.[TimeSheetEntrySID] = SourceTbl.[TimeSheetEntrySID]
               ,TARGET.[EmployeeSID] = SourceTbl.[EmployeeSID]
               ,TARGET.[FromTime] = SourceTbl.[FromTime]
               ,TARGET.[ToTime] = SourceTbl.[ToTime]
               ,TARGET.[BaseCurrencySID] = SourceTbl.[BaseCurrencySID]
               ,TARGET.[minutes] = SourceTbl.[minutes]
               ,TARGET.[hours] = SourceTbl.[hours]
               ,TARGET.[days] = SourceTbl.[days]
               ,TARGET.[ProjectDays] = SourceTbl.[ProjectDays]
               ,TARGET.[timeAndMaterialminutes] = SourceTbl.[timeAndMaterialminutes]
               ,TARGET.[timeAndMaterialhours] = SourceTbl.[timeAndMaterialhours]
               ,TARGET.[timeAndMaterialdays] = SourceTbl.[timeAndMaterialdays]
               ,TARGET.[NonChargeableminutes] = SourceTbl.[NonChargeableminutes]
               ,TARGET.[NonChargeablehours] = SourceTbl.[NonChargeablehours]
               ,TARGET.[NonChargeabledays] = SourceTbl.[NonChargeabledays]
               ,TARGET.[FixedBidMilestoneminutes] = SourceTbl.[FixedBidMilestoneminutes]
               ,TARGET.[FixedBidMilestonehours] = SourceTbl.[FixedBidMilestonehours]
               ,TARGET.[FixedBidMilestonedays] = SourceTbl.[FixedBidMilestonedays]
               ,TARGET.[Supportminutes] = SourceTbl.[Supportminutes]
               ,TARGET.[Supporthours] = SourceTbl.[Supporthours]
               ,TARGET.[Supportdays] = SourceTbl.[Supportdays]
               ,TARGET.[SupportAdditionalminutes] = SourceTbl.[SupportAdditionalminutes]
               ,TARGET.[SupportAdditionalhours] = SourceTbl.[SupportAdditionalhours]
               ,TARGET.[SupportAdditionaldays] = SourceTbl.[SupportAdditionaldays]
               ,TARGET.[OverTimeMinutes] = SourceTbl.OvertimeMinutes
               ,TARGET.OvertimeHours = SourceTbl.OvertimeHours
               ,TARGET.OvertimeDays = SourceTbl.OvertimeDays
               ,TARGET.[ProjectManagerSID] = SourceTbl.[ProjectManagerSID]
               ,TARGET.[EmployeeRateCurrency] = SourceTbl.[EmployeeRateCurrency]
               ,TARGET.[EmployeeDayLength] = SourceTbl.[EmployeeDayLength]
               ,TARGET.[EmployeeRate] = SourceTbl.[EmployeeRate]
               ,TARGET.[EmployeeRateConverted] = SourceTbl.EmployeeRateConverted
               ,TARGET.[CompareEmployeeRate] = SourceTbl.[CompareEmployeeRate]
               ,TARGET.[CostAmount] = SourceTbl.[CostAmount]
               ,TARGET.[CompareCostAmount] = SourceTbl.CompareCostAmount
               ,TARGET.[CosttimeAndMaterialAmount] = SourceTbl.[CosttimeAndMaterialAmount]
               ,TARGET.[CostNonChargeableAmount] = SourceTbl.[CostNonChargeableAmount]
               ,TARGET.[CostFixedBidMilestoneAmount] = SourceTbl.[CostFixedBidMilestoneAmount]
               ,TARGET.[CostSupportAmount] = SourceTbl.[CostSupportAmount]
               ,TARGET.[CostSupportAdditionalAmount] = SourceTbl.[CostSupportAdditionalAmount]
               ,TARGET.[CostOvertimeAmount] = SourceTbl.[CostOvertimeAmount]              
               ,TARGET.[HolidaysInd]    = SourceTbl.[HolidaysInd]   
               ,TARGET.[IsProjectChargeable] = SourceTbl.[IsProjectChargeable]
               ,TARGET.[IsIPCChargeable]  = SourceTbl.[IsIPCChargeable]
               ,TARGET.ProjectRate = SourceTbl.ProjectRate
               ,TARGET.ProjectRateConverted = SourceTbl.ProjectRateConverted
               ,TARGET.[DailyProjectRate] = SourceTbl.DailyProjectRate
               ,TARGET.[DailyProjectRateConverted] = SourceTbl.DailyProjectRateConverted
               ,TARGET.BillingUnits = SourceTbl.BillingUnits
               ,TARGET.billingDayLength = SourceTbl.billingDayLength               
               ,TARGET.ProjectRoleSID = SourceTbl.ProjectRoleSID
               ,TARGET.TimeAndMaterialCharge = SourceTbl.TimeAndMaterialCharge
               ,TARGET.AdditionalSupportCharge = SourceTbl.AdditionalSupportCharge
               ,TARGET.ProjectChannelSID = SourceTbl.ProjectChannelSID
               ,TARGET.StrategicPillarSID = SourceTbl.StrategicPillarSID
               ,TARGET.[auUpdatedLogID] =  @LogID

  WHEN NOT MATCHED BY TARGET AND Sourcetbl.Action='CREATED' 
    THEN
         INSERT ([CostDateSID]
               ,[ProjectSID]
               ,[ProjectActivitySID]
               ,[IPCSID]
               ,[CustomerSID]
               ,[TeamSID]
               ,[ProjectTeamSID]
               ,[ProjectSubsidiarySID]
               ,[EmployeeTeamSID]
               ,[EmployeeSubsidiarySID]
               ,[CapabilitySID]
               ,[streamSID]
               ,[TimeSheetEntrySID]
               ,[EmployeeSID]
               ,[FromTime]
               ,[ToTime]
               ,[BaseCurrencySID]
               ,[minutes]
               ,[hours]
               ,[days]
               ,[ProjectDays]
               ,[timeAndMaterialminutes]
               ,[timeAndMaterialhours]
               ,[timeAndMaterialdays]
               ,[NonChargeableminutes]
               ,[NonChargeablehours]
               ,[NonChargeabledays]
               ,[FixedBidMilestoneminutes]
               ,[FixedBidMilestonehours]
               ,[FixedBidMilestonedays]
               ,[Supportminutes]
               ,[Supporthours]
               ,[Supportdays]
               ,[SupportAdditionalminutes]
               ,[SupportAdditionalhours]
               ,[SupportAdditionaldays]
               ,[OvertimeMinutes]
               ,[OvertimeHours]
               ,[OvertimeDays]
               ,[ProjectManagerSID]
               ,[EmployeeRateCurrency]
               ,[EmployeeDayLength]
               ,[EmployeeRate]
               ,[EmployeeRateConverted]
               ,[CompareEmployeeRate]
               ,[CostAmount]
               ,[CompareCostAmount]
               ,[CosttimeAndMaterialAmount]
               ,[CostNonChargeableAmount]
               ,[CostFixedBidMilestoneAmount]
               ,[CostSupportAmount]
               ,[CostSupportAdditionalAmount]
               ,[CostOvertimeAmount]
               ,[HolidaysInd]
             ,[IsProjectChargeable]
             ,[IsIPCChargeable]
               ,ProjectRate
               ,ProjectRateConverted
               ,[DailyProjectRate]
               ,[DailyProjectRateConverted]
               ,BillingUnits
               ,billingDayLength
               ,ProjectRoleSID
               ,TimeAndMaterialCharge
               ,AdditionalSupportCharge
               ,ProjectChannelSID
               ,StrategicPillarSID
               ,[auInsertedLogID]
               ,[HashKey])

   VALUES 
   (   
                Sourcetbl.[CostDateSID]
               ,Sourcetbl.[ProjectSID]
               ,Sourcetbl.[ProjectActivitySID]
               ,Sourcetbl.[IPCSID]
               ,Sourcetbl.[CustomerSID]
               ,Sourcetbl.[TeamSID]
               ,Sourcetbl.[ProjectTeamSID]
               ,Sourcetbl.[ProjectSubsidiarySID]
               ,Sourcetbl.[EmployeeTeamSID]
               ,Sourcetbl.[EmployeeSubsidiarySID]
               ,Sourcetbl.[CapabilitySID]
               ,Sourcetbl.[streamSID]
               ,Sourcetbl.[TimeSheetEntrySID]
               ,Sourcetbl.[EmployeeSID]
               ,Sourcetbl.[FromTime]
               ,Sourcetbl.[ToTime]
               ,Sourcetbl.[BaseCurrencySID]
               ,Sourcetbl.[minutes]
               ,Sourcetbl.[hours]
               ,Sourcetbl.[days]
               ,Sourcetbl.[ProjectDays]
               ,Sourcetbl.[timeAndMaterialminutes]
               ,Sourcetbl.[timeAndMaterialhours]
               ,Sourcetbl.[timeAndMaterialdays]
               ,Sourcetbl.[NonChargeableminutes]
               ,Sourcetbl.[NonChargeablehours]
               ,Sourcetbl.[NonChargeabledays]
               ,Sourcetbl.[FixedBidMilestoneminutes]
               ,Sourcetbl.[FixedBidMilestonehours]
               ,Sourcetbl.[FixedBidMilestonedays]
               ,Sourcetbl.[Supportminutes]
               ,Sourcetbl.[Supporthours]
               ,Sourcetbl.[Supportdays]
               ,Sourcetbl.[SupportAdditionalminutes]
               ,Sourcetbl.[SupportAdditionalhours]
               ,Sourcetbl.[SupportAdditionaldays]
               ,Sourcetbl.[OvertimeMinutes]
               ,Sourcetbl.[OvertimeHours]
               ,Sourcetbl.[OvertimeDays]
               ,Sourcetbl.[ProjectManagerSID]
               ,Sourcetbl.[EmployeeRateCurrency]
               ,Sourcetbl.[EmployeeDayLength]
               ,Sourcetbl.[EmployeeRate]
               ,Sourcetbl.[EmployeeRateConverted]
               ,Sourcetbl.[CompareEmployeeRate]
               ,Sourcetbl.[CostAmount]
               ,Sourcetbl.[CompareCostAmount]
               ,Sourcetbl.[CosttimeAndMaterialAmount]
               ,Sourcetbl.[CostNonChargeableAmount]
               ,Sourcetbl.[CostFixedBidMilestoneAmount]
               ,Sourcetbl.[CostSupportAmount]
               ,Sourcetbl.[CostSupportAdditionalAmount]
               ,Sourcetbl.[CostOvertimeAmount]
               ,Sourcetbl.[HolidaysInd]
             ,Sourcetbl.[IsProjectChargeable]
             ,Sourcetbl.[IsIPCChargeable]
               ,Sourcetbl.ProjectRate
               ,Sourcetbl.[ProjectRateConverted]
               ,Sourcetbl.[DailyProjectRate]
               ,Sourcetbl.[DailyProjectRateConverted]
               ,Sourcetbl.BillingUnits
               ,Sourcetbl.billingDayLength
               ,Sourcetbl.ProjectRoleSID
               ,Sourcetbl.TimeAndMaterialCharge
               ,Sourcetbl.AdditionalSupportCharge
               ,Sourcetbl.ProjectChannelSID
               ,Sourcetbl.StrategicPillarSID
            ,@LogID
             ,Sourcetbl.[HashKey]
)   

  WHEN NOT MATCHED BY SOURCE THEN --- AND SourceTbl.Action='DELETED' THEN
   DELETE 
   ;
END

GO

b.将 MERGE 与删除逻辑一起使用：

ALTER PROCEDURE Fact.USP_PopulateFactCost
@LogID INT = NULL
AS
BEGIN

DELETE FROM [Fact].[Cost]
    WHERE [hashkey] IN (SELECT x.[hashkey] FROM [Stage].[FactCost] x WHERE x.action = 'DELETED')

---New SP
MERGE [Fact].[Cost] WITH (HOLDLOCK)  TARGET
USING Stage.FactCost   SourceTbl 
ON (SourceTbl.HashKey= TARGET.HashKey)
WHEN MATCHED AND SourceTbl.Action='UPDATED' THEN 
    UPDATE 
       SET  TARGET.[CostDateSID] = SourceTbl.[CostDateSID]
           ,TARGET.[ProjectSID] = SourceTbl.[ProjectSID]
               ,TARGET.[ProjectActivitySID] = SourceTbl.[ProjectActivitySID]
               ,TARGET.[IPCSID] = SourceTbl.[IPCSID]
               ,TARGET.[CustomerSID] = SourceTbl.[CustomerSID]
               ,TARGET.[TeamSID] = SourceTbl.[TeamSID]
               ,TARGET.[ProjectTeamSID] = SourceTbl.[ProjectTeamSID]
               ,TARGET.[EmployeeTeamSID] = SourceTbl.[EmployeeTeamSID]
               ,TARGET.[ProjectSubsidiarySID] = SourceTbl.ProjectSubsidiarySID
               ,TARGET.[EmployeeSubsidiarySID] = SourceTbl.EmployeeSubsidiarySID
               ,TARGET.[CapabilitySID] = SourceTbl. [CapabilitySID]
               ,TARGET.[streamSID] = SourceTbl.[streamSID]
               ,TARGET.[TimeSheetEntrySID] = SourceTbl.[TimeSheetEntrySID]
               ,TARGET.[EmployeeSID] = SourceTbl.[EmployeeSID]
               ,TARGET.[FromTime] = SourceTbl.[FromTime]
               ,TARGET.[ToTime] = SourceTbl.[ToTime]
               ,TARGET.[BaseCurrencySID] = SourceTbl.[BaseCurrencySID]
               ,TARGET.[minutes] = SourceTbl.[minutes]
               ,TARGET.[hours] = SourceTbl.[hours]
               ,TARGET.[days] = SourceTbl.[days]
               ,TARGET.[ProjectDays] = SourceTbl.[ProjectDays]
               ,TARGET.[timeAndMaterialminutes] = SourceTbl.[timeAndMaterialminutes]
               ,TARGET.[timeAndMaterialhours] = SourceTbl.[timeAndMaterialhours]
               ,TARGET.[timeAndMaterialdays] = SourceTbl.[timeAndMaterialdays]
               ,TARGET.[NonChargeableminutes] = SourceTbl.[NonChargeableminutes]
               ,TARGET.[NonChargeablehours] = SourceTbl.[NonChargeablehours]
               ,TARGET.[NonChargeabledays] = SourceTbl.[NonChargeabledays]
               ,TARGET.[FixedBidMilestoneminutes] = SourceTbl.[FixedBidMilestoneminutes]
               ,TARGET.[FixedBidMilestonehours] = SourceTbl.[FixedBidMilestonehours]
               ,TARGET.[FixedBidMilestonedays] = SourceTbl.[FixedBidMilestonedays]
               ,TARGET.[Supportminutes] = SourceTbl.[Supportminutes]
               ,TARGET.[Supporthours] = SourceTbl.[Supporthours]
               ,TARGET.[Supportdays] = SourceTbl.[Supportdays]
               ,TARGET.[SupportAdditionalminutes] = SourceTbl.[SupportAdditionalminutes]
               ,TARGET.[SupportAdditionalhours] = SourceTbl.[SupportAdditionalhours]
               ,TARGET.[SupportAdditionaldays] = SourceTbl.[SupportAdditionaldays]
               ,TARGET.[OverTimeMinutes] = SourceTbl.OvertimeMinutes
               ,TARGET.OvertimeHours = SourceTbl.OvertimeHours
               ,TARGET.OvertimeDays = SourceTbl.OvertimeDays
               ,TARGET.[ProjectManagerSID] = SourceTbl.[ProjectManagerSID]
               ,TARGET.[EmployeeRateCurrency] = SourceTbl.[EmployeeRateCurrency]
               ,TARGET.[EmployeeDayLength] = SourceTbl.[EmployeeDayLength]
               ,TARGET.[EmployeeRate] = SourceTbl.[EmployeeRate]
               ,TARGET.[EmployeeRateConverted] = SourceTbl.EmployeeRateConverted
               ,TARGET.[CompareEmployeeRate] = SourceTbl.[CompareEmployeeRate]
               ,TARGET.[CostAmount] = SourceTbl.[CostAmount]
               ,TARGET.[CompareCostAmount] = SourceTbl.CompareCostAmount
               ,TARGET.[CosttimeAndMaterialAmount] = SourceTbl.[CosttimeAndMaterialAmount]
               ,TARGET.[CostNonChargeableAmount] = SourceTbl.[CostNonChargeableAmount]
               ,TARGET.[CostFixedBidMilestoneAmount] = SourceTbl.[CostFixedBidMilestoneAmount]
               ,TARGET.[CostSupportAmount] = SourceTbl.[CostSupportAmount]
               ,TARGET.[CostSupportAdditionalAmount] = SourceTbl.[CostSupportAdditionalAmount]
               ,TARGET.[CostOvertimeAmount] = SourceTbl.[CostOvertimeAmount]              
               ,TARGET.[HolidaysInd]    = SourceTbl.[HolidaysInd]   
               ,TARGET.[IsProjectChargeable] = SourceTbl.[IsProjectChargeable]
               ,TARGET.[IsIPCChargeable]  = SourceTbl.[IsIPCChargeable]
               ,TARGET.ProjectRate = SourceTbl.ProjectRate
               ,TARGET.ProjectRateConverted = SourceTbl.ProjectRateConverted
               ,TARGET.[DailyProjectRate] = SourceTbl.DailyProjectRate
               ,TARGET.[DailyProjectRateConverted] = SourceTbl.DailyProjectRateConverted
               ,TARGET.BillingUnits = SourceTbl.BillingUnits
               ,TARGET.billingDayLength = SourceTbl.billingDayLength               
               ,TARGET.ProjectRoleSID = SourceTbl.ProjectRoleSID
               ,TARGET.TimeAndMaterialCharge = SourceTbl.TimeAndMaterialCharge
               ,TARGET.AdditionalSupportCharge = SourceTbl.AdditionalSupportCharge
               ,TARGET.ProjectChannelSID = SourceTbl.ProjectChannelSID
               ,TARGET.StrategicPillarSID = SourceTbl.StrategicPillarSID
               ,TARGET.[auUpdatedLogID] =  @LogID

  WHEN NOT MATCHED BY TARGET AND Sourcetbl.Action='CREATED' 
    THEN
         INSERT ([CostDateSID]
               ,[ProjectSID]
               ,[ProjectActivitySID]
               ,[IPCSID]
               ,[CustomerSID]
               ,[TeamSID]
               ,[ProjectTeamSID]
               ,[ProjectSubsidiarySID]
               ,[EmployeeTeamSID]
               ,[EmployeeSubsidiarySID]
               ,[CapabilitySID]
               ,[streamSID]
               ,[TimeSheetEntrySID]
               ,[EmployeeSID]
               ,[FromTime]
               ,[ToTime]
               ,[BaseCurrencySID]
               ,[minutes]
               ,[hours]
               ,[days]
               ,[ProjectDays]
               ,[timeAndMaterialminutes]
               ,[timeAndMaterialhours]
               ,[timeAndMaterialdays]
               ,[NonChargeableminutes]
               ,[NonChargeablehours]
               ,[NonChargeabledays]
               ,[FixedBidMilestoneminutes]
               ,[FixedBidMilestonehours]
               ,[FixedBidMilestonedays]
               ,[Supportminutes]
               ,[Supporthours]
               ,[Supportdays]
               ,[SupportAdditionalminutes]
               ,[SupportAdditionalhours]
               ,[SupportAdditionaldays]
               ,[OvertimeMinutes]
               ,[OvertimeHours]
               ,[OvertimeDays]
               ,[ProjectManagerSID]
               ,[EmployeeRateCurrency]
               ,[EmployeeDayLength]
               ,[EmployeeRate]
               ,[EmployeeRateConverted]
               ,[CompareEmployeeRate]
               ,[CostAmount]
               ,[CompareCostAmount]
               ,[CosttimeAndMaterialAmount]
               ,[CostNonChargeableAmount]
               ,[CostFixedBidMilestoneAmount]
               ,[CostSupportAmount]
               ,[CostSupportAdditionalAmount]
               ,[CostOvertimeAmount]
               ,[HolidaysInd]
             ,[IsProjectChargeable]
             ,[IsIPCChargeable]
               ,ProjectRate
               ,ProjectRateConverted
               ,[DailyProjectRate]
               ,[DailyProjectRateConverted]
               ,BillingUnits
               ,billingDayLength
               ,ProjectRoleSID
               ,TimeAndMaterialCharge
               ,AdditionalSupportCharge
               ,ProjectChannelSID
               ,StrategicPillarSID
               ,[auInsertedLogID]
               ,[HashKey])

   VALUES 
   (   
                Sourcetbl.[CostDateSID]
               ,Sourcetbl.[ProjectSID]
               ,Sourcetbl.[ProjectActivitySID]
               ,Sourcetbl.[IPCSID]
               ,Sourcetbl.[CustomerSID]
               ,Sourcetbl.[TeamSID]
               ,Sourcetbl.[ProjectTeamSID]
               ,Sourcetbl.[ProjectSubsidiarySID]
               ,Sourcetbl.[EmployeeTeamSID]
               ,Sourcetbl.[EmployeeSubsidiarySID]
               ,Sourcetbl.[CapabilitySID]
               ,Sourcetbl.[streamSID]
               ,Sourcetbl.[TimeSheetEntrySID]
               ,Sourcetbl.[EmployeeSID]
               ,Sourcetbl.[FromTime]
               ,Sourcetbl.[ToTime]
               ,Sourcetbl.[BaseCurrencySID]
               ,Sourcetbl.[minutes]
               ,Sourcetbl.[hours]
               ,Sourcetbl.[days]
               ,Sourcetbl.[ProjectDays]
               ,Sourcetbl.[timeAndMaterialminutes]
               ,Sourcetbl.[timeAndMaterialhours]
               ,Sourcetbl.[timeAndMaterialdays]
               ,Sourcetbl.[NonChargeableminutes]
               ,Sourcetbl.[NonChargeablehours]
               ,Sourcetbl.[NonChargeabledays]
               ,Sourcetbl.[FixedBidMilestoneminutes]
               ,Sourcetbl.[FixedBidMilestonehours]
               ,Sourcetbl.[FixedBidMilestonedays]
               ,Sourcetbl.[Supportminutes]
               ,Sourcetbl.[Supporthours]
               ,Sourcetbl.[Supportdays]
               ,Sourcetbl.[SupportAdditionalminutes]
               ,Sourcetbl.[SupportAdditionalhours]
               ,Sourcetbl.[SupportAdditionaldays]
               ,Sourcetbl.[OvertimeMinutes]
               ,Sourcetbl.[OvertimeHours]
               ,Sourcetbl.[OvertimeDays]
               ,Sourcetbl.[ProjectManagerSID]
               ,Sourcetbl.[EmployeeRateCurrency]
               ,Sourcetbl.[EmployeeDayLength]
               ,Sourcetbl.[EmployeeRate]
               ,Sourcetbl.[EmployeeRateConverted]
               ,Sourcetbl.[CompareEmployeeRate]
               ,Sourcetbl.[CostAmount]
               ,Sourcetbl.[CompareCostAmount]
               ,Sourcetbl.[CosttimeAndMaterialAmount]
               ,Sourcetbl.[CostNonChargeableAmount]
               ,Sourcetbl.[CostFixedBidMilestoneAmount]
               ,Sourcetbl.[CostSupportAmount]
               ,Sourcetbl.[CostSupportAdditionalAmount]
               ,Sourcetbl.[CostOvertimeAmount]
               ,Sourcetbl.[HolidaysInd]
             ,Sourcetbl.[IsProjectChargeable]
             ,Sourcetbl.[IsIPCChargeable]
               ,Sourcetbl.ProjectRate
               ,Sourcetbl.[ProjectRateConverted]
               ,Sourcetbl.[DailyProjectRate]
               ,Sourcetbl.[DailyProjectRateConverted]
               ,Sourcetbl.BillingUnits
               ,Sourcetbl.billingDayLength
               ,Sourcetbl.ProjectRoleSID
               ,Sourcetbl.TimeAndMaterialCharge
               ,Sourcetbl.AdditionalSupportCharge
               ,Sourcetbl.ProjectChannelSID
               ,Sourcetbl.StrategicPillarSID
            ,@LogID
             ,Sourcetbl.[HashKey]
)   

   ;
END

GO

两个代码片段都需要 40 分钟以上，第二个代码需要 60+ 分钟。这里有什么选择？如何优化代码，以便插入 5000 条记录需要几分钟（最多一两分钟）

我在这里错过了什么？一定是我做错了什么，否则声明应该快速运行。MERGE

谢谢，非常感谢

sql sql-server sql-server-2012

评论

0赞 Xedni 11/4/2023
目标表有多大，存在哪些索引？
2赞 Dale K 11/4/2023
1） 你标记了多个版本的 SQL Server - 为什么？2）问题需要自成一体 - 不需要人们访问异地资源。3）SQL性能问题需要执行计划。这不是一般或理论上可以回答的问题，因为 SQL 是一种声明性语言，您正在描述您想要的结果，但引擎如何选择执行您的请求取决于它，并且取决于很多因素。
0赞 xorpower 11/4/2023
@DaleK 已经解决了这些问题。不知道我如何跟踪执行计划，因为查询通过 SSIS 内的 SP 运行。此外，当我直接执行 SP 时，它会在一秒钟内执行所有 5000 条记录。
0赞 Dale K 11/5/2023
stackoverflow.com/questions/57820177/......
0赞 xorpower 11/5/2023
@DaleK需要在哪里运行探查器？在运行软件包的服务器中，还是在具有 SP？由于 SP 是通过 SSIS 包运行的。

答： 暂无答案

上一个：查看差异表

下一个：如何在SQL Server中重复缺少日期的最后一行？