Reducing the cost of a SQL query on live database

JAYANTA_DATTA · #1 (**permalink**) 03-03-05, 05:59

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 (:?, :?, :?, :?, :?)

sathyaram_s · #2 (**permalink**) 03-03-05, 06:10

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

JAYANTA_DATTA · #3 (**permalink**) 03-03-05, 06:19

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

sathyaram_s · #4 (**permalink**) 03-03-05, 06:25

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

Marcus_A · #5 (**permalink**) 03-03-05, 12:57

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.

JAYANTA_DATTA · #6 (**permalink**) 03-04-05, 02:14

Thanks a lot Marcus.
That one worked fine and served our purpose upto a great extent.

Jayanta

Internet Services

Web Development

Digital Marketing

Consumer Tech