Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2005
    Location
    Chennai
    Posts
    16

    Question Unanswered: optimize the query to improve performace

    Column Name ColNo Datatyp Length
    ----1----v----2----v----3----v----4----v-
    RQST_ID 1 CHAR 18
    ACCT_ID 2 CHAR 18
    FLAG_ATR 3 CHAR 1
    TIME_STAMP 4 TIMESTAMP 26
    TRANS_TXT 5 CHAR 80

    and a batch process using the below cursor as its driver.
    ........
    ........
    EXEC-SQL
    DECLARE MAIN-CURSOR FOR
    SELECT
    RQST_ID,
    ACCT_ID,
    FLAG_ATR,
    TIME_STAMP,
    TRANS_TXT
    FROM AUDIT_TABLE
    WHERE
    FLAG_ATR = 'A'
    ORDER BY ACCT_ID, TIME_STAMP
    FOR FETCH ONLY
    END-EXEC
    The Audit_Table has a total of 12 million rows and 10 million of them have a value of 'A' for FLAG_ATR field. The only available index on the table is on RQST_ID.

    Could u plz suggest to optimize the query ?

    Attention please : This will retrieve 10 million records out of 12 ..Is there any other way to optimize without creating index on FLAG_ATR coloumn
    Last edited by sramesh; 02-05-07 at 02:53.
    Learning, Keep on learning.....

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    always try to put indexes on 'where' conditions. So putting index on flag_atr could help.

    You can also try to and index on all columns in select list: first on where condition and all other followed. This is all column in index approach. To optimize the SQL you need to add more where conditions to the query or using fetch first n rows only. If you are using static SQL you also need to rebind the packages. Have you executed statistics and reorganization on table?

    By the way what are you doing with 2 millions of records? Are you using some kind of report or some think like that. If so try using "fetch first n rows only" - use number of rows instead of n. Try explaining what are you doing, because this is little strange to use 2 millions or rows in query. Please also provide db2 version and operating system and version you are using.

    Hope this helps,
    Grofaty
    Last edited by grofaty; 02-05-07 at 02:42.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You could try to put an index on FLAG_ATR, but if 2 million (out of 12 million) of the rows have 'A', then it probably will not help if the 'A' rows are randomly distributed throughout the table.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605
    Marcus_A, I agree. Using such an index on randomly distributed data could retrieve all pages from table. DB2 could also decide no index is used. But creating index I would try the first...

  5. #5
    Join Date
    Jun 2005
    Location
    Chennai
    Posts
    16
    Plz see the changes ...We have 8 million records with flag_attr='A'..Also we
    don't want to create index .. Any possible to optimize ?
    Learning, Keep on learning.....

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If 8 million records have flag_attr = 'A', then an index on that column will do no good. If you can avoid the order by, then that would make it run faster.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    May 2006
    Posts
    82
    Most of the time is taken for fetching the randomly distributed data. Since there is no index it will go for tablespace scan. Removing Order by would save little bit and not much.
    You cannot optimise the query without changing the conditions on where predicate. Thats where you control the resources. Somehow try to bring RQST_ID in the where predicate if possible.

  8. #8
    Join Date
    May 2006
    Posts
    82
    I do see that this is a static SQL used in COBOL program. So the time taken is while opening the cursor and it is uncommited read. Once it is opened it should not be an issue.but not sure what is the recordlength and how much buffer area it would take.

  9. #9
    Join Date
    Dec 2005
    Posts
    273
    first of all, I would remove the column FLAG_ATR from the SELECT-list.

    Due to the WHERE-condition, that value is always 'A'. there is no need to retrieve ( and SORT !! ) this data.

  10. #10
    Join Date
    Jan 2003
    Posts
    1,605
    sramesh, can you please provide the reason you need to retrieve 8 million rows. What kind of application is this? Is it data warehouse application, OLTP application...

    Can you please provide execution time and what is desired execution time you are looking for?

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You could run the query throught the DB2 Design Advisor to get some further suggestions.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  12. #12
    Join Date
    Jan 2003
    Posts
    1,605
    stolze, if I remember correctly Design Advisor (from Control Center) will not advise how to rewrite SQL. But according to the simplicity of SQL, I can't see any rewrite option beside umayer suggestion. So Design Advisor will suggest some indexes.

    Hope this helps,
    Grofaty

  13. #13
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Indexes and MDCs are suggested by the Advisor (and probably a few other things).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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