October 17, 2007 at 4:46 am
Hi Guys,
Recently I have noticed that a job that we are running is coming close to the threshold allocated for completion. Basically I have a job set-up that takes data from a table and validates it, processing each record in that table and updating tables as it needs to. The validation aspect of this is massive and there is no way that this component can be rewritten(even though it should be). I basically need to adopt a new strategy for executing the SPs that are there already.
The problem I have is that there are generally busy periods where performance goes down on this process. The job is set-up so that if the processing is not completed within a 5 minute period new records that have come in and are awaiting processing will not get processed until the existing job has finished. In busy times this can be detremental. Changing the time the job runs is not an option.
One solution that has been thrown around is using service brokers. I am currently reading up on these and discovering that this could be the best approach for me.
Any ideas that you guys may have is greatly appreciated.
thanks in advance,
Martin
October 17, 2007 at 5:36 am
Hi Martin,
assuming that your existing stored procedures can validate individual rows, the service broker could indeed help. You could send a message every time a new row is inserted, and this will be put in a queue. This queue will be automatically processed, and you could have several threads doing this processing (you can specify this in the MAX_QUEUE_READERS in the queue creation statement). Make sure that there are no conflicting sideeffects for the validating procedures, so that you are not forced to do sequential processing. Also, the max number of validation procedures are restricted this way 🙂
I can recommend Roger Wolter's book on Service Broker as a good starting point.
Regards,
Andras
October 17, 2007 at 5:36 am
If you think moving to Service Broker will save you from re-writing, you are pretty likely to end up surprised.
Without any details, I cannot be of too much help, but the advantage you typically get from using Service Broker is that you are able to multi-thread your process.
Being able to validate several of your records at the same time is probably a good idea, but if your current process is taking a single record, running a stored procedure, and then moving to the next record, you may find yourself in a resource or lock contention situation very quickly and this may take you a lot of time to resolve.
Do not get me wrong, I use Service Broker for a lot of things (probably a few I shouldn't). I have just never found a situation in which I had a long-running process that I could solve with it without a significant re-writing of the process.
October 17, 2007 at 6:19 am
Folks,
Thanks for your replies. Unfortunately due to the complexity of the system and the value of it(accepts all our payment) it cannot be rewritten so easily. It has been investigated extensively and the conclusion has been met that we just have to go with it as it is and try and find another method of executing this process without too much restructuring. I also may have understated the validation process, it is again another complex process as there are is a huge range of products and quantities that can be sold on a per customer basis. various discounts, languages etc...
Currently a DTS job runs every 5 mins do process the jobs but I think that a message broker type architecture would go a long way to making this more efficent. Using Message broker allows you to have a queue thats always open, something similar to a listener doesn't it?
Thanks once again for you help,
Any other areas that I should investigate are also appreciated.
kind regards,
Martin
October 17, 2007 at 6:42 am
Well the only thing I can suggest is - if you have the option to test - throw it out there with a decent plan on how to rollback to the old solution if this doesn't work. It's not clear to me that multiple instances of this process can't run without disrupting each other, which is something service broker needs, and without a solid analysis on the matter which only you can do, the only way to find out is to throw it at the wall and see what sticks.
Like always - find a way to have a limited test to see if it works at all (with a FEW concurrent threads/messages). Assuming that does work - then try it at a slow time. Anyway - you get the idea.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply