Results 1 to 9 of 9
  1. #1
    pido is offline Member
    Join Date
    Feb 2010
    Posts
    5
    Rep Power
    0

    Question JDBC & Java executeQuery 20 times slower then TOAD

    Hi folks,

    I have a problem fetching the result of some of my queries out of Oracle 10g2
    If I run the query in TOAD it takes approx 1 sec to finish.
    If I run the query (static or Prepared) it takes longer than 20 sec.
    Some sample code of what I'm doing:
    Java Code:
    conn = (OracleConnection)DriverManager.getConnection(DB_CONNECTION_STRING + hostname + ":" + port + ":" + sid, DB_USER, DB_PASSWORD);
    conn.setSessionTimeZone(timezone);
    conn.setReadOnly(true);
    conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    objects = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
    objects.setQueryTimeout(queryTimeout);
    objectset = objects.executeQuery(query);
    Java Code:
    TOAD Plan on query:
    Cost: 5,54,
    	2 SORT AGGREGATE  Bytes: 27  Cardinality: 1  	
    		1 TABLE ACCESS FULL TABLE TOMEASUREMENTGROUP Cost: 5,54  Bytes: 1.894.401  Cardinality: 70,163
    Eventually my program is shutdown by a scheduler because it was running to long (>10 sec while it should run approx 1,5 sec).

    Anyone experience with this?

  2. #2
    travishein's Avatar
    travishein is offline Senior Member
    Join Date
    Sep 2009
    Location
    Canada
    Posts
    684
    Rep Power
    5

    Default

    I'm stumped. I first thought something with time to establish connection itself, but the setQueryTimeout right there only effects execution time.

    Would toad be using the same shared locking mechanism that the TRANSACTION_READ_COMMITTED is specifying on the connection? In that if doing that, such as the time to acquire the lock when the statement is executing is taking more time in the java mode ?

  3. #3
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,817
    Rep Power
    19

    Default

    TRANSACTION_READ_COMMITTED is completey unecessary for Oracle, since that's all about dirty reads...and Oracle doesn't allow dirty reads anyway.
    Not that that should have any effect ont he execution time, but you never know.

  4. #4
    pido is offline Member
    Join Date
    Feb 2010
    Posts
    5
    Rep Power
    0

    Default

    Update:

    I think I found a good lead on the problem.
    I extracted my code to isolate the problem.
    Below there are 2 queries I run in sequence using the same connection each run.
    I've been changing my connection and cursor settings to see if anything changes.
    Java Code:
    (1) SELECT CAST(VTFTIME.CET_TO_UTC1(CURRENT_DATE) AS TIMESTAMP) NOW FROM DUAL
    (2) SELECT COUNT(*), MAX(MTIMESTAMP) FROM (SELECT MTIMESTAMP FROM TOMEASUREMENTGROUP WHERE ID=2701 AND MTIMESTAMP BETWEEN (TO_DATE('26-02-2010 16:57:26', 'DD-MM-YYYY HH24:MI:SS') - INTERVAL '10' MINUTE) AND TO_DATE('26-02-2010 16:57:26', 'DD-MM-YYYY HH24:MI:SS'))
    
    Without specifying a cursor and connection type
    Time zone: My region
    (1) TOAD avg exec. time 0.07 sec, JDBC exec. time: 0.032 sec
    (2) TOAD avg exec. time 1.8 sec, JDBC exec. time: 11.234 sec
    
    Connection: read-only, read_commited
    ResultSet: FORWARD_ONLY,CONCUR_READ_ONLY
    Time zone: My region
    (1) TOAD avg exec. time 0.07 sec, JDBC exec. time: 0.032 sec
    (2) TOAD avg exec. time 1.8 sec, JDBC exec. time: 11.234 sec
    
    Connection: read-only, read_commited
    ResultSet: FORWARD_ONLY,CONCUR_READ_ONLY
    Time zone: not set
    (1) TOAD avg exec. time 0.07 sec, JDBC exec. time: 0.078 sec
    (2) TOAD avg exec. time 1.8 sec, JDBC exec. time: 0.875 sec !!!!
    So, now I know it has something to do with conn.setSessionTimeZone(timezone)

    Anyone?

  5. #5
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,817
    Rep Power
    19

    Default

    How exactly are you doing the prepared statement?
    As in the code.

  6. #6
    pido is offline Member
    Join Date
    Feb 2010
    Posts
    5
    Rep Power
    0

    Default Prepared statement

    Quote Originally Posted by Tolls View Post
    How exactly are you doing the prepared statement?
    As in the code.
    Hi Tolls,

    Currently I run the statement using SQL, no prepared statement.
    I isolated the problem at setting the timezone.
    If I don't set the timezone, my queries run in the expected time.
    The database Oracle 10g2 is set to timezone +1:00.
    If I request the timezone form the connection, it is null.
    This appears to be an incompatibility between jdbc 1.4 and oracle...

  7. #7
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,817
    Rep Power
    19

    Default

    What version of the drivers are you using?

  8. #8
    pido is offline Member
    Join Date
    Feb 2010
    Posts
    5
    Rep Power
    0

    Default Version

    Hi Tolls,

    Ofcourse

    platform server: windows 2003
    oracle enterprise: 10.2.0.3.0
    ---
    platform client: windows xp 2002 SP3
    jre: 1.6.0u17
    jdbc: 1.4 (latest according to my knowledge)
    Manifest-Version: 1.0
    Specification-Title: Oracle JDBC driver classes for use with JDK14
    Sealed: true
    Created-By: 1.4.2_14 (Sun Microsystems Inc.)
    Implementation-Title: ojdbc14.jar
    Specification-Vendor: Oracle Corporation
    Specification-Version: Oracle JDBC Driver version - "10.2.0.4.0"
    Implementation-Version: Oracle JDBC Driver version - "10.2.0.4.0"
    Implementation-Vendor: Oracle Corporation
    Implementation-Time: Sat Feb 2 11:40:29 2008

    Name: oracle/sql/converter/
    Sealed: false

    Name: oracle/sql/
    Sealed: false

    Name: oracle/sql/converter_xcharset/
    Sealed: false

  9. #9
    pido is offline Member
    Join Date
    Feb 2010
    Posts
    5
    Rep Power
    0

    Default Solutions

    For everyone who might encounter the same problem.
    I didn't go into detail about solving the time zone compatibility issue.
    I just casted the oracle timestamps to date:
    SELECT CAST(TIMESTAMPCOLUMN AS DATE) FROM ...

    This allows me to fetch dates without setting the timezone.
    Problem solved

Similar Threads

  1. Times without dates.
    By JavaJuJitZu in forum Advanced Java
    Replies: 14
    Last Post: 01-17-2010, 10:54 PM
  2. Replies: 1
    Last Post: 07-14-2009, 07:04 PM
  3. Replies: 0
    Last Post: 04-01-2008, 10:17 AM
  4. Getting row count from executeQuery()
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 12-05-2007, 02:31 PM
  5. Replies: 0
    Last Post: 09-28-2007, 12:56 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •