Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2011
    Posts
    5

    Unanswered: query performance in different OS

    Hi,

    I have a query which reads from syscat.columns for a particular table , to find out that table's column details.

    select typename,length into i_typename,i_length from syscat.columns where tabname=<mytabname> and colname=<mycolname>
    -- here mytabname and mycolname are variables.

    This query is fast in Windows, but slow in AIX.
    db2 version is 8.2 .


    This query is in a loop , to find details for different tables and columns.

    Later I changed the query , in such a way that , I fired the select qry from system table only once and copied those data into a temp table and then I am reading from the temp table.

    This query is also fast in Windows, but slow in AIX.

    1.My question is does query performance depend on OS?
    2.Any OS configuration settings affect the query performance? If so what are they and how to read those settings?
    3. Any database configurations affecting the performance ? If so what are they and how to read those settings ?
    4.Or is this because of reading from system tables ?

    thanks .

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Almost every factors affect to performance.

    It would be better to look this first.
    (It is for DB2 9.7, but basics are common to other versions.)
    Performance overview - IBM DB2 9.7 for Linux, UNIX, and Windows

    For example:
    Performance information that users can provide

    The first sign that your system requires tuning might be complaints from users. If you do not have enough time to set performance objectives and to monitor and tune in a comprehensive manner, you can address performance issues by listening to your users. Start by asking a few simple questions, such as the following:
    •What do you mean by "slow response"? Is it 10% slower than you expect it to be, or tens of times slower?
    ...
    ...

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    This query is in a loop , to find details for different tables and columns.
    Why in a loop?

    Any other ways?
    For example:
    1) Open a cursor for "SELECT tabname , colname , typename , length ... ORDER BY tabname , colno"
    2) Issue fetch(es) in a loop.

    Note: If you want to specify specific combinations of tabname and colname,
    use like ...
    Code:
    SELECT tabname , colname , typename , length
     FROM  syscat.columns
     WHERE (tabname, colname)
           IN (VALUES (yourtabname1, yourcolname1), (yourtabname2, yourcolname2), ...)
     ORDER BY
           tabname , colno
    Last edited by tonkuma; 07-24-11 at 07:12. Reason: Add Note. Replace sample SELECT statement.

  4. #4
    Join Date
    Jun 2011
    Posts
    5
    Thanks.

    But the loop one is the first approach, where I have a cursor which gets the column names, for which I need the type and length. so placed the syscat. columns qry inside that loop.

    Later I changed the approach by reading all the columns outside the cursorloop . Now reading from syscat.columns for these columns and copying into temp table. Now inside the loop, I am reading the data from that temp table for each column.

    Something like copying data from syscat.columns into a temp table first and then reading from this temp table inside the loop. replacing the call to syscat.columns with call to temp table.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    But the loop one is the first approach, where I have a cursor which gets the column names,
    for which I need the type and length. so placed the syscat. columns qry inside that loop.

    Later I changed the approach by reading all the columns outside the cursorloop .
    Now reading from syscat.columns for these columns and copying into temp table.
    Now inside the loop, I am reading the data from that temp table for each column.
    Instead of that approach, I thought that you can try...
    1) declare/open a cursor outside the loop for
    Code:
    SELECT colname , typename , length
     FROM  syscat.columns
     WHERE tabname = <yourtabname>
       AND colname
           IN ( SELECT statement for a cursor which gets the column names )
     ORDER BY
           /* your required sequence in reading the data from that temp table for each column. */
    2) fetch the cursor inside the loop
    instead of reading the data from that temp table for each column.

    By this approach,
    you can avoid a cursor which gets the column names,
    and reading from syscat.columns for these columns and copying into temp table.

    And you may be able to expect more optimized query for a cursor than using two cursors.
    Last edited by tonkuma; 07-24-11 at 16:22. Reason: Change "1) make ..." to "1) declare/open ...". Add a phrase "instead of ..." to "2) fetch the cursor ..."

Posting Permissions

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