• Post Reply Bookmark Topic Watch Topic
  • New Topic

Java and DB Design problem.  RSS feed

 
Rakesh Jhamb
Ranch Hand
Posts: 154
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello All,

This is the current situation...of our application.


There is a DB. Data in the DB is populated from some external source that we cannot control. Right now we have a Java task configured that goes to DB every 2 minutes and process the rows that have arrived in DB.

So Java code is pulling information every 2 minutes.

In heavy load, this kind of architecture is not very much scalable for us, as in 2 minutes there can be high number of rows that might have arrived.

Is there some way, we can implement push behavior rather than the pull behavior, such that every time row arrives inside a DB, the Java process can be kicked off that will process the row that has just arrived.

Please provide your inputs on such scenario. We are open for any design changes

Thanks

 
Tim Holloway
Saloon Keeper
Posts: 18799
74
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Usually you'd do this by means of a trigger in the database.

Some DBMS's support embedded Java which could then be attached to the trigger.

However, if there is a massive amount of data arriving every 2 minutes, you might need something that can distribute the load. Alternatively, consider whether you really need to do that much work that often and if any of it can be reduced.
 
Rakesh Jhamb
Ranch Hand
Posts: 154
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Thanks for your reply.

There is not much issue with the load currently, the only requirement is that if we are somehow able to process the row as soon as it reaches DB, then we will have a much scalable solution.

Our end goal is to execute the process as soon as the row reaches DB.

We are using Oracle DB, hence we can invoke Java Program.

But then the question is: The whole Java project will have to reside on the same machine as the Oracle DB. As per my understanding Oracle will be able to trigger Java code only in the embedded VM.

Please correct if my assumptions are wrong.



 
Tim Holloway
Saloon Keeper
Posts: 18799
74
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, the triggered code has to be on the Oracle DB server, but that doesn't mean that the triggered code can't in turn trigger a separate application server.

A variety of mechanisms can handle that, including RMI, remote EJB (which is basically enhanced RMI), JMS, and even Web Services - although that's a lot of overhead for internal work.
 
William Brogden
Author and all-around good cowpoke
Rancher
Posts: 13078
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The advantage of JMS (Java Message Services) would be that the JMS server will maintain a queue of events asynchronously. This will prevent hanging either your program or the database triggered process when a change does not go through immediately.

Bill
 
Lucas Lech
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Maybe you could come up with a "hacked" setup as follows:

plsql would send a jms message with a range of row ids (start - end) to a dedicated jms destination (calling an embedded java program ?)
your "serverside" queue listener would pick that information up, and act accordingly - if the order of processing wasn't the issue you could
split the work, even between nodes
 
Rakesh Jhamb
Ranch Hand
Posts: 154
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks to all of you for your inputs.

Lucas, you have mentioned that plsql would send a jms message with a range of row ids (start - end) to a dedicated jms destination.

Do you intend to say that PL/SQL can directly send JMS message or it will do it with the help of a Java Program that will send message to the queue.

Also, the JMS model is a good proposal, but the problem is till now we have a simple jar doing the whole thing without any app server. If we introduce JMS, then we will have to introduce Messaging server and hence increased cost.

Please share your views.
 
Tim Holloway
Saloon Keeper
Posts: 18799
74
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Actually, high volume isn't the best use for messaging. There's less overhead for polling as long as the interval is acceptable - each message has overhead to set up, send, receive and process. Which is why Lucas was suggesting sending keys in batches. Messaging is optimal when you need "immediate" response to each transaction or the transaction rate is slow enough that polling overhead would exceed messaging overhead.

If you do want to go the JMS route, the JBoss and JOnAS open-source J2EE servers both support it. It's not "free", since someone has to set the server up and someone has to administer it. But at least there's no licensing costs unless you choose to buy support for them.
 
Lucas Lech
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Rakesh

The suggested route would be a plsql trigger invoking a java program (plsql wrappers around classes, research more on google, I'm just writing from the top of my head) which in turn would post the ids range onto a jms destination.

Messaging itself should not bring a performance penalty, especially if you're sending metadata & not the data itself. Since you're using a "simple jar" I would suggest using an opensource, standalone jms provider, like activemq (http://activemq.apache.org/) which is fairly easy to setup & tune.

 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!