File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes PreparedStatement / Oracle Performance Problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "PreparedStatement / Oracle Performance Problem" Watch "PreparedStatement / Oracle Performance Problem" New topic

PreparedStatement / Oracle Performance Problem

pascal betz
Ranch Hand

Joined: Jun 19, 2001
Posts: 547
hi All
i have a performance problem with my webapplication and Oracle 9 DB. we use prepared statements to execute our queries. one PreparedStatement executes really sloooooooow (around 26 seconds). if i intercept this statement trough the IronTrack SQL Monitor and execute the it in TOAD (or Hora or any other SQL tool) it runs in about 4 seconds. that is six times faster than trough JDBC. How can this be ?

any hints here what i could do , where this comes from ?

have a nice evening.


Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33119

Is it a complex query? Does it return a lot of data?

My first guess would be network traffic, but it would help if you could post the query here.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
pascal betz
Ranch Hand

Joined: Jun 19, 2001
Posts: 547

yes it is a complex query.... (if i remove all formatting and print it it's about one page.... so i do not post it here ;-)) but in the example i use it only returns 2 rows.

the problem is if i run the same query in TOAD then it takes about 4 seconds. but if i run it as a prepared statement it takes about 26 seconds (same DB, same Network link).
When using JDBC/PreparedStatements then
IronTrack SQL shows that most of the time is used while executing (preparation and retrieval are only a about 5 percent of the whole time).

So i wonder how can this be ? why does JDBC take so much longer ? I can understand, that native code (and an Oracle specific tool) might be a little faster.
But the execution of the actual statement should be at about the same speed, shouldn't it ?

Are there any Oracle Specific connection settings i should/can apply ? i testet several versions of the Oracle JDBC driver but that did not change anything.

Again i would be thankful for any hints, articles, links, ideas and thoughts.

I agree. Here's the link:
subject: PreparedStatement / Oracle Performance Problem
It's not a secret anymore!