October 12, 2010 at 10:28 am
Is there a way to improve the performance of the following statement?
update tableA
Set SomeCounter = SomeCounter + 1
where primaryKeyID = @primaryKeyID
tableA has less than 1000 total records. My app currently executes this update statement
hundreds of times a second by hundreds of different connections/users in a web environment on the
same record.
Naturally, each subsequent update request has to wait for previous requests to finish. My gut
tells me to convert his process to inserting a record for each request and then running a scheduled
job to get the counts and update tableA with a single update statement for each record.
Prior to moving forward with that concept, is there a better alternative?
October 12, 2010 at 10:34 am
I can't hardly see this really locking up...
Are we seeing the whole procedure?
October 12, 2010 at 10:43 am
I have to agree with Ninja's_RGR'us. Assuming that the column primaryKeyID is indeed the primary key, this statement should run very fast with row level lock and shouldn’t cause a problem. If you see that it does block other users, check if the table has an update trigger that is slow and causing blocking problems.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 12, 2010 at 10:53 am
Well 2 things.
One thing that will likely cause a lock is to do update... then select where.
Depending on the transaction isolation levels that can lock it up enough to slow you down.
You can remedy that one with the syntax Column = @Var = Column + 1.
#2, the hardware is just not strong enough and you need to upgrade.
All the above assumes that the PK is actually indexed correctly.
October 12, 2010 at 11:02 am
The table and the procedure really are this simple. the primary key is a clustered index on a single column.
Keep in mind, these are thousands of simultaneous updates to the exact same record all at the same time.
October 12, 2010 at 11:16 am
Then you need more hardware power...
Checkout where the server is waiting (IO, RAM, CPU). You'll likely find the problem in that order.
It's also possible to do the insert solution as you stated and then run a proc 24/7 to update the base table. The real question here is how "live" you need this data to be. If it's only for reporting then maybe compiling the data only daily or horly can make sens.
October 12, 2010 at 11:18 am
robbemorris (10/12/2010)
The table and the procedure really are this simple. the primary key is a clustered index on a single column.Keep in mind, these are thousands of simultaneous updates to the exact same record all at the same time.
Is this application running on a web [farm] server?
Then maybe you can save that data in the webserver's session variables of flat file.
From there import every few minutes rather than live. It's going to be much faster anyways.
October 12, 2010 at 11:24 am
I guess I was looking for some insight into the likelihood of the waits that must occur while each subsequent update request (and lock on the record) on the exact same record. I'm seeing timeouts spuratically on this specific procedure but virtually no timeouts on any of the other 100 hundred stored procedures. Checked the fragmentation of the primary key index and it is fine. So, for such a small table, I could only surmise the issue was thousands of simultaneous requests to update the same record was the issue.
This procedure is essentially an ad impression counter. Figure two to three requests per page load with thousands of simultaneous requests. It adds up. Perhaps this could be a connection pooling issue or something else.
I'm pretty confident the hardware is more than capable of handling the load. The CPU utilization across the various CPUs is rarely above 40%. Memory is upwards of 6GB dedicated to SQL Server.
October 12, 2010 at 11:28 am
Can we see a little more info about the table design and which table is having the issue?
Maybe you could go around the issue with other tools of sql server.
And I still need the info about how live you need this data to be?
Can you have a few minutes of lag between real data and reported data?
October 12, 2010 at 11:51 am
The table really is just:
PrimaryKeyID uniqueidentifier
Description varchar(200)
CreateDateTime datetime
PageViewCount bigint
It is essentially a lookup table that has next to nothing for live queries run against it (they are held in a static collection in memory of the app at runtime). The update statement I mentioned above just updates the PageViewCount. PageViewCount is not indexed. Just the primary key column.
It is a nice to have for it to be 100% live data but not an absolute must. I have a feeling I'm going to need to move to some sort of logging table and then perform a job to update the counts versus doing the count updates live. There are just too many simultaneous update requests for the same record.
October 12, 2010 at 12:04 pm
Any way to move the PK from GUID to TINYINT or SMALLINT?
That would give "less" work for the server as the key would be ±18 times smaller.
I know it's not much but it's better than nothing and times 1000 / sec it might just be the edge you are searching for.
October 12, 2010 at 12:11 pm
Also I guess that you don't need a Datetime for the create date.
Smalldatetime might save you a few page reads here and there as well.
I know this seems insignificant but it adds up. I was at Pass a few years back and the MySpace.com Chief DBA was telling a story about a query optimisation project. The query was running millions of times per day (if not per hour). What they did is change from SELECT MAX() to SELECT TOP 1 ORDER BY.
That tiny weenie change meant a few MS (MICRO seconds saved per executions).
But by the end of the day it mean a few minutes of processing and less locking.
And by the end of the year it meant a few less servers to install and configure :w00t:.
Assuming 1M execs per hour and 0.1 Millisecond less per run that makes up for 40 minutes of processing per day saved... and quite possibly a little less locking.
October 12, 2010 at 12:20 pm
Little late in the game for that. I could test it out I suppose but doubt it fixes the problem especially if traffic continues to increase. That table has 20 records in it right now and the index has no fragmentation. If it is slow now, it is only going to get worse regardless of the data type.
Oh well, worth a shot asking folks who deal with these kinds of details every single day. Had a feeling I was SOL... Off to work up a logging mechanism and update stat job.
Thanks for the insight folks...
October 12, 2010 at 12:48 pm
Hence you'll need more hardware....
Or a difference solution.
40% is not "much" but as soon as you get a burst or cpu heavy query you're toasted.
But using a tinyint here wouldn't be a bad test. It might give you a few extra months before reengineering everything.
I'm running a few tests on our test servers right now but my idea seems promising.
October 12, 2010 at 12:58 pm
I won't have time to finish those tests but I seem to be getting as much as 10-15% throughput increase with that little change and 1M updates in 2 minutes on a slow prod server (5 concurrent connections)... that's well over 8000 updates / second and I'm talking about a single processor quad core xeon machine... not a monster by any means.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply