Design Question (Database Polling)

[Mike Boota]

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. 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 every 3 seconds 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.

Thanks

[Jesper de Jong]

So you have a database table that contains a kind of queue with requests that have to be processed.

More suited for this kind of problem than a database is message queueing software (for example IBM Websphere MQ or Oracle Advanced Queues). To handle the messages, you would write a message-driven EJB that you deploy in a Java EE application server. The app server will call your EJB whenever a message arrives on the queue. The Java API for working with message queues is called JMS (Java Messaging Service).

Ofcourse you'll need to know Java EE and you'll need a Java EE application server for this solution - but it's most likely more robust, efficient and scalable than a simple Java program that you write yourself from the ground up.

[Mike Boota]

Thanks,

I did think of using Advanced Queues as we have an Oracle App. Server. As I haven't used AD 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 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.

Thanks

[Deepak Bala]

I wrote a batch process once that did this. I didnt use a message driven queue though. We didnt have too many records to process anyway. Why are you waiting for 3 seconds everytime you process a record ? Cant you process them all in a bunch ? Perhaps you could thread your calls to the web service and let it do all the work. Your threads will have to wait for a response from the web server. May be you could limit the number of active threads at any point of time to 20 ? Just some thoughts.

It would be better to go for a messaging system in your case when there are so many records to manage. Most messaging servers let you specify the number of retries for failed messages. After this they get deleted or get sent to a seperate queue. Messages that have no takers in IBM MQ for example - go into a dead letter queue.

[Mike Boota]

Thanks,

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 s there be any issues with the database connections and using threading if I have 5000+ records what are chances of failure etc.

Thanks

Thanks

[Mike Boota]

Any feedback is appreciated.

Thanks