Optimisation question.

I have a table which is being used as a processing queue by a number of instances of an application. Each instance of the application finds the oldest untagged job in the queue, tags it, performs the appropriate calculation and updates the queue record to indicate that it has been processed, resetting the tag. A given record in the queue table will have upwards of 30 processing operations performed on it before it is finished and deleted from the queue table. Typically processing operations take less than one second to perform (about 5 per second) so the queue table is polled heavily (at an interval of about 1/10 seconds per instance of the application).

I am looking for ways to improve the scalability of the product without having to alter the application. Currently processing time degrades substantially with the addition of more processing applications, and I'm fairly sure that it is at least partly due to contention for the queue table. The database may be deployed on either Oracle 7/8 or MS SQL Server 6.5/7. I'm really looking for some physical database tuning suggestions.

Thanks, David. Received on Tue Jun 20 2000 - 00:00:00 CDT