wood burning stoves 2.0*
The moose likes JDBC and the fly likes Inserts using java or plsql Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Inserts using java or plsql" Watch "Inserts using java or plsql" New topic
Author

Inserts using java or plsql

Ratan Kumar
Ranch Hand

Joined: Jul 19, 2006
Posts: 67
Hi,

I have an input file full of records. I need to perform some validation on data in this file and then load the data into the table 1 and then from table 1 to table 2. Later the data from table 1 gets deleted.

Java program loads the file into table 1. Then plsql loads table 2 from table 1.

I would like know which is the best place to do this validation.

1) should I perform validations on input file in java program which loading the records into table 1?

or

2) should I first load the table 1 using the java program with the data from file as it is. And perform validations in plsql while loading table 2 from table 1?

Which is the best way performance wise?

Thanks
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Why the intermediate step? Why can't you just load directly into table 2?


Which is the best way performance wise?

If performance is your only consideration, I'd look at SQL Loader instead or JDBC or PL/SQL.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Ratan Kumar
Ranch Hand

Joined: Jul 19, 2006
Posts: 67
Hi

I need a solution in the present set of conditions without using sqlloader. Also the flow should be table 1 and then table 2.

Please suggest which is the better way..validation check in java program or the plsql code? (performance wise)

Thanks
[ October 10, 2008: Message edited by: Ratan Kumar ]
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


Please suggest which is the better way..validation check in java program or the plsql code

This is subjective. Since the only requirement you state is performance, this will depend on the data, what your need to tdo to it and the environment. PL/SQL should in theory be faster to run than JDBC, assuming you have the database resource to do it. It may be there are more resources available to the Java environment than the database.

I a previous job I worked on a system where the bulk of the business rules were written in PL/SQL. This made for an odd architecture - the database servers were considerably more powerful than the application servers they sat behind (and the only thing the DB servers managed was our app). This architecture made it fairly expensive to upgrade - another Oracle licence as opposed to a new Linux box. You also have to coinsider maintenance - Java is much easier to debug and maintain than PL/SLQ.

I understand that sometime we just have to work with requirements as they are, no matter how odd they appear, but I'm still curious: if the only purpose of Table 1 is a staging area before the data is moved to Table 2, there is no transformation occuring during the transfer, and your only driver is performance, why does Table 1 exist?
Ratan Kumar
Ranch Hand

Joined: Jul 19, 2006
Posts: 67
Hi

Thanks for the reply. Yes, table 1 is only for the staging purpose and later the data in it gets deleted..

I was wondering if i do the validations in the java program , there will be hit on the performance due to mmuch i/o being done. And so I am thinking it would be good to do that in plsql.

Thanks
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


I was wondering if i do the validations in the java program , there will be hit on the performance due to mmuch i/o being done. And so I am thinking it would be good to do that in plsql.

The IO to upload the invalid/untransformed data will presumably result in more data being passed over the network? Unless your validation is a conversation with the database (i.e. do you need to select other data as part of your validation?) it makes sense to manipulate the data before uploading it (as SQL Loader does) to avoid the extra work in the database and the extra unnecessary traffic?
[ October 10, 2008: Message edited by: Paul Sturrock ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Inserts using java or plsql