Update performance

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

Do you have index or pk/uk constraint on id column? How many rows do you have?

Yes, I have an identity column that is the primary key....the id field.

Is this single column clustered pk?

No it is not...just a regular Identity column that increments.

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?

Yes, that is what I meant.

Ok, make it clustered, then please let me know if it helped.

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?

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?

We will, but for this testing, the update command is the only one running.

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?

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?

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.

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

Thanks for the help.

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.