Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2009
    Posts
    31

    Unanswered: Query taking a long time to return result

    Hi All,

    I am db29.7.5. I am running a query with join on 3 tables that is taking 30 minutes to complete and sometimes timing out

    SELECT v.cid,
    CASE WHEN u.type = 'I' THEN coalesce(u.lname, ' ') || ', ' || coalesce(u.fname,' ')
    WHEN u.type='C' THEN coalesce(u.lname, ' ')
    END,
    (a.street || ', ' || a.city || ', ' || a.state) as place
    FROM tableA v, tableB u, tableC a
    WHERE v.uid = u.uid
    AND v.mailid = a.addid
    AND v.status='ACTIVE' AND u.lname LIKE 'BRIA'
    FOR FETCH ONLY;

    I Created an index on tableA which reduced the cost of the query but it did not significantly decreased the runtime. I rewrote the query "with temp table" still not much difference in the runtime. Can anyone suggest a better way of writing this query. The query is pulling upto 2055 records but each table has close to 90 million records

    with alist (cid, uid, street, city,state) as (SELECT
    v.cid, v.uid, a.street , a.city , a.state as
    FROM tableA v
    inner join tableC a
    on v.mailid = a.addid
    AND v.state='CHN'
    )

    select v.cid,
    CASE WHEN u.type = 'I' THEN coalesce(u.lname, ' ') || ', ' || coalesce(u.fname,' ')
    WHEN u.type='C' THEN coalesce(u.lname, ' ')
    END,
    (a.street || ', ' || a.city || ', ' || a.state) as place
    FROM tableA v, tableB u
    WHERE v.uid = u.uid
    AND u.lname LIKE '%BRIA%';


    Thanks

    dbsam

  2. #2
    Join Date
    Nov 2011
    Posts
    334
    plz collect db2exfmt output and tables ddl and post them here
    If you could , db2batch output is also needed....

  3. #3
    Join Date
    Dec 2009
    Posts
    31
    BEFORE INDEX
    Table Access Full TABLEA Table 4655.21
    Index Scan (No Table Access) TABLEB.UNAME Index 137.54
    Sort 143.32
    Row Identifier Scan (RID) 143.32
    Fetch Table Data TABLEB Table 3250.46
    Hash Join 7923.76
    Index Scan (No Table Access) TABLEC.ADDIX3 Index 22.86
    Nested Loop Join 8138.23
    Data Operation Complete 8138.23


    AFTER INDEX
    Index Scan (No Table Access) TABLEA.TEST_IX Index 1724.83
    Index Scan (No Table Access) UNAME Index 138.96
    Sort 145.71
    Row Identifier Scan (RID) 145.71
    Fetch Table Data TABLEB Table 3256.48
    Hash Join 5002.53
    Index Scan (No Table Access) TABLEC.ADDIX3 Index 22.87
    Nested Loop Join 5218.25
    Data Operation Complete 5218.26

  4. #4
    Join Date
    Nov 2011
    Posts
    334
    plz collect information as the following:

    put the sql in a file named sql.txt
    db2batch -d <dbname> -i complete -o p 5 e yes r 5 -f sql.txt > xxx.batch
    db2exfmt -d <dbname> -1 > xxx.exfmt
    db2look -d <dbname> -t tablename -e > table.ddl
    and upload these files ,thx。
    If you do not have explain tables
    plz run db2 -tvf <db2_install_dir>/misc/EXPLAIN.DDL first。

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Although it would be better to see the information suggested by fengsun2,
    I had some thoughts now.

    You specified different conditions for your first and second queries.
    So, it is not good to compare them, for investigation of the effect of indexes and with clause.


    Anyway, I reformatted your first query and looking it.
    (made some change, like using "inner join" syntax, added alias for first result column, removed "for fetch only")
    Code:
    SELECT v.cid
         , CASE u.type
           WHEN 'I' THEN
                coalesce(u.lname, ' ') || ', ' || coalesce(u.fname,' ')
           WHEN 'C' THEN
                coalesce(u.lname, ' ')
           END  AS name
         , a.street || ', ' || a.city || ', ' || a.state as place
     FROM  tableA v
     INNER JOIN
           tableB u
      ON   u.uid = v.uid
     INNER JOIN
           tableC a
      ON   a.addid = v.mailid
     WHERE v.status = 'ACTIVE'
       AND u.lname LIKE 'BRIA'
    ;
    Note: "u.lname LIKE 'BRIA'" is equivalent to
    u.lname = 'BRIA' AND LENGTH(u.lname) = 4 /* 4 = LENGTH('BRIA') */

    Candidates of indesxes:
    tableA v: (status , uid , mailid)
    tableB u: (lname , uid) or (uid , lname)
    tableC a: (addid)


    Looking into your second query, the following conditions were different from first query.
    AND v.state = 'CHN'
    AND u.lname LIKE '%BRIA%';

    Candidates of indesxes:
    tableA v: (state , uid , mailid)
    tableB u: (uid , lname)
    tableC a: (addid)

  6. #6
    Join Date
    Dec 2009
    Posts
    31
    Hi tonkuma,

    I executed the query with your recommndations, but no change in the runtime. It is still taking 12 minutes and the cost is the same as the previous one.

    Thanks

    dbsam

  7. #7
    Join Date
    Mar 2012
    Posts
    1
    Quote Originally Posted by dbsam View Post
    Hi tonkuma,

    I executed the query with your recommndations, but no change in the runtime. It is still taking 12 minutes and the cost is the same as the previous one.

    Thanks

    dbsam
    Few questions
    • What are the indexes available on TableB?
      What is your predicate a) LIKE 'BRIA%' -- b) LIKE '%BRIA%'?


    From what I see, its TableB which is having the max time. So create a non-unique index on this TableB on lname and include uid and fname columns. It should use index scan and query should perform with index only access.

    For tableA and tableC, go with tonkuma recommendations.

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    First things first, Did you do RUNSTATS on the table and indexes ?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  9. #9
    Join Date
    Dec 2009
    Posts
    31
    yep started with runstats, plus the new index that I created on tableA ( similar to what Tonkuma has suggested but with the PK in the end os the index sequence) was with collect detailed statistics. Also there is a similar index on TableB just not an include index . I will try the include index and see if that works

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please show your CREATE INDEX statements.

  11. #11
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb You have to separate the tables

    How I understood the problem the following query will work better:

    Code:
    SELECT 
           U.cid
         , U.name
         , A.place
     FROM  
    
    ( select distinct v1.mailid, v1.cid, 
    , CASE u1.type
           WHEN 'I' THEN
                coalesce(u1.lname, ' ') || ', ' || coalesce(u1.fname,' ')
           WHEN 'C' THEN
                coalesce(u1.lname, ' ')
           END  AS name
    
    from tableA v1 JOIN tableB u1
      ON   u1.uid = v1.uid
    WHERE v1.status = 'ACTIVE'
       AND  u1.lname LIKE '%BRIA%'
    ) U 
    JOIN TABLE
    (select a1.street || ', ' || a1.city || ', ' || a1.state as place
    from tableC a1
    Where  a1.addid = U.mailid ) A
    ON 1 = 1
     With UR;
    Lenny
    Last edited by Lenny77; 03-23-12 at 18:05.

Posting Permissions

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