Results 1 to 9 of 9

Thread: slow query

  1. #1
    Join Date
    Mar 2012
    Posts
    109

    slow query

    Hi,
    I'm using DB2 10.1 ESE on Windows.

    I have two tables, each one has 200K rows.

    CREATE TABLE SYSREPORTS(
    actId INTEGER NOT NULL,
    propId INTEGER NOT NULL,
    bckId INTEGER NOT NULL,
    sysId INTEGER NOT NULL,
    failed SMALLINT NOT NULL,
    template SMALLINT NOT NULL,
    description VARCHAR(8000),
    rnum BIGINT NOT NULL,
    address BIGINT NOT NULL
    CONSTRAINT pk_sysreports PRIMARY KEY ( actId, propId, bckId, sysId )
    );

    CREATE TABLE SMART_PCS(
    id INTEGER NOT NULL,
    );

    SYSREPORTS has a further index, not UNIQUE, on sysId.

    If I run this query

    select S.sysid FROM SMART_PCS C
    JOIN SYSREPORTS S ON S.sysId= C.id
    WHERE S.actId = p_actId AND S.propId = p_propId AND S.sysId = p_sysId for read only;

    it takes less than a second, if I run

    select S.sysid, S.description, S.rnum FROM SMART_PCS C
    JOIN SYSREPORTS S ON S.sysId= C.id
    WHERE S.actId = p_actId AND S.propId = p_propId AND S.sysId = p_sysId for read only;

    it takes more than 400 seconds.

    I cannot include description and rnum in the index since it would require the index to be unique (and I don't want it).

    How can I solve this issue?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,130
    Is there a difference in the access plan for both queries?

    Andy

  3. #3
    Join Date
    Mar 2012
    Posts
    109
    Yes there is!
    Could you please help me understand what's happening?

    FAST query access plan:
    --------------------------
    Total Cost: 278.963
    Query Degree: 1

    Rows
    RETURN
    ( 1)
    Cost
    I/O
    |
    508.424
    HSJOIN
    ( 2)
    278.963
    120.348
    /------+-------\
    127635 1000
    IXSCAN TBSCAN
    ( 3) ( 4)
    264.062 7.61769
    119.348 1
    | |
    5.56488e+07 1000
    INDEX: MYS TABLE: MYS
    PK_SYSREPORTS SMART_PCS
    Q2 Q1

    -------------------------
    SLOW query access plan:
    -------------------------
    Total Cost: 17696.8
    Query Degree: 1

    Rows
    RETURN
    ( 1)
    Cost
    I/O
    |
    508.424
    NLJOIN
    ( 2)
    17696.8
    2509.42
    /-------+--------\
    1000 0.508424
    TBSCAN FETCH
    ( 3) ( 4)
    7.61769 17.7037
    1 2.50842
    | /---+----\
    1000 0.508424 5.56488e+07
    TABLE: MYS IXSCAN TABLE: MYS
    FROM SMART_PCS ( 5) SYSREPORTS
    Q1 14.1192 Q2
    2
    |
    5.56488e+07
    INDEX: MYS
    PK_SYSREPORTS
    Q2

  4. #4
    Join Date
    Jan 2003
    Posts
    4,130
    You need an index on SMART_PCS. You are table scanning it in both cases. Because, for the fast query you only join to the other table on its index, it uses a has join. The bad query, you want other columns from the other table, so it uses a loop join. A table scan (SMART_PCS) with a loop join will always take a while.

    Andy

  5. #5
    Join Date
    Mar 2012
    Posts
    109
    Thanks Andy.
    I added an index on SMART_PCS and now my query runs in 100 seconds, great improvement!

    However, I still have a problem.
    If I run the same query on SQL Server the first time it takes 110 seconds, but the second time it takes only 2 seconds!!

    If I run the same query on DB2 it always takes 100 seconds.
    I expected data to be already present in bufferpool so to have the same results as SQL Server, I also tried enlarging the bufferpool size (now it's 27000), the result is the same...

  6. #6
    Join Date
    Jan 2003
    Posts
    4,130
    Did you increase the correct bufferpool? Is the index in a separate one. If so, that is the BP you need to increase.

    Andy

  7. #7
    Join Date
    Mar 2012
    Posts
    109
    I've got only one big bufferpool, the default one, and increased it to
    30000
    40000
    45000
    55000
    with the command
    db2 "alter bufferpool ibmdefaultbp immediate size xxx automatic".
    The query is not affected at all by any of these change

  8. #8
    Join Date
    Jan 2003
    Posts
    4,130
    55,000 is a small BP. Assuming a 4K page size, it is only 225MB. How many pages are your tables and indexes?


    Andy

  9. #9
    Join Date
    Nov 2011
    Posts
    315
    plz publish the full access plan here ( both slow and fast , use db2exfmt )
    and how many rows will the query get ( count(*) )?

Posting Permissions

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