sql - MERGE 语句执行时间太长(仅针对 1 条记录)。 (MS-SQL Server 2012)

Question

我遇到的问题是这个 SQL 合并命令执行时间太长？仍然需要超过 30 秒。我们使用的是 MS-SQL 2012 Server。

那么，谁能告诉我 MERGE 语句做错了什么以及为什么？

谢谢...

/****** Object:  StoredProcedure [dbo].[spDealerAccount_VehicleSalesRecordReload]    Script Date: 07/02/2014 11:02:21 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ============================================================================
-- Author:      ...
-- Create date: 07/01/2014
-- Description: 
--      Either creates new or updates existing vehicle inventory record.
--      Record is updated only if ***either*** of following are met:
--          - source AccountID exists
--          - source VIN exists
--          - both source AccountID & VIN exists having no duplicate unique vehicle
-- References:
--      http://technet.microsoft.com/en-us/library/bb510625.aspx
--      http://en.wikipedia.org/wiki/Upsert
--      http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx (for explanation of HOLDLOCK)
-- ============================================================================
ALTER PROCEDURE [dbo].[spDealerAccount_VehicleSalesRecordReload]
    @parmAccountId              [bigint], 
    @parmBitwiseAcceptedTotal   [bigint],
    @parmYear                   [nvarchar](50),
    @parmMake                   [nvarchar](50),
    @parmModel                  [nvarchar](50),
    @parmTrim                   [nvarchar](80),
    @parmVin                    [nvarchar](17),
    @parmSquishVin              [nvarchar](12),
    @parmVinValidationSkipped   [bit],
    @parmMileage                [int],
    @parmPurchaseDate           [date],
    @parmSaleDate               [date],
    @parmNumOfDays              [int],
    @parmSaleType               [nvarchar](1),
    @parmPurchaseAmount         [money],
    @parmRepairCostAmount       [money],
    @parmSaleAmount             [money],
    @parmTotalTradeAmount       [money],
    @parmGrossProfitAmount      [money],
    @parmZipcode                [nvarchar](50),
    @parmCity                   [nvarchar](50),
    @parmState                  [nvarchar](50),
    @parmRegion                 [nvarchar](50),
    @parmStockNumber            [nvarchar](50),
    @parmLocation               [nvarchar](150),
    @parmDataSourceFrom         [nvarchar](500),
    @parmImportedVersion        [int],
    @parmCondition              [nvarchar](50),
    @parmAccessories            [nvarchar](MAX),
    @parmEngineDetail           [nvarchar](50),
    @parmExteriorColor          [nvarchar](50) 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
    SET NOCOUNT ON;

    MERGE 
        INTO [dbo].[tblDealerSalesVehicles] WITH (HOLDLOCK) AS t
        USING [dbo].[tblDealerSalesVehicles] AS s
        ON (t.[AccountId] = s.[AccountId] AND t.[Vin] = s.[Vin])
    --  
    -- if matched, update existing record if right conditions are met (see Description in header)   
    WHEN MATCHED THEN
        UPDATE SET 
            --RawID...
            --AccountID...
            t.[BitwiseAcceptedTotal] = @parmBitwiseAcceptedTotal, 
            t.[Year] = @parmYear,
            t.[Make] = @parmMake,
            t.[Model] = @parmModel,
            t.[Trim] = @parmTrim,
            --VIN...
            --SquishVin...
            --[VinValidationSkipped...
            t.[Mileage] = @parmMileage,
            t.[StockDate] = @parmPurchaseDate,
            t.[SoldDate] = @parmSaleDate,
            t.[NumOfDays] = @parmNumOfDays,
            t.[SaleType] = @parmSaleType,
            t.[PurchPrice] = @parmPurchaseAmount,
            t.[RepairCost] = @parmRepairCostAmount,
            t.[SalePrice] = @parmSaleAmount,
            t.[TotalTrade] = @parmTotalTradeAmount,
            t.[GrossProfit] = @parmGrossProfitAmount,
            t.[Zipcode] = @parmZipcode,
            t.[City] = @parmCity,
            t.[State] = @parmState,
            t.[Region] = @parmRegion,
            t.[StockNo] = @parmStockNumber,
            t.[Location] = @parmLocation,
            t.[DataSourceFrom] = @parmDataSourceFrom,
            t.[ImportedVersion] = @parmImportedVersion,
            t.[ModifiedDate] = GETDATE(),
            --ImportedDate...
            t.[TimeStamp] = GETDATE()
    --             
    -- if not matched, add new record and set return values
    WHEN NOT MATCHED THEN   
         --http://stackoverflow.com/questions/1609208/need-help-with-the-merge-statement...
        --(Cannot use "s." or "t." for source or target table-name-abbreviation cuz "Columns name in the insert list can only refer to the target table, so the parser doesn't expect to see a table alias there, wouldn't know how to resolve it. It sees "column1", it knows it belongs to the target table. It sees "table1.column1", it doesn't know what "table1" means. "table1" is out of scope, so to speak")...
        INSERT (
            --RawID...
            [AccountID], [BitwiseAcceptedTotal],
            [Year], [Make], [Model], [Trim], [VIN], [SquishVin], [VinValidationSkipped],
            [Mileage], [StockDate], [SoldDate], [NumOfDays], [SaleType],
            [PurchPrice], [RepairCost], [SalePrice], 
            [TotalTrade], [GrossProfit], 
            [Zipcode], [City], [State], [Region],
            [StockNo], [Location], 
            [DataSourceFrom], [ImportedVersion], [ModifiedDate], [ImportedDate], [TimeStamp]
        ) 
        VALUES (
            @parmAccountId, @parmBitwiseAcceptedTotal,
            @parmYear, @parmMake, @parmModel, @parmTrim, @parmVin, @parmSquishVin, @parmVinValidationSkipped,
            @parmMileage, @parmPurchaseDate, @parmSaleDate, @parmNumOfDays, @parmSaleType,
            @parmPurchaseAmount, @parmRepairCostAmount, @parmSaleAmount,  
            @parmTotalTradeAmount, @parmGrossProfitAmount,  
            @parmZipcode, @parmCity, @parmState, @parmRegion, 
            @parmStockNumber, @parmLocation, 
            @parmDataSourceFrom, @parmImportedVersion, NULL, GETDATE(), GETDATE()
        )
    ; -- required semicolon separator for MERGE

    RETURN @@ERROR; 
END

Answer 1

我认为您的 MERGE 语句没有达到您的预期。您具有相同的源和目标，没有指向参数的链接，因此这一点:

MERGE
    INTO [dbo].[tblDealerSalesVehicles] WITH (HOLDLOCK) AS t
    USING [dbo].[tblDealerSalesVehicles] AS s
    ON (t.[AccountId] = s.[AccountId] AND t.[Vin] = s.[Vin])

将匹配每个现有记录并使用您的参数更新它。您可能想使用类似的东西:

MERGE 
    INTO [dbo].[tblDealerSalesVehicles] WITH (HOLDLOCK) AS t
    USING (VALUES (@parmAccountId, @ParamVin)) AS s (AccountID, Vin)
    ON (t.[AccountId] = s.[AccountId] AND t.[Vin] = s.[Vin])
WHEN MATCHED THEN
    UPDATE SET 
  ....etc