This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes PreparedStatement / Oracle Performance Problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
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
internet detective

Joined: May 26, 2003
Posts: 30050

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.

[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, 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
Similar Threads
Doubt about PreparedStatement
Error in executeUpdate
Statement vs. PreparedStatement
Executing PreparedStatement only once and Executing Statement only once
What is the Advantage Of Prepared Statement Over Statement