Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338

    Unanswered: Reducing the cost of a SQL query on live database

    The following query is doing full table scan on table1 and table2 and thus the total cost on the Live database is becoming very high creating enough trouble for us.

    Can anybody suggest any tuning to this query:


    SELECT Q3.STUDENT_ID AS "APPROVAL_ID", Q3.INPUT_STUDENT AS "INPUT_STUDENT",
    Q3.ROOT_STUDENT AS "ROOT_STUDENT", Q3.LOCK_TOKEN AS "LOCK_TOKEN",
    'STUDENT'
    AS "APPROVAL_TYPE"
    FROM TABLE1 AS Q1, TABLE1 AS Q2, TABLE2 AS Q3
    WHERE Q3.WKF_STATE IS NOT NULL AND Q3.TO_DATE IS NULL AND (Q3.INPUT_STUDENT <>
    :?) AND Q3.INPUT_STUDENT IS NOT NULL AND (Q2.TYPE = :?) AND (Q2.ID =
    Q3.INPUT_STUDENT) AND (Q3.STUDENT_ID = Q1.ID) AND Q3.WKF_STATE IN (:?,
    :?,
    :?, :?) AND Q1.TYPE IN (:?, :?, :?, :?, :?)

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    What platform are you on ?

    have you done runstats on the tables ?

    Posting the definitions of the tables and indexes will make it easier for people to suggest

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    Thanks Sath: here are the details:

    Table1 has index on Id.
    Table2 has indexes on party_id, to_date, wkf_state, input_party
    other tan alt_id. But query is still doing full table scan.

    Thanks,
    Jayanta

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Just to double check , are you on DB2 ?

    With the details you provide, it is very difficult, atleast for me, to even guess

    Good luck

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Code:
    SELECT 
        Q3.STUDENT_ID AS "APPROVAL_ID", 
        Q3.INPUT_STUDENT AS "INPUT_STUDENT", 
        Q3.ROOT_STUDENT AS "ROOT_STUDENT", 
        Q3.LOCK_TOKEN AS "LOCK_TOKEN", 
        'STUDENT' AS "APPROVAL_TYPE" 
    FROM TABLE1 AS Q1, 
        TABLE1 AS Q2, 
        TABLE2 AS Q3 
    WHERE Q3.WKF_STATE IS NOT NULL 
        AND Q3.TO_DATE IS NULL 
        AND 
        (
            Q3.INPUT_STUDENT <> :?
        ) 
        AND Q3.INPUT_STUDENT IS NOT NULL 
        AND 
        (
            Q2.TYPE = :?
        ) 
        AND 
        (
            Q2.ID = Q3.INPUT_STUDENT
        ) 
        AND 
        (
            Q3.STUDENT_ID = Q1.ID
        ) 
        AND Q3.WKF_STATE IN (:?, :?, :?, :?) 
        AND Q1.TYPE IN (:?, :?, :?, :?, :?)
    Given the above predicates, I suspect that DB2 thinks that a large percentage of the rows from the tables will be returned, which means that it is faster for DB2 to read the entire table(s).

    If your query normally returns a small number of rows, then which predicate is the most selective?

    When you execute runstats, make sure you are getting distribution on key columns.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    Thanks a lot Marcus.
    That one worked fine and served our purpose upto a great extent.

    Jayanta

Posting Permissions

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