• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

PreparedStatement / Oracle Performance Problem

 
pascal betz
Ranch Hand
Posts: 547
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34225
341
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
pascal betz
Ranch Hand
Posts: 547
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic