Databases
StackOverflow2013
Postsdbo
POPerformance issues with JDBC
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPerformance issues with JDBC
    primarykey
    data
    text
    <p>I'm currently facing an issue where a specific SQL-query is taking about 30 seconds to issue from within my Java application but &lt;1 sec in a SQL-client (SQL Developer). </p> <p>In the the question,<br> <a href="https://stackoverflow.com/questions/1339549/slow-query-in-java-by-jdbc-but-not-in-other-systems-toad">Slow query in Java by JDBC but not in other systems (TOAD)</a>, it is suggested that using a PreparedStatement bound to java variables could make the query execute far slower than in the SQL-client (TOAD in that case) because Oracle is getting confused about which indexes to use. Could this be an issue with a PreparedStatement without parameters as well?</p> <p>What could otherwise be the issue? </p> <p>The query looks something like </p> <pre><code>select sum(col1), sum(col2), max(select ...) from view_ where time_id = get_time_id(to_date('2010-10-10','yyyy-mm-dd')) </code></pre> <p>where view_ is a complex view containing aggregations of tables and other complex views. The query is executed as a PreparedStatement but without any parameters. It doesn't seem to make a difference whether we use prepared statement or just plain statements.</p> <p>Since the execution plan is quite huge I can't post all if it here, but the relevant difference seems to be: </p> <pre> UNION-ALL TABLE ACCESS FULL GVC_WH.PLAYER_FACT_DAILY TABLE 37 6717151 596,934.317 19940 240 7621178231 19502 </pre> <pre> UNION-ALL TABLE ACCESS BY INDEX ROWID GVC_WH.PLAYER_FACT_DAILY TABLE 38 2657 236.120 2429 30 20544658 2428 INDEX RANGE SCAN GVC_WH.PK_AGG_PLAYER INDEX (UNIQUE) 37 2657 16 1 638743 16 </pre> <p>Where the first snippet is from when running it with the JDBC Thin Client and the second from when running it inside SQL Developer. It's not picking up the correct index when running as a statement (makes no difference whether I use a prepared statement or not) with the JDBC Thin Client. The time difference i 30 seconds for the first and 0.5 seconds for the second. </p> <p>Could it be that using the function get_time_id prohibits the use of the index when used though JDBC, even though it is not function on the column and even though it seems to be working in SQL Developer? </p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USSebastian Ganslandt
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POPerformance issues with JDBC
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POPerformance issues with JDBC
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POPerformance issues with JDBC
      1. USAdam Paynter
      1. VTFavorite
    1. COCan you get and post the query plans for the two cases? And just for clarity, are you being consistent about how you are measuring performance? It is somewhat common to accidentally measure the time when SQL Developer returns the first row vs. the time that your Java code fetches the last row which may be substantially different.
      singulars
      1. POPerformance issues with JDBC
      1. USJustin Cave
    2. COI would start by looking at the execution plan (via dbms_xplan.display cursor) generated by each method (TOAD vs. Java). That should tell you if the database is treating the queries differently. If so, pay close attention to the predicates section as that will likely give you some clues as to why (e.g. implicit datatype conversion is occurring from the Java version and causing Oracle to not be able to use an index)
      singulars
      1. POPerformance issues with JDBC
      1. USCraig
    3. COSee also http://stackoverflow.com/questions/3976184/oracle-query-ora-01652-unable-to-extend-temp-segment-but-only-in-some-versions
      singulars
      1. POPerformance issues with JDBC
      1. USsymcbean
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload