• 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

JDBC vs PL/SQL Stored Procedures

 
Ranch Hand
Posts: 188
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
Cheers,
E
 
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Eric,
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.
http://www.javaperformancetuning.com/tips/jdbcconnpool.shtml
 
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 188
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for the advice!
 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 52
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Life just hasn't been the same since the volcano erupted and now the air is full of tiny ads.
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic