Re: Update Performance

From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 01/10/05 

Date: Mon, 10 Jan 2005 15:23:04 -0500

Even if it is the only row in the table you should add a Clustered index on
[ID]. Make sure that you are not getting high disk queues for the drive
that your transaction log file is on. If you are and the log file is not on
it's own Raid 1 you should move it. You might also want to think about
caching this counter in the web server. That way you can increment the
counter without hitting the bak end and only do the update to the db once
every x hits. 

-- 
Andrew J. Kelly  SQL MVP
"Brian Linden" <brian@ineedadip.com> wrote in message 
news:%23cw%235C19EHA.2608@TK2MSFTNGP10.phx.gbl...
>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.
>
>

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: araneida logging
    ... > I'm trying to log the requests that my web server gets. ... > Though the log file doesn't get created. ... ASCII ribbon against html email (o_ the computer industry all around ...
    (comp.lang.lisp)
  • Re: Integrity of Log Files
    ... content from a web server that has been seized -- in a log file they ... I imagine the lawyer will advise the defendant of the difference ... in the threshold of a civil vs a criminal case. ...
    (comp.security.misc)
  • Re: Macro code
    ... >> All a web server is, is a program that accepts incoming connection ... >> requests, grants a connection, and satisfy's requests. ... the array index to designate which thread should use it. ...
    (comp.os.vms)
  • Re: Only 2 concurrent connections with window.open()?
    ... I am using client side JScript in Internet Explorer 6 SP1 to use a page to ... new browser windows get opened as expected, and it is evident that all are ... Active Server Pages 'Requests Executing' and 'Requests Queued' counters on ... client browser at any one time, i.e. they never reach the web server until ...
    (microsoft.public.scripting.jscript)