1

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

3

3 Answers 3

2

If it's the first UPDATE that times-out then I'd look closer at your physical reads.

If [staffid] isn't indexed then you'll be performing a full table scan and pulling the entire table from the IO subsystem through to the buffer.

After the first UPDATE most of the table's pages will be in the buffer and subsequent UPDATES will be able to perform logical reads for the EXISTS part of the SP.

6
  • Good point about the index. Definitely check if there is an index on StaffID! Commented Jun 27, 2018 at 10:30
  • Hi pacreely, yes, my table does not have an index on StaffID. I have tried to index StaffID column and checked the execution plan, it seems the costing is worse than before, as it needs to do the 'clustered index update' (304%) and 'clustered index seek' (100%) AIK, indexing will slow down the update statement, is it a correct way to solve this problem?
    – Jay Chen
    Commented Jun 27, 2018 at 11:48
  • @JayChen a 'clustered index seek' is not a bad thing, it's way better than a 'table scan'. The only additional overhead is on the update, it's worth testing the query to get the actual IO and TIME. A half-way option would be to keep the table as a Heap and create a Non-Clustered index on staffid. You'll be able to perform RID lookups and have less update overhead. Also, is the staffid column an NVARCHAR?
    – pacreely
    Commented Jun 27, 2018 at 12:26
  • 1
    @JayChen Don’t put a lot of weight into the estimated cost % shown in an estimated plan. They can occasionally be useful guide posts but if you’re just looking at a single plan what is the value? Every query is going to have costs that add up to 100% of the cost. Run the query and see if it is faster. Commented Jun 27, 2018 at 12:33
  • @pacreely yes, it is a NVARCHAR type, should I change it to varchar? (unicode is not required for this column) Hi Aaron, Thanks, I will try and see how
    – Jay Chen
    Commented Jun 27, 2018 at 13:16
1

Nah, I would guss that StaffID is unique and if estimated row is 1 then it it pretty much on target. Perhaps blocking is the cause for the timeouts?

0

I can see you have a 'Photo' column there, you might want to check if there are no cases of 'overuse' of your system. I have seen systems, where there was no validation and a few people would drop in some e.g. 200MB photos and have your table grow very large, which could then increase the time needed to retrieve/update.

Also, do you have concurrent workload there ? And what is the isolation level your running at ?

1
  • Hi Blazej, we have restricted the size of photo to 10KB as maximum and this sp is triggered one by one in the foreach loop of application.
    – Jay Chen
    Commented Jun 28, 2018 at 2:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.