I have a stored procedure which is called by Console Application (C#), the sp do nothing much just checking the existing of the record and update it:
ALTER PROCEDURE [dbo].[sp_Update_StaffTable]
@OldStaffId nvarchar(100)
,@NewStaffId nvarchar(100)
,@FirstName nvarchar(100)
,@LastName nvarchar(100)
,@Description nvarchar(100)
,@WorkPhone nvarchar(100)
,@Photo varbinary(MAX)
,@UserType nvarchar(100)
AS
BEGIN
SET NOCOUNT ON;
if EXISTS(SELECT [StaffId] FROM [dbo].[StaffTable_Temp] WHERE [StaffId] = @OldStaffId)
BEGIN
UPDATE [dbo].[StaffTable_Temp]
SET [FirstName] = @FirstName
,[LastName] = @LastName
,[Description] = @Description
,[WorkPhone] = @WorkPhone
,[Photo] = @Photo
,[UserType] = @UserType
,[StaffId] = @NewStaffId
WHERE [StaffId] = @OldStaffId;
END
END
The console application calls this sp and gets intermittent timeout issue when it updates the first record (we update for the whole table which contains around ~5k items).
I don't think 'parameter sniffing' is the root cause as the estimated number of rows is always = '1' for all scenarios, is it correct?
any help would be much appreciated