File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Java versus Oracle Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Java versus Oracle" Watch "Java versus Oracle" New topic

Java versus Oracle

Winston Smith
Ranch Hand

Joined: Jun 06, 2003
Posts: 136
Hi All,
I'd like to get some suggestions on the following scenario:
Basically, I have a web application which contacts an Oracle database to retrieve data, some of which has to be sorted or otherwise manipulated. Is it more efficient to read the data from the database, and do the sorting/searching/data manipulation with Java, or should I keep that logic on the Oracle side, and have Oracle perform necessary data manipulation, then feed my Java program the organized data? In a nutshell, when performing serious data manipulation, should it be kept on the Java side, or on the Oracle side?
Any suggestions are appreciated, thanks!

for (int i = today; i < endOfTime; i++) { code(); }
SJ Adnams
Ranch Hand

Joined: Sep 28, 2001
Posts: 925
my 2c: let Oracle do the work.
Dorothy Finkel-Laverty
Ranch Hand

Joined: Nov 24, 2001
Posts: 51
While I won't make suggestions on this scenario, because I don't think there is one and only one right answer, I can give you some questions to figure it out yourself in your specific environment.
How much volume of data are we talking about here? If you do the manipulation on the Oracle side, you could conceivably cut down on the amount of data you have to pass over the communication lines. Is that an issue for you?
Would you be doing the Oracle programming yourself? Where are your skills better?
Could some of these be stored procedures in Oracle, allowing you to take advantage of some of the processing efficiencies Oracle offers?
Will you have to run around to install stuff on client machines if you put the processing in the front end?
Are you doing this project "for real", or is it an academic exercise? If it's an academic exercise, I'd do it both ways.
There are a whole bunch more questions that could be asked, but I think this gives you a start.
Good luck!
Winston Smith
Ranch Hand

Joined: Jun 06, 2003
Posts: 136
Thanks for the responses. I'm about halfway through this project (real-world), and I'm doing data manipulation on both ends. The quantity of data is miniscule, so that's not a consideration. I was just wondering (possibly for future projects), which route would be better as far as data manipulation. Thanks again,
David Gallardo

Joined: Jul 07, 2003
Posts: 18
If you can do the data manipulation with SQL in a JDBC Statement (or PreparedStatement), you should do that. For example, you can sort and search using SQL. It's *much* faster and *much* easier that getting unordered, unfiltered data and sorting and searching in Java, because that's what a database is optimized to do.
In the rare instance that you feel you must do something programatically, you might want to double check with someone with more SQL experience, just in case there's are clever way to do it, using unions or nested queries, for example. If there isn't a way to do it with SQL, and performance isn't a big issue, try processing the data in your Java program, rather than adding an additional level of complexity by using stored procedures (either Java or PL/SQL).
If you seriously need performance, only then use stored procedures. (Depending on how much it interacts with the database, and how powerful the db server is, a stored proceduure can be an order of magnitude faster or more than an external program.) Java stored procedures are a bit slower than PL/SQL but they have the benefit that you can develop them outside the database, within your program and then later deploy in the database as stored procedures if it proves necessary for performance.
[ August 02, 2003: Message edited by: David Gallardo ]

Co-author: "Eclipse in Action: A guide for the Java developer"<br />Author: "Java Oracle Database Development"
I agree. Here's the link:
subject: Java versus Oracle
jQuery in Action, 3rd edition