Re: Update Performance

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: David Gugick (davidg-nospam_at_imceda.com)
Date: 01/10/05 

Date: Mon, 10 Jan 2005 15:22:50 -0500

Brian Linden wrote:
> 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.

What's in the table? In other words, how many rows? An index will only
help performance if there are enough rows to make it worthwhile. If you
have a many rows, consider adding an index to the id column.

Also, where is this update in the transactions that perform them? Is the
update wrapped up in a transaction that is also inserting/updating the
real data? If so, consider pulling the update of the counter out of the
main transaction. It sounds like you are dealing with a lot of blocking
problems on the tblStats table and that's the reason for the slowdown. 

-- 
David Gugick
Imceda Software
www.imceda.com

Relevant Pages

  • 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. ... If I comment out this line, I get about 350 Requests a second. ...
    (microsoft.public.sqlserver.programming)
  • Re: [2/3] POHMELFS: Documentation.
    ... it is possible to send multiple requests from ... When transaction is submitted, it ... to be read from different server or written to new one or dropped. ... hosta$ touch foo.txt ...
    (Linux-Kernel)
  • Re: Asynchronous database calls
    ... In the end I used MSQM: I put the requests in a queue and immediately return ... off the time consuming call to the middle tier and database. ... I would like to invoke a stored procedure and immediately free up ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Newbie Needs Design Help
    ... The replies do not always follow the requests (due to a preprocessor ... and replies from the institution back to the POS ... > a recorded transaction and you want to match up ... > Dim msg As String, ...
    (microsoft.public.vb.general.discussion)
  • Re: Simple (?) Singleton Question
    ... That page opens the resources it needs, ... The next page is another transaction and needs to do it's thing, ... This is very different from batch programs which can keep resources ... and can maintain resources across multiple requests. ...
    (comp.lang.php)