• 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

Using JDBC versus Stored Procedures

 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello - I am currently in a dilema, and don't have much time to benchmark different approaches.
Here is my problem:
I am reading in a flat file (approximately 100,000 records) into my Java application, processing this file (performing specific business logic) and then need to update my Oracle database with these records.
Unfortunately, I have many foreign keys set up in Oracle on the tables that I need to update/insert, so I would need to check these keys before performing an Insert/Update (Many JDBC calls).
My first approach was to write these records from Java to a staging table and having stored procedure perform the check on the constraints before performing the Insert/Update.
Is this the best approach? Should I keep all inserts/updates and constraint checks in Java??
I NEED HELP!!!
Thank you to everyone!
 
Ranch Hand
Posts: 15304
6
Mac OS X IntelliJ IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
In my opinion, as many checks as you can do inside your java code the better. Simply because you are saving possbile Connection creation time and network traffic.
The problem you should consider is will the database ever change to the extent that you would have to dig back into your code because the constraints have changed.
It's hard to find that happy medium.
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Robert,
Naturally, I don't know what processing you need to do on this flat file, but I just wanted to make you aware (in case you weren't) of the "sqlloader" utility that is part of the Oracle database installation. It is the fastest way to load data from a file into the Oracle database.
You can find more information on it from these Web sites:
http://technet.oracle.com
http://tahiti.oracle.com
http://metalink.oracle.com
http://asktom.oracle.com
Hope this helps.
Good Luck,
Avi.
 
Ranch Hand
Posts: 104
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Robert,
My experience with doing constrainst and business logic checking in java with a large input file is that the program performace really suffers. You can be smart and use prepared statements and the like but it will still run somewhat slow as it is just such a vast number of statements to send to the database and wait for your results to come back. That said, I still go with this option sometimes because its so much easier than writing the stored procedure. Just be sure the program has the time it needs.
In my humble opinion, the best way to takle this is to store the inputs into a temp table and kick off a stored procedure. Make sure the temp table is mostly VARCHAR unless you want to do type and format checking up front. Then just the throw the file line by line into the temp table and let the stored procedure(s) take care of the rest. You're stored procedure should be able to output any errors/bad records to a file or another temp table.
 
Ranch Hand
Posts: 53
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Robert,
If you are using version 9, is posible to use external tables, so your file acts as an table inside the database.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic