Professional Web Applications Themes

resultset.close() - IBM DB2

hi, i have a sql query be executed up to thousand times, the only change is the where clause value change, so each time i need to create a statement to execute query, before create statement, i need to close last time opened resultset and statement, so i do resultset.close(); resultset = null, statement.close(); statement= null; after i meaesure the time, i found these steps take 15 millionsecs, but execute the query also take 15 millionsecs, so i don't understand why close resultset and close statement will take that much time, since it doesn't have anything relate to database and ...

  1. #1

    Default resultset.close()

    hi,

    i have a sql query be executed up to thousand times, the only change
    is the where clause value change, so each time i need to create a
    statement to execute query, before create statement, i need to close
    last time opened resultset and statement, so i do resultset.close();
    resultset = null, statement.close(); statement= null; after i meaesure
    the time, i found these steps take 15 millionsecs, but execute the
    query also take 15 millionsecs, so i don't understand why close
    resultset and close statement will take that much time, since it
    doesn't have anything relate to database and have no network traffic
    to return resultset. can anyone gives me the mechnism underneath so
    better for me to optimize ?
    xixi Guest

  2. #2

    Default Re: resultset.close()

    I can't help you on the CLI side, but if the queries are predictable in
    advance (you know what the different where predicates will go after, and
    in sum they go after a subset of the total table), these techniques
    could improve speed:

    1. Putting the subset of the table you care about in a Materialized
    Query Table (=MQT - i.e. your where predicate goes after different dates
    in July, but the table contains rows for all year - the MQT would hold
    all the July data.

    2. You could do the same as above in a temporary table - again, if you
    know what subset of the data you will be querying against

    3. You presumably have indexed the column in the where predicate - if
    the value of the where predicate always increases or always decreases
    with each query, you want may DB2 to do sequential prefetching - look
    this up in the Admin Guide (Performance) to ensure it's taking place.

    xixi wrote:
    > hi,
    >
    > i have a sql query be executed up to thousand times, the only change
    > is the where clause value change, so each time i need to create a
    > statement to execute query, before create statement, i need to close
    > last time opened resultset and statement, so i do resultset.close();
    > resultset = null, statement.close(); statement= null; after i meaesure
    > the time, i found these steps take 15 millionsecs, but execute the
    > query also take 15 millionsecs, so i don't understand why close
    > resultset and close statement will take that much time, since it
    > doesn't have anything relate to database and have no network traffic
    > to return resultset. can anyone gives me the mechnism underneath so
    > better for me to optimize ?
    Blair Adamache Guest

  3. #3

    Default Re: resultset.close()

    xixi <dai_xi> wrote:
    > hi,
    >
    > i have a sql query be executed up to thousand times, the only change
    > is the where clause value change,
    How much does the where clause change? Couldn't you just use parameter
    markers and, thus, reuse a prepared statement?
    > so each time i need to create a
    > statement to execute query, before create statement, i need to close
    > last time opened resultset and statement, so i do resultset.close();
    > resultset = null, statement.close(); statement= null;
    You should be able to just reuse the statement object, i.e. close it and
    then execute a new statement with it. That way, you could at least avoid
    the additional time to construct a new statement:

    statement = new java.sql.Statement();
    while ( ...<there is another query>...) {
    // execute query
    statement.close();
    statement.executeQuery(...<next query>...)
    }
    statement.close();
    statement = null;
    > after i meaesure
    > the time, i found these steps take 15 millionsecs, but execute the
    > query also take 15 millionsecs, so i don't understand why close
    > resultset and close statement will take that much time, since it
    > doesn't have anything relate to database and have no network traffic
    > to return resultset.
    Somewhere I have seen that creating a new statement object is rather
    expensive and you should avoid the creation if possible. Even if it is not
    very expensive, it would still be a good idea to reuse existing objects
    than to throw them away and recreate them.

    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena
    Knut Stolze Guest

  4. #4

    Default Re: resultset.close()

    Hi xixi,
    You could try investigating with the db2 driver trace.
    If you are using the cli driver (i.e., type 2 jdbc), checkout the
    "TRACE" keywords, especially TRACECOMM to see the "communication" data
    flow sizes between client/server and TRACETIME.

    They are doented in the db2cli.ini SERVICE section.

    I don't know for sure if jdbc type 4 has a trace facility, but expect it
    may too.

    Hope this helps,
    Dave.


    xixi wrote:
    >hi,
    >
    >i have a sql query be executed up to thousand times, the only change
    >is the where clause value change, so each time i need to create a
    >statement to execute query, before create statement, i need to close
    >last time opened resultset and statement, so i do resultset.close();
    >resultset = null, statement.close(); statement= null; after i meaesure
    >the time, i found these steps take 15 millionsecs, but execute the
    >query also take 15 millionsecs, so i don't understand why close
    >resultset and close statement will take that much time, since it
    >doesn't have anything relate to database and have no network traffic
    >to return resultset. can anyone gives me the mechnism underneath so
    >better for me to optimize ?
    >
    sharpe@us.ibm.com Guest

Similar Threads

  1. Can not open resultset
    By Rob in forum MySQL
    Replies: 2
    Last Post: May 21st, 07:49 PM
  2. Resultset Comparison
    By alagukannan in forum Coldfusion Database Access
    Replies: 1
    Last Post: December 8th, 07:59 PM
  3. Single Quotes in resultset
    By timstsprymsn in forum Macromedia ColdFusion
    Replies: 1
    Last Post: March 16th, 10:37 PM
  4. ResultSet is not updatable.
    By xixi in forum IBM DB2
    Replies: 7
    Last Post: August 7th, 10:53 PM
  5. how to overcome ResultSet limitations in DB2?
    By miguel angel rojas aquino in forum IBM DB2
    Replies: 1
    Last Post: June 6th, 11:43 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139