• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Inserts using java or plsql

 
Ranch Hand
Posts: 67
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ratan Kumar
Ranch Hand
Posts: 67
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


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
Posts: 67
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


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've got no option but to sell you all for scientific experiments. Or a tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic