Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
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
JavaRanch » Java Forums » Databases » JDBC
Bookmark "PreparedStatement / Oracle Performance Problem" Watch "PreparedStatement / Oracle Performance Problem" New topic
Author

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.

thanks

pascal
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30361
    
150

Pascal,
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
Jeanne:

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.


pascal
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: PreparedStatement / Oracle Performance Problem