aspose file tools*
The moose likes JDBC and the fly likes Using JDBC versus Stored Procedures Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Using JDBC versus Stored Procedures" Watch "Using JDBC versus Stored Procedures" New topic
Author

Using JDBC versus Stored Procedures

Robert Russell
Greenhorn

Joined: Feb 25, 2003
Posts: 1
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!
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15300
    
    6

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.


GenRocket - Experts at Building Test Data
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1135

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.
Lu Battist
Ranch Hand

Joined: Feb 17, 2003
Posts: 104
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.
Rafa Moreno
Ranch Hand

Joined: Nov 19, 2002
Posts: 53
Robert,
If you are using version 9, is posible to use external tables, so your file acts as an table inside the database.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Using JDBC versus Stored Procedures