Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2007
    Posts
    2

    Unanswered: SQL query taking long time

    Hi,

    One of my query is taking a lot of execution time.I need help with fine tuning this query.

    Query:

    SELECT
    A,
    B,
    C,
    D,
    E
    FROM
    QUAL1.TABLE1
    WHERE
    PROCS_TIMSTM > = TIMESTAMP (?) AND
    PROCS_TIMSTM < = TIMESTAMP (?) AND
    AGENT_CD = ? AND
    ( STORE_CD IN (?, ?, ?, ?) OR DLV_STORE_CD IN (?, ?, ?, ?) ) WITH UR;

    Is there any better way to write this query using sub routines or unions to get better performance.

    Thanks in advance

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Without knowing what indexes you have on the table (I will assume you have the correct ones), I woud try this:

    SELECT
    A,
    B,
    C,
    D,
    E
    FROM
    QUAL1.TABLE1
    WHERE
    PROCS_TIMSTM > = TIMESTAMP (?) AND
    PROCS_TIMSTM < = TIMESTAMP (?) AND
    AGENT_CD = ? AND
    STORE_CD IN (?, ?, ?, ?)

    UNION ALL

    SELECT
    A,
    B,
    C,
    D,
    E
    FROM
    QUAL1.TABLE1
    WHERE
    PROCS_TIMSTM > = TIMESTAMP (?) AND
    PROCS_TIMSTM < = TIMESTAMP (?) AND
    AGENT_CD = ? AND
    DLV_STORE_CD IN (?, ?, ?, ?)
    WITH UR;
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Oct 2007
    Posts
    2
    Index 1:

    AGENT_CD
    PROCS_TIMSTM
    SELL_SRC_CD
    MDL_NBR

    index 2:

    DLV_STORE_CD
    PROCS_TIMSTM
    SELL_SRC_CD
    MDL_NBR

    We can create new indexes if required based on query..

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It is not possible to know which indexes should be created based on the information you supplied. For example, I would need to know the cardinality of AGENT_CD to know if it should be indexed.

    Since I assume that PROCS_TIMSTM has a very high cardinality, I would make it the first column of at least one of the indexes.

    In general, columns with a low cardinality, especially less than 20 unique values, should not be indexed, or not the first column of a multi-column index.

    I hope you have run runstats with following options:
    runstats on table QUAL1.TABLE1 with distribution on key columns and indexes all
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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