Design Question Request-Response (Database Polling)

[whitesox99]

Hi,

I need to write a program that will basically look for any records being added in a Database table e.g. Requests. If there are any records picks them up one by one and calls a Web Service gets a response and inserts in some other table e.g Response, updates the column in Requests table so next time the same record is not picked up again. Before picking up the next record waits for 3 seconds and if there are no records still keep polling the table every 3 seconds. Now the issue is I may have over 8000 records that needs to be processed in a reasonable time and am trying to figure out a better solution so that all my records gets processed in a reasonable time, the Web Service may take 3-5 seconds to send the response back. I will have this program to run as a windows service all the time. Just trying to find out if it's ok to poll on the database to check for new records and in case of errors how do I make sure my program that runs as a service keep running. Or what else can be a better solution.

I did think of using Advanced Queues as I have an Oracle App. Server. As I haven't used AQ before so am just curious on how to keep track of the messages i.e. if my MDB picksup a message and then it fails due to something how do I again pickup the message does it goes away from the queue once read, is there some kind of retry or something that only remove or mark the message read if my request/response cycle succeeds. As I don't want to delete my request data from the table but just need to mark some field in the table that marks the message as read so it's not picked-up again next time and also if I want to send the same data again I can just go to the table and change the status of the column so that it gets picked-up again for processing. As I haven't used AQ's before so wondering if it does provide this kind of functionality. Any small tutorial will help how to setup AQ's will help a lot.

In my scenario my batch process run every night i.e. a scheduled job that dumps the records in a Request table and there can be any number of records i.e. 5000+. From there I need to process all the records and send them to a Web Service that responds back and insert the response in a separate table. The same process can be run in a real time on demand i.e. from a UI a user can select the submit button and behind the secenes the same route is taken inserts a record in a request table and gets the response the whole cycle takes arround 5-8 seconds. Once a response comes user go to a diffreent screen and sees the response. My concern is in a batch mode when I have so many records that all needs to be processed within a reasonable time. So wondering what is my best way to do this. If any request fails wants to basically also retry or the user can change the flag in for those records in a request table so they are automatically send again.

Any help in this is really appreciated. Using Queues I don't know how the retry works or how I send the same record again if a flag is changed in a table. If using a program to poll on a table is there be any issues with the database connections and using threading if I have 8000+ records what are chances of failure etc.

Just looking for some bright ideas that anyone can share.

Thanks

 

[whitesox99]

Any feedbacks?

 

[stevexff]

Batching them up and calling a web service 5000 times seems like a really bad idea. Can you do it in real time, perhaps from a database trigger? Then as soon as you write the request row, the trigger can process it in a background task to call the web service and update the table(s). If you have a retry counter on the request table, you can increment it if the WS call fails, and have a cleanup task that runs every five minutes or so to sweep up any that have failed and retry them. You might like to configure it so that if they get retried more then n times, it alerts someone...



Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[whitesox99]

I am trying to avoid calling a web service from database so that's why I am having a table where wants some java application to poll and look for new records then pick them and call a web service for each record. It's third party web service and they only accept one record a time therefore if I have 5000+ records I have to call the web service that many times. I am trying to figure out how can I process multiple records a time in parallel to finish the processing of all the records in a reasonable time as each call to a web service may take arround 20 seconds to process. So if I have to process 5000+ records it will be hours by the time evrything is done so am trying to find out how else I can reduce the time and on the same hand keep track of any records failed etc. Any help is appreciated. Thanks

 

[stevexff]

OK. Here are some observations that might help.[ul][li]If the web service is that slow, then going flat out it can process 4320 per day. As you have 5000 per day, you already need some kind of multi threading or asynchronous behaviour to get the throughput.[/li][li]How many concurrent web service calls are your third party willing to accept? If you batch them up and try to pump them all through once a day, they may think you are mounting a denial of service attack on them...[/li][li]A queue might be a sensible option, as it will support transactional atomicity on the queue items, which solves the problems you might experience when polling a database table from multiple threads.[/li][/ul]

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[whitesox99]

Thanks,

I am trying to make it work thru. AQ and MDB. As I haven't worked with Advanced Queues before is there a small tutorial that can help me to start using MDB and AQ.


Thanks