Re: Update Performance

From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 01/10/05 

Date: Mon, 10 Jan 2005 14:58:36 -0600

First thing I would do would be to add a unique index on the id column.
This should help out quite a bit, especially if you are modifying any other
rows in this table

The other obvious issue is that you are single threading all of your access
through this update. If you see the calls queuing up, then you may hve your
counter update inside a transaction and as such making the next update wait.

If the index doesn't help, consider having a table that instead of updating,
you simply write a new row in a table. This may actually cause more issues
with space, so you will need proper disk structures to help this, but it
should alleviate any single threading issues you are seeing. No you can
simply count the values when you need them. 

-- 
----------------------------------------------------------------------------
Louis Davidson - drsql@hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design - 
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in 
consulting services.  All other replies may be ignored :)
"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

  • Re: ThreadAbort Exceptions !! HELP !!
    ... Well we checked for queued requests last night and there were none during ... Looks like our SQL server box is ... trace seems to usually point at a webservice not an aspx page. ... System.Threading.WaitHandle.WaitOne(Int32 millisecondsTimeout, Boolean ...
    (microsoft.public.dotnet.framework.webservices)
  • Re: SQL Server 2000 and Latching problem
    ... these requests. ... How much ram on the SQL Server? ... > I have an application that acts as a database "server" where it captures ... > clients requests have to be queued. ...
    (microsoft.public.sqlserver.connect)
  • SQL Server 2000 and Latching problem
    ... as the clients could talk to the database directly. ... anything if the SQL server is dedicated or not. ... Are these mostly read only requests or do they involve Inserts, ...
    (microsoft.public.sqlserver.connect)
  • Re: Linking SQL Server tables
    ... The linked table will be read only if there is no unique index or constraint ... >>> I'm trying to link tables from SQL Server to my Access 2003 database. ... >>> in the Microsoft SQL Server database. ...
    (microsoft.public.access.externaldata)
  • Re: 2 NULLs violate Uniqueness?
    ... This is a detail where SQL Server does not follow the SQL standard. ... Create a view of the nonnull items and put a unique index on it: ... Use a computed column that equals the primary key when F is null, ...
    (microsoft.public.sqlserver.programming)
Loading