aspose file tools*
The moose likes Java in General and the fly likes Best way to connect/save to two Databases on one page? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Java » Java in General
Bookmark "Best way to connect/save to two Databases on one page?" Watch "Best way to connect/save to two Databases on one page?" New topic
Author

Best way to connect/save to two Databases on one page?

Robert Paris
Ranch Hand

Joined: Jul 28, 2002
Posts: 585
I have a web page (JSP) where a user will enter information. This info then has to be saved to two different databases (the databases are different types - mySQL and SQL Server - and on different servers).
What is the best way to do this? I feel like it will be a huge performance hit to be dong two inputs every time, but I can't think of a way around this. Is there one?
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
Given your performance focus, I assume you're already using connection pooling. In that case the overhead isn't too bad.
Besides, the first rule of optimization is: Don't.
If, after profiling (see the third rule of optimization) you have shown and know for sure that the concurrent database updates are your performance bottleneck, you could do the two updates in different threads; as database access is mainly I/O bound, that will reduce the extra overhead to practically zero. Do use a thread pool in that case, though, or the thread creation overhead might kill you.
- Peter
Robert Paris
Ranch Hand

Joined: Jul 28, 2002
Posts: 585
The problem with Threads is that the JSP page will return right away. If there's a db/connection error, I have no way of notifying the user and getting them to try again. They'll think everything went through fine and probably leave the site.
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
Why? Thread.join() is your friend. Not that I really think you should do this.
- Peter
Gopi Balaji
Ranch Hand

Joined: Jan 23, 2003
Posts: 84
Robert,
What if you use a persistent middle layer - a Message Oriented Middleware?
The JSP page can use an already created (static) queue to put in its message. If the messages are successfully put in the queue, you can assume transaction success. At the other end of the queue, you can have a process which polls the queue (or, subscribes to it), and then insert the data in the two (or three, or ..) databases.
This method could be significantly faster than direct DB I/O and multi-threaded DB I/O.
-GB.
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
Originally posted by Gopi Balaji:
What if you use a persistent middle layer - a Message Oriented Middleware? [...] This method could be significantly faster than direct DB I/O and multi-threaded DB I/O.
Unlikely; your typical run of the mill persistent queue implementations persists into an underlying RDBMS, so you're essentially doing the same thing but with an extra layer on top: likely to be slower rather than faster.
A non-persistent queue, however -- be it a noddy little queue you wrote yourself or a full-blown non-persistent JMS queue -- is another matter. It wouldn't be faster than using threads in terms of throughput, but the queue would allow some evening out of the load and it is likely to be a lot safer and more convenient. Not a bad idea, but it doesn't address Robert's error handling concerns.
- Peter
[ January 24, 2003: Message edited by: Peter den Haan ]
Robert Paris
Ranch Hand

Joined: Jul 28, 2002
Posts: 585
So does anyone have an answer for me on this? i.e. error-handling? Some way that I can have good performance, but somehow also let the user know if it failed to go into one or other of the databases?
If the errors didn't matter than this is easy, just use Threads, but if some piece of data doesn't go in, I may need to get it from the user differently to make it go through. Using threads, the JSP page will return without waiting for the database.
Gopi Balaji
Ranch Hand

Joined: Jan 23, 2003
Posts: 84
Originally posted by Peter den Haan:
Unlikely; your typical run of the mill persistent queue implementations persists into an underlying RDBMS,

Typically, queue implementations dump messages in hex format into the file system - either in a single file (not an RDBMS though), or each message in a separate file.
Originally posted by Peter den Haan:

so you're essentially doing the same thing but with an extra layer on top: likely to be slower rather than faster.

Not the same thing. Even if a queue implementation uses a Database, it is not an RDBMS to which you are connecting through JDBC / vendor dependent Java libraries. A RDBMS access involves (extra) overhead of :-
1. per transaction security (it is per connection when using MOM),
2. logging (mostly done by a single thread in standard implementation of commercial databases),
3. SQL parsing and compilation time (if you opt for a stored procedure, you have to consider binding/rebinding issues)
4. data integrity checks.
5. others which experts can add here.
Originally posted by Peter den Haan:

A non-persistent queue, however -- be it a noddy little queue you wrote yourself or a full-blown non-persistent JMS queue -- is another matter.

Non-persistent queue will not add any value in this particular solution. Rather, it defeats the purpose of assuring persistence.
Originally posted by Peter den Haan:

It wouldn't be faster than using threads in terms of throughput, but the queue would allow some evening out of the load and it is likely to be a lot safer and more convenient.

-
Originally posted by Peter den Haan:

Not a bad idea, but it doesn't address Robert's error handling concerns.
- Peter
[ January 24, 2003: Message edited by: Peter den Haan ]

But it does! If the message is in the queue, you are assured that your message is persisted, and is lost in case of a hard disk failure only (the same can happen to a DB too). The Web page can assume that a message into the queue is a successful transaction. At the other end of the queue, you can have light weight Java application(s) pulling off messages and
1. inserting them into multiple DBs,
2. sending emails, pagers, based on boundary conditions of data,
3. logging
4. creating reports
5. etc..
-GB.
Robert Paris
Ranch Hand

Joined: Jul 28, 2002
Posts: 585
But it does!

No it doesn't. While I'm assured the message will eventually attempt to speak to the database, again i have no way of letting the user know if something they entered is unacceptable and needs to be changed.
For example, let's assume everything passes the javascript format checks. Now when it gets to the database, there's a field that says, "If there are more than 20 people who have entered 'YES' for this field, then reject this person."
If I have used a messaging system, how will the person ever know they were rejected?
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
Originally posted by Gopi Balaji:
Typically, queue implementations dump messages in hex format into the file system - either in a single file (not an RDBMS though), or each message in a separate file.
It depends on how persistent your messages need to be. The implementations I'm familiar with are still most robust when backed by a JDBC data store. If you don't mind that when the drive goes, the messages go with it, then yes, you are right: by all means use flat files. It's faster.
Even if a queue implementation uses a Database, it is not an RDBMS to which you are connecting through JDBC / vendor dependent Java libraries. A RDBMS access involves (extra) overhead [...]
I don't get your point, I'm sorry. What is it about the way JMS implementations use their RDBMS backing store that somehow makes them much more efficient than anything I could do myself? Provided that I don't make gross mistakes like not using a connection pool and prepared statements.
Non-persistent queue will not add any value in this particular solution. Rather, it defeats the purpose of assuring persistence. [...] But it does [address Robert's error handling concerns]!
Make up your mind: does it or doesn't it?

Sorry. Couldn't resist that. As I was discussing non-persistent queues throughout the second section, these were exactly the points I was trying to make.
If the message is in the queue, you are assured that your message is persisted, and is lost in case of a hard disk failure only (the same can happen to a DB too).
No, it can't. Any decent database will survive hard disk failure with all committed transactions intact provided you keep redo logs (journal). And backups, of course. That's why a JDBC backing store is usually the best choice for messages that need to be truly persistent.
You definitely have a very valid point that Robert may not at all need this level of robustness though.
- Peter
[ January 30, 2003: Message edited by: Peter den Haan ]
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
Originally posted by Robert Paris:
No it doesn't. While I'm assured the message will eventually attempt to speak to the database, again i have no way of letting the user know if something they entered is unacceptable and needs to be changed.
Well, they'd need to check back to see their status.
But it sounds like everything has to be done synchronously. The question is, do you want it to be done concurrently as well? You could use a thread pool and dispatch separate threads for the separate database updates. At the end you'd call Thread.join() to wait until all threads had finished their jobs. But please think twice before trying that. You'd introduce a lot of complexity, and is this really a performance problem?
- Peter
Gopi Balaji
Ranch Hand

Joined: Jan 23, 2003
Posts: 84
Originally posted by Robert Paris:

No it doesn't. While I'm assured the message will eventually attempt to speak to the database, again i have no way of letting the user know if something they entered is unacceptable and needs to be changed.
For example, let's assume everything passes the javascript format checks. Now when it gets to the database, there's a field that says, "If there are more than 20 people who have entered 'YES' for this field, then reject this person."
If I have used a messaging system, how will the person ever know they were rejected?

Agreed. What I say is ideal in a situation where you use the DB (in the web page) for write-only purpose. If you have to read the DB, and then based on that, decide to write / display to users, then MOM will not do it.
But, when you say -
when it gets to the database, there's a field that says, "If there are more than 20 people who have entered 'YES' for this field, then reject this person."
, isn't your business logic overflowing into the data layer?
Anyway, this is new information (coders shout Requirements Change).. I thought you meant DB I/O errors?
-GB.
[ January 30, 2003: Message edited by: Gopi Balaji ]
Gopi Balaji
Ranch Hand

Joined: Jan 23, 2003
Posts: 84
Peter - The implementations I'm familiar with are still most robust when backed by a JDBC data store.
GB - Let me know of such implementations. I am aware that IBM MQ Series, MS MQ, and Tux use the file system. Do those implementations by default use the RDBMSes, or do they have to be specially configured to persist messages in an RDBMS? But, then, wont performance be hit, if you use, say, an Oracle DB to store messages from MQ Series?
Peter - I don't get your point, I'm sorry. What is it about the way JMS implementations use their RDBMS backing store that somehow makes them much more efficient than anything I could do myself? Provided that I don't make gross mistakes like not using a connection pool and prepared statements.
GB - When I said MOMs may use DBMSes, I (thought) I stressed on *non-relational* DBMSes, as oppossed to RDBMSes. Non-relational DBMSes using proprietary format, to enhance speed.
Peter - Make up your mind: does it or doesn't it?

GB - Non-persisted queues *does not* allow assured persistence. But, the queue solution *does* solve R.'s problem of error-handling (though this is not valid in the changed context where R. needs to read info from the DB to perform error-handling. Error-handling to me, meant DB I/O error-handling).
Peter - No, it can't. Any decent database will survive hard disk failure with all committed transactions intact provided you keep redo logs (journal).
GB - If a hard-disk fails, and you are able to recover t-logs from it, there is nothing preventing you from recovering the DB files themselves. A complete hard disk failure (for example - magnetic polarization destruction (made it up ;-) )) would make the redo files useless. And hence, the "hard drive failure when using queues" situation is equivalent to this.
Peter - And backups, of course.
GB - Of course, if perpetual backups on remote media is done. Else, data between the last performed backup and the mega hard disk failure (see above) is still lost.
-GB.
[ January 30, 2003: Message edited by: Gopi Balaji ]
[ January 31, 2003: Message edited by: Gopi Balaji ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Best way to connect/save to two Databases on one page?