I have a column in the database which keeps counts of incoming requests, but updated from different sources and systems.
And the incoming requests are in thousands per minute.
What is the best way to update this column with the new request count?
The 2 ways at the top of my head are -
Read current value from column, increment it by one, and then update it back(All part of a sproc).
The problem I see with this is that every source/system that updates needs to lock this column and this might increase the wait time of read and updating of the column. And will slow down the DB.
Put requests in a queue, and a job reads the queue and updates the column, one at a time. This method looks safer, atleast to me, but is it too much work to get a count of requests coming in?
What is the approach you would typically take in such a read & update in a column in huge amounts scenario?
1000s per minute is not "huge". Let's say its 10k per minute. That leaves 6ms of time per update. For an in-memory row with a simple integer increment and not too many indexes expect <1ms per update. Works out fine.
So just use
UPDATE T SET Count = Count + 1 WHERE ID = 1234
Put an index on the database and just do:
update table t set request_count = requestcount + 1 where <whatever conditions are appropriate>;
Be sure that the conditions in the
where clause all refer to indexes, so finding the row is likely to be as fast as possible.
Without strenuous effort, I would expect the update to be as fast enough. You should test this to see if this is true. You could also insert a row into a requests table and do the counting when you query that table.
inserts are faster than
updates, because the engine doesn't have to find the row first.
If this doesn't meet performance goals, then some sort of distributed mechanism may prove successful. I don't see that batching the requests using sequences would be a simple solution. Although the queue is likely to be distributed, you then have the problem that the request counts are out-of-sync with the actual updates.