Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2007
    Posts
    36

    Unanswered: improve the performance of queries

    Hi,

    I've 10 large tables each have around 20 million records.
    while retreiving queries using joining of 3 to 4 tables with some conditions, the execution takes very long time and performance become a concern.

    What are the ways to improve the performance of the queries?
    Actually the queries are for datawarehousing reports and joins more than 3 tables.

    Please suggest on Indexing, table splits and any other ways to improve the performance.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    MDCs, MQTs

    Quote Originally Posted by ani_dbforum
    Hi,

    I've 10 large tables each have around 20 million records.
    while retreiving queries using joining of 3 to 4 tables with some conditions, the execution takes very long time and performance become a concern.

    What are the ways to improve the performance of the queries?
    Actually the queries are for datawarehousing reports and joins more than 3 tables.

    Please suggest on Indexing, table splits and any other ways to improve the performance.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Nov 2007
    Posts
    36
    I'm using db2v8. I'll try with MDC and MQT (I'm new to Db2 and need to search )
    The tables are already created. Can the reorg of Indexes with Runstats help?

  4. #4
    Join Date
    Nov 2007
    Posts
    21
    Creating some more indexes will improve the performance. Need to check what are the coloumns require the indexes.

  5. #5
    Join Date
    Nov 2007
    Posts
    36
    How to see the index on which coloumns they are created?
    I need to see the indexes and their keys.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    db2 describe indexes for schema.table show detail
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Nov 2007
    Posts
    36

    Left Outer Join

    are the below queries give same result?

    QUERY1:

    SELECT A.DEPTNAME,
    A.DEPTNO, A.BUILDING_NO,B.CALL_LETTERS, B.EMP_CITY]
    FROM
    ORG.DEPT A LEFT OUTER JOIN ORG.EMP B
    ON
    A.DEPTNO=B.DEPTNO
    WHERE
    (A.JOINING_DATE= '2005-01-01 11:16:30.0' OR
    B.JOINING_DATE= '2005-01-01 11:16:30.0')


    query2:

    SELECT A.DEPTNAME,
    A.DEPTNO, A.BUILDING_NO,B.CALL_LETTERS, B.EMP_CITY
    FROM
    ORG.DEPT A LEFT OUTER JOIN ORG.EMP B
    ON
    A.DEPTNO=B.DEPTNO
    AND
    A.JOINING_DATE= '2005-01-01 11:16:30.0'
    OR
    B.JOINING_DATE= '2005-01-01 11:16:30.0'

Posting Permissions

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