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

JDBC vs PL/SQL Stored Procedures

Eric Fletcher
Ranch Hand

Joined: Oct 26, 2000
Posts: 188
Greetings all,
I'm not sure this the best place to post this, but I thought I would solicit some opinions from more experienced developers.
Is there a major performance gain to be realized using stored procedures to run complex queries vs just using JDBC calls? I inherited an app with very complex(read: too cluttered, IMHO ) JSP user interfaces that are pulling information from dozens of tables and it runs at a snail's pace. I think I have the queries optimized as much as the data model will allow, but it is still too slow.

My theory of evolution is that Darwin was adopted. - Steven Wright
Felix Kan

Joined: Aug 12, 2002
Posts: 8
Performance problems can be caused by vairous means. Before you get too carried away in optimizing individual SQL queries, you may want to review the following checklist:
1. How many SQL queries are you running off your page?
2. Are you using connection pooling to avoid unneccessary DataSource instantiation?
3. Run your SQL query in a console tool (Enterprise manager for MSSQL, TOAD for Oracle, etc). Is it as slow as what you expected? If not, then the query itself may not be the bottleneck.
4. Note that (a reminder) if you modified your jsp file, the first time you reload the page it will be slower (jsp compilation required). if you want to benchmark your jsp's performance do not count the first load after modification.
5. Consider using an Object cache if the usage pattern of your data model will benefit from it.
I had run into a performance problem before, and it's (2) and (5) that gave me a huge performance gain.
Here is a URL with lots of starting points on JDBC performance tuning.
Ken Robinson
Ranch Hand

Joined: Jul 25, 2002
Posts: 52
Without looking at your code it is hard to say. While my personal preference is to place database specific login in the db as stored procs, you seem to already have much of it written on the Java side.
One advantage is that a stored procedure on the database is already compiled and the database has already determined the best way for it to get the data you require. If you send the query from JDBC each and every time you need that same data, the DB may (or may not) have to develop that plan from scratch each time.
I would follow the advice above. Copy/Paste the SQL into your SQL tool. Run the query there and see if it is faster. You may be able to wrap that code in a stored proc fairly fast. Try that and see if performance increases. In either case, try a few test cases and verify you like the results before converting the entire system.
Eric Fletcher
Ranch Hand

Joined: Oct 26, 2000
Posts: 188
Thanks for the advice!
Laurence Jennings

Joined: Sep 26, 2002
Posts: 3
I have a similar issue with a project that I inherited, my experience was that as soon as I migrated the JDBC code to stored procedures I had some visible performance boosts. Don't get me wrong though I love JDBC, I just found that recompiling imbedded SQL statements in my apps was more time consuming that just staying in my database envir.
Ken Robinson
Ranch Hand

Joined: Jul 25, 2002
Posts: 52
This is an issue where there is no silver bullet. The way to go is dictated by the project, deadline and conditions.
If you inherit a project where all the code is JDBC Queries, the code works and there are no performance problems, then there is really no reason to change. However, if there are performance problems, moving the SQL Code to an SQL Server is a much better option, give the time exist.
I agree. Here's the link:
subject: JDBC vs PL/SQL Stored Procedures
Similar Threads
XML vs Database
prepared statement Vs Stored procedures
EJBs vs Stored Procedures vs Plain JDBC
stored procedures vs JDBC batch updates
can anybody give me an example about this?