Results 1 to 2 of 2
  1. #1
    asgardar is offline Member
    Join Date
    Jan 2011
    Posts
    3
    Rep Power
    0

    Default OracleStatement.defineColumnType(). example

    have a query which involves multilpe tables and joins and function calls and inline views in the from clause. If I run the query using TOAD the query is fast takes about 50 secs to 1 min. If I run the same query in java program by JDBC using a parametric query the query takes longet time (3 to 4 times longer). I'm doing insert using select statement.I read somewhere that SELECT statement makes two round trips to the database, the first for metadata, the second for data. Use OracleStatement.defineColumnType(). does someone have an example how it can be done.My sample query

    insert into TESTAD
    select td.id as ID,
    td.std_bill_rate as Standard_Bill_Rate,
    td.overtime_bill_rate as OverTimeBillRate,
    td.doubletime_bill_rate as DoubleBillRate,
    td.effective_date as effective_billRate

    from THIRDDEV td
    where td.id = ? and td.effective_date = ?

    I need some java code example using defineColumnType(). My sql is very complicated I dont want to post it in the forum. if I have some example I can then modify my query.

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,015
    Rep Power
    20

    Default

    If this is a PreparedStatement (which seems to be the case since it's got '?' in it) then there will be the single round trip to the db.

    That SQL will be executed by Oracle.

    How are you setting the parameters? Are you setting them using the correct setters?

    How are you timing the Java side of this?

    Have you timed it several times (to take into account cacheing on Oracle)?

Posting Permissions

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