SQL Server Performance

Update performance

Discussion in 'Performance Tuning for DBAs' started by blinden, Jan 10, 2005.

  1. blinden New Member

    I have a stored procedure that is executed on every web request that we get.
    On the web server, I get about 50 Requests a second.

    After looking into the bad performance I narrowed it down to my update statement in my stored proc:
    Update tblStats set counter = counter + 1 Where id = @id

    If I comment out this line, I get about 350 Requests a second.
    That is the kind of performance that I need.

    It is crucial that I keep a real-time counter for business logic purposes.

    I don't have any indexes on the table that I am updating.
    Does anyone have any suggestions on what I can do to speed things up?

    Any suggestions would be appreciated.

    - Brian

  2. mmarovic Active Member

    Do you have index or pk/uk constraint on id column? How many rows do you have?
  3. blinden New Member

    Yes, I have an identity column that is the primary key....the id field.
  4. mmarovic Active Member

    Is this single column clustered pk?
  5. blinden New Member

    No it is not...just a regular Identity column that increments.
  6. mmarovic Active Member

    I'm not quite sure I've got it. I guess you have pk constraint defined on that column, but it is not clustered. Is it what you mean?
  7. blinden New Member

    Yes, that is what I meant.
  8. mmarovic Active Member

    Ok, make it clustered, then please let me know if it helped.
  9. blinden New Member

    I apologize, I got a little mixed up. There is an ID column that is the primary key and it has a clustered index.

    Is there any way that I can find out why it is taking so long to update?
  10. mmarovic Active Member

    Do you have frequent queries reading data from the same table? Have you measured performance on production system or you tried it on dev server? Have you checked if something else was running on the server while you tested performance?
  11. blinden New Member

    We will, but for this testing, the update command is the only one running.
  12. mmarovic Active Member

    How many rows do you have in this table? I'm running out of ideas. You can check clustered index fragmentation but i can't imagine that it can cause so big impact on single row update by clustered pk. Is it possible that the same row is updated frequently?
  13. blinden New Member

    There are only about 50 rows in the table.

    YES, the same row is updated every time.
    The table actually looks something like this:

    ID (int, primary key, clustered index)
    ReportDay (smalldatetime, '12/8/2005', 12/9/2005')
    Counter (int)

    Then the stored procedure updates like:

    Update tblStats Set Counter = Counter = 1 Where ReportDay = @TodaysDate

    So I guess I'm not using the ID in my query at all.
    There are very few inserts (about one a day) Should I think about indexing the ReportDay field?
  14. mmarovic Active Member

    quote:There are only about 50 rows in the table.

    YES, the same row is updated every time.
    The table actually looks something like this:

    ID (int, primary key, clustered index)
    ReportDay (smalldatetime, '12/8/2005', 12/9/2005')
    Counter (int)

    Then the stored procedure updates like:

    Update tblStats Set Counter = Counter = 1 Where ReportDay = @TodaysDate
    Now it becomes clear. You don't have update... id = @id, you have reportDay=@TodaysDate.

    quote:So I guess I'm not using the ID in my query at all.
    Right!

    quote:There are very few inserts (about one a day) Should I think about indexing the ReportDay field?
    You should drop id column if it is not referenced by fk from other table. Then you can make reportDay clustered pk. Not sure if it helps (there are only 50 rows), please test and let us know.
  15. blinden New Member

    It did speed things up a little bit....but still not acceptable, I'll have to keep researching.

    Thanks for the help.
  16. Adriaan New Member

    Updating rows takes more time than inserting.

    Why not just insert a new row with (max(Counter) for the same ReportDay) + 1. Then at set intervals delete all rows where exists a row for the same ReportDay with a higher Counter.

    You might consider putting the statistics table in a separate file, so it is handled by a different processor thread than the rest of the database read/writes, and you can let it grow without fear for the size of the data file.

Share This Page