The MERGE statement took too much time (for only one entry). (MS-SQL Server 2012)

Question

The problem I am facing is that this SQL Merge command took too long to execute? It still takes more than 30 seconds. We are using MS-SQL 2012 Server.

So, can anyone tell me what I did wrong with the MERGE statement and why?

Thank...

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

I do not think your MERGE statement is doing what you expect. You have the same source and target, without reference to the parameters, so this bit:

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