Options for single threaded worker task
I'm working on an application that currently does the following:
1. User performs task that must update a table B "post commit" by
inserting into table A. This update must be performed by a single
process (similar to an ATM application where account balances are
being updated). The work to be performed on table B is read from a
work queue table, the data is placed into the work queue table via a
trigger on table A.
2. Every 60 seconds a job is scheduled to run a stored procedure to
perform the update of table B by reading from the work queue table.
The drawback to the above is the amount of time (lag) between the job
running. It's currently set at 60 seconds. I believe this paramater
could be reduced (along with reducing my JOB_QUEUE_INTERVAL).
Are there other techniques that I could employ to reduce the amount of
time between updates?
Thanks in advance,