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

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.

  • Good point about the index. Definitely check if there is an index on StaffID! – Tibor Karaszi Jun 27 '18 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 Jun 27 '18 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 Jun 27 '18 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. – Aaron Bertrand Jun 27 '18 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 Jun 27 '18 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 ?

  • 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 Jun 28 '18 at 2:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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