Stored Procedure to update table incredibly slow. Why?

Question

I have a simple stored procedure that takes a bunch of parameters to update an existing record within a table. Creating and deleting the record with other stored procs takes very little time to execute. However, executing the update stored proc takes up to 60 seconds and I can't fathom why.

I've tried the proposals from this SO article, but without any luck: SQL Server: Query fast, but slow from procedure

Here is the original SP:

CREATE PROCEDURE [dbo].sp_Update
        @OwnerId uniqueidentifier,
        @DealId uniqueidentifier,
        @Title nvarchar(250),
        @Description nvarchar(MAX),
        @ProjectValue money,
        @Country nvarchar(250),
        @CountryRegion nvarchar(MAX),
        @WorldRegion nvarchar(250),
        @MarketSector nvarchar(250),
        @ProjectStage nvarchar(250),
        @CreationDate datetime,
        @ExpiryDate datetime,
        @ImageFilePath nvarchar(max),
        @IsActive bit,
        @IsDeleted bit

AS
BEGIN

    update SomeTable set 
    OwnerId = @OwnerId, 
    Title = @Title,
    Description = @Description, 
    ProjectValue = @ProjectValue, 
    Country = @Country, 
    CountryRegion = @CountryRegion, 
    WorldRegion = @WorldRegion, 
    MarketSector = @MarketSector, 
    ProjectStage = @ProjectStage,
    CreationDate = @CreationDate, 
    ExpiryDate = @ExpiryDate, 
    ImageFilePath = @ImageFilePath, 
    IsActive = @IsActive,
    IsDeleted = @IsDeleted

    where DealId = @DealId
END

After trying ALL of the suggestions in the above article, one by one, I ended up with this:

CREATE PROCEDURE [dbo].sp_Update2
        @OwnerId uniqueidentifier,
        @DealId uniqueidentifier,
        @Title nvarchar(250),
        @Description nvarchar(MAX),
        @ProjectValue money,
        @Country nvarchar(250),
        @CountryRegion nvarchar(MAX),
        @WorldRegion nvarchar(250),
        @MarketSector nvarchar(250),
        @ProjectStage nvarchar(250),
        @CreationDate datetime,
        @ExpiryDate datetime,
        @ImageFilePath nvarchar(max),
        @IsActive bit,
        @IsDeleted bit

WITH RECOMPILE
AS
BEGIN
        set quoted_identifier off
        SET ansi_nulls on

        Declare @tempOwnerId uniqueidentifier
        Declare @tempDealId uniqueidentifier
        Declare @tempTitle nvarchar(250)
        Declare @tempDescription nvarchar(MAX)
        Declare @tempProjectValue money
        Declare @tempCountry nvarchar(250)
        Declare @tempCountryRegion nvarchar(MAX)
        Declare @tempWorldRegion nvarchar(250)
        Declare @tempMarketSector nvarchar(250)
        Declare @tempProjectStage nvarchar(250)
        Declare @tempCreationDate datetime
        Declare @tempExpiryDate datetime
        Declare @tempImageFilePath nvarchar(max)
        Declare @tempIsActive bit
        Declare @tempIsDeleted bit

        set @tempOwnerId = @OwnerId
        set @tempDealId = @DealId
        set @tempTitle = @Title
        set @tempDescription = @Description
        set @tempProjectValue = @ProjectValue
        set @tempCountry = @Country
        set @tempCountryRegion = @CountryRegion
        set @tempWorldRegion = @WorldRegion
        set @tempMarketSector = @MarketSector
        set @tempProjectStage = @ProjectStage
        set @tempCreationDate = @CreationDate
        set @tempExpiryDate = @ExpiryDate
        set @tempImageFilePath = @ImageFilePath
        set @tempIsActive = @IsActive
        set @tempIsDeleted = @IsDeleted


    update SomeTable set 
    OwnerId = @tempOwnerId, 
    Title = @tempTitle,
    Description = @tempDescription, 
    ProjectValue = @tempProjectValue, 
    Country = @tempCountry, 
    CountryRegion = @tempCountryRegion, 
    WorldRegion = @tempWorldRegion, 
    MarketSector = @tempMarketSector, 
    ProjectStage = @tempProjectStage,
    CreationDate = @tempCreationDate, 
    ExpiryDate = @tempExpiryDate, 
    ImageFilePath = @tempImageFilePath, 
    IsActive = @tempIsActive,
    IsDeleted = @tempIsDeleted

    where DealId = @tempDealId

    set quoted_identifier off
    SET ansi_nulls on

END

Note the funny positioning for the ANSI_NULLS and QUOTED_IDENTIFIER inside the proc as well as CREATE instead of ALTER because of Visual Studio 2015's inability to cope with the commands as you probably would in SSMS.

I'm at a loss as to how to speed this up and don't want to go down the route of turning it into a manually concatenated string as some of the params can accept characters that would render the query unsafe (i.e. single quotes, etc)

I'm also calling the execution of the stored proc via Dapper in C# .NET, if that makes any difference.

Any ideas?

UPDATE: Here's the Table Definition with current indexes:

CREATE TABLE [dbo].[SomeTable] (
    [DealId]        UNIQUEIDENTIFIER NOT NULL,
    [OwnerId]       UNIQUEIDENTIFIER NOT NULL,
    [Title]         NVARCHAR (250)   NOT NULL,
    [Description]   NVARCHAR (MAX)   NOT NULL,
    [ProjectValue]  MONEY            NOT NULL,
    [ProjectStage]  NVARCHAR (250)   NOT NULL,
    [Country]       NVARCHAR (250)   NOT NULL,
    [CountryRegion] NVARCHAR (MAX)   NULL,
    [WorldRegion]   NVARCHAR (250)   NOT NULL,
    [MarketSector]  NVARCHAR (250)   NOT NULL,
    [ImageFilePath] NVARCHAR (MAX)   NULL,
    [CreationDate]  DATETIME         NOT NULL,
    [ExpiryDate]    DATETIME         NOT NULL,
    [IsDeleted]     BIT              DEFAULT ((0)) NOT NULL,
    [IsActive]      BIT              DEFAULT ((0)) NOT NULL,
    PRIMARY KEY CLUSTERED ([DealId] ASC)
);


GO
CREATE NONCLUSTERED INDEX [deal_owners]
    ON [dbo].[SomeTable]([OwnerId] ASC);


GO
CREATE NONCLUSTERED INDEX [deal_ids]
    ON [dbo].[SomeTable]([DealId] ASC);

Stored Procedure to update table incredibly slow. Why?

Answers (1)

Related Questions