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 ?
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.