Re: Optimisation question.

A few suggestions:

1. have you tried pinning the queue table into memory? That might improve things.
2. have you looked at any tuning statistics? Is there contention in your db?
3. have you examined all SQL statements in your application and tuned them?

While you said that you don't want to alter the application, you might want to consider using Advanced Queueing (available in Oralce 8 on up).

HTH,
Brian

David Pattinson wrote:
>
> 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.
 

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================