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