SQL Server Performance

very slow update

Discussion in 'T-SQL Performance Tuning for Developers' started by mmoss, Aug 24, 2005.

  1. mmoss New Member

    Hi,
    I am not sure if this is the correct place for this message, please correct me if I am wrong...


    I have a small 5 user application that seems to go along just fine for a while and then suddenly updating using the proc or direct SQL can slow down to over 3 minutes to do the update. Once myself or any other user successfully saves a record, all other calls to the proc happen in less than 1 second. Everything might be just fine for several days and then it happens again. The users are very frustrated. I tried converting the PK index to a clustered index but it did not help. I adjusted the autogrow amount to be a substantial amount in case It was growing frequently. I did a execution plan on my update statement and I did not see anything unusual - no table scans. The table is a little wide, maybe about 25 columns and under 10,000 records. The update is very straightforward.

    here is the gist of the code...

    if isnull(@PK,0) >0

    update tablename
    set field1 = @field1
    , field2 = @field2
    ...
    where PKID = @PKID

    else

    select @PK=max(pk) from ....
    select @PK = isnull(@PK,0)+1


    insert into tablename
    (PK, field1, field2, field3...)
    values
    (@PK, @field1, @field2, @field3...)


    You see, the proc is very simple. It either updates a record or inserts a record depending on whether or not the client app passed a value in the @PK stored proc parameter.

    99 times out of 100 this proc runs in less than 1 second. But when it takes a long time it will not be fast again until someone waits out the 2 to 5 minutes for the update to complete. My users are getting very frustrated and I am pulling out my hair trying to figure this out.


    Any Help is very much appreciated!

    Mike



  2. satya Moderator

    How many rows are getting updated with this statement?
    The clustered index on the underlying table will have slow performance affect if you're trying to update it frequently.

    Try to recompile involved stored procedures and reindex too in order to get optimum performance intermittently.

    The most common performance problems are associated with the time it takes to accomplish insert update and query tasks. Updating and inserting probably are most irritating to users because they are more closely associated with the user's time. Querying has a lower incidence of user interaction and lower expectations than a save for insert or update. SQL Server offers two great tools to help you reach your database optimization goals, System Monitor and SQL Profiler.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. mmoss New Member

    Hello,
    I am updating or inserting a single record at a time. Only 7500 records have been added to the database by the users in the past 8 months .


    I have tried reindexing and recompiling. It's a little hard to tell if there was any effect because once the proc gets executed, it will continue to execute fine for a few days.

    Whenever one of the user complains that the saving is "hanging" I execute a saved snippet of code with hard coded values that I just open in Query Analyzer and execute. The hard coded snippet looks something like:

    update customers
    set firstname = 'Mike'
    , lastname = 'Smith'
    , address = '123 main st'

    ...
    where customerid = 100


    this simple code, which is very much like the code in the proc can take 3 minutes or more to execute, but once it executes the users can perform saves in the app which calls a proc which does a similar update on the same table. The users saves will now happen instantly when they click save...for a while anyway.

    I will take a look into System Monitor and SQL Profiler tomorrow when I get into work.

    Thanks for replying!

    Mike

  4. mmarovic Active Member

    1. Do you autoshrink that db?
    2. In which case @pk value can be null?
    3. Do you have explicit transaction?
    4. What isolation level do you use?
    5. Why don't you use identity column as primary key?
  5. Adriaan New Member

    Can you tell if the slow update corresponds to when @PK is null or zero?

    In that case, is there any kind of index on the "PK" column referred to in this line:

    select @PK=max(pk) from ....

    You might also add a WITH (NOLOCK) hint to that query.

Share This Page