This week's giveaway is in the EJB and other Java EE Technologies forum.
We're giving away four copies of EJB 3 in Action and have Debu Panda, Reza Rahman, Ryan Cuprak, and Michael Remijan 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 EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
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: 29233
    
138

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
 
Similar Threads
Error in executeUpdate
Doubt about PreparedStatement
Executing PreparedStatement only once and Executing Statement only once
Statement vs. PreparedStatement
What is the Advantage Of Prepared Statement Over Statement