Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256

    Unanswered: Tuning advice needed

    I have reports running on two datamarts tables , one ordertable (50000 rec.) and a sales table (2000000 rec.)
    Both datatables are joined to a dimensiontable on item-level (70.000 rec). This dimensiontable itself is joined to a table storing group entries (300 rec)

    Reports on both sales and ordertable have conditions set on primary key columns of the facttables. Adding a condition on the small grouping table to fetch data for just one group of items (condition on the indexed column) slows the query down with a factor 40.....!!

    On the big sales-table the query takes minutes, but completes. On the small order-table it takes forever.

    The explain plan analyses indicate that with just conditions on the facttable a hash join is created between facttable and itemtable.
    With additional condition on the grouping table explain plan starts with the index scan of the index on the group table.

    What puzzles me is :

    1. Adding a condition on an indexed field to fetch a smaller dataset seems to kill of the performance almost totally

    2. report on large facttable succeeds in minutes, whereas same query-structure on small facttable nevers seems to end....

    Anyone for some usefull tips/hints/suggestions?
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Can you please post the SQL and db2 version and OS version?

    Thanks,
    Grofaty

  3. #3
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    Originally posted by grofaty
    Hi,

    Can you please post the SQL and db2 version and OS version?

    Thanks,
    Grofaty

    DB2 version 7.1 on Windows NT (a slow old server)

    ORDER-query without additional condition:


    SELECT
    DIM_SALESMEN_COMP.SALES_REP_ID,
    Sum((BIKE_ORDERS_ECL.LQORD-BIKE_ORDERS_ECL.LQSHP) * BIKE_ORDERS_ECL.NET_PRICE * BIKE_ORDERS_ECL.DISC_FACTOR * BIKE_ORDERS_ECL.EXCHANGE_RATE),
    SUM(BIKE_ORDERS_ECL.LQORD - BIKE_ORDERS_ECL.LQSHP),
    BIKE_IREF01_TRANSLATION.IREF01,
    BIKE_IREF01_TRANSLATION.IABBT,
    BIKE_IREF01_TRANSLATION.CATEGORY,
    BIKE_CUSTOMER.CCUST,
    BIKE_CUSTOMER.CNME,
    BIKE_CUSTOMER.CCOUN,
    DIM_ITEM.IREF02
    FROM
    DIM_SALESMEN_COMP,
    BIKE_ORDERS_ECL,
    BIKE_IREF01_TRANSLATION,
    BIKE_CUSTOMER,
    DIM_ITEM
    WHERE
    ( BIKE_IREF01_TRANSLATION.IREF01=DIM_ITEM.IREF01 )
    AND ( BIKE_ORDERS_ECL.CUSTOMER=BIKE_CUSTOMER.CCUST )
    AND ( DIM_SALESMEN_COMP.SALES_REP_ID=BIKE_ORDERS_ECL.SAL ES_REP )
    AND ( DIM_ITEM.ITEM_ID=BIKE_ORDERS_ECL.ITEM )
    AND (
    BIKE_CUSTOMER.CCUST <= 500000
    AND BIKE_CUSTOMER.CCUST >= 200000
    AND DIM_SALESMEN_COMP.SALES_REP_ID IN (1, 2, 3, 4, 5, 6, 4001, 4002, 4003, 4004, 4005, 4006)
    )
    GROUP BY
    DIM_SALESMEN_COMP.SALES_REP_ID,
    BIKE_IREF01_TRANSLATION.IREF01,
    BIKE_IREF01_TRANSLATION.IABBT,
    BIKE_IREF01_TRANSLATION.CATEGORY,
    BIKE_CUSTOMER.CCUST,
    BIKE_CUSTOMER.CNME,
    BIKE_CUSTOMER.CCOUN,
    DIM_ITEM.IREF02



    ORDER-query with additional condition:


    SELECT
    DIM_SALESMEN_COMP.SALES_REP_ID,
    Sum((BIKE_ORDERS_ECL.LQORD-BIKE_ORDERS_ECL.LQSHP) * BIKE_ORDERS_ECL.NET_PRICE * BIKE_ORDERS_ECL.DISC_FACTOR * BIKE_ORDERS_ECL.EXCHANGE_RATE),
    SUM(BIKE_ORDERS_ECL.LQORD - BIKE_ORDERS_ECL.LQSHP),
    BIKE_IREF01_TRANSLATION.IREF01,
    BIKE_IREF01_TRANSLATION.IABBT,
    BIKE_IREF01_TRANSLATION.CATEGORY,
    BIKE_CUSTOMER.CCUST,
    BIKE_CUSTOMER.CNME,
    BIKE_CUSTOMER.CCOUN,
    DIM_ITEM.IREF02
    FROM
    DIM_SALESMEN_COMP,
    BIKE_ORDERS_ECL,
    BIKE_IREF01_TRANSLATION,
    BIKE_CUSTOMER,
    DIM_ITEM
    WHERE
    ( BIKE_IREF01_TRANSLATION.IREF01=DIM_ITEM.IREF01 )
    AND ( BIKE_ORDERS_ECL.CUSTOMER=BIKE_CUSTOMER.CCUST )
    AND ( DIM_SALESMEN_COMP.SALES_REP_ID=BIKE_ORDERS_ECL.SAL ES_REP )
    AND ( DIM_ITEM.ITEM_ID=BIKE_ORDERS_ECL.ITEM )
    AND (
    BIKE_CUSTOMER.CCUST <= 500000
    AND BIKE_CUSTOMER.CCUST >= 200000
    AND DIM_SALESMEN_COMP.SALES_REP_ID IN (1, 2, 3, 4, 5, 6, 4001, 4002, 4003, 4004, 4005, 4006)
    AND BIKE_IREF01_TRANSLATION.IREF01 = 'SHGLA'
    )
    GROUP BY
    DIM_SALESMEN_COMP.SALES_REP_ID,
    BIKE_IREF01_TRANSLATION.IREF01,
    BIKE_IREF01_TRANSLATION.IABBT,
    BIKE_IREF01_TRANSLATION.CATEGORY,
    BIKE_CUSTOMER.CCUST,
    BIKE_CUSTOMER.CNME,
    BIKE_CUSTOMER.CCOUN,
    DIM_ITEM.IREF02
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    I can't see any problem in SQL. Maybe you have some problems of missing indexes. Make the explain or Visual Explain (in Command Center) to see where is the problem.

    If you don't know how to use Visual Explain there is one excellent article DB2 Explains Itself: A Roadmap to Faster Query Runtime on DevX.com web page.

    Hope this helps,
    Grofaty

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Was RUNSTATS done recently ? Did it have WITH DISTRIBUTION AND DETAILED INDEXES Clause ?

    What is the cardinality of the key ? Is is CLUSTERED ? Have you had a chance to look at the distribution and quantiles of data values, which may explain the behaviour ..

    You may want to consider, NUMFREQVALIES and NUMQUANTILE USage in RUNSTATS ....

    REORGCHK may also be useful

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

  6. #6
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    Originally posted by grofaty
    Hi,

    I can't see any problem in SQL. Maybe you have some problems of missing indexes. Make the explain or Visual Explain (in Command Center) to see where is the problem.

    If you don't know how to use Visual Explain there is one excellent article DB2 Explains Itself: A Roadmap to Faster Query Runtime on DevX.com web page.

    Hope this helps,
    Grofaty
    Was RUNSTATS done recently ? Did it have WITH DISTRIBUTION AND DETAILED INDEXES Clause ?

    What is the cardinality of the key ? Is is CLUSTERED ? Have you had a chance to look at the distribution and quantiles of data values, which may explain the behaviour ..

    You may want to consider, NUMFREQVALIES and NUMQUANTILE USage in RUNSTATS ....

    REORGCHK may also be useful

    HTH
    Sathyaram

    Indices.

    I am currently using just primary key indices which means that FK
    DIM_ITEM.IREF01 and BIKE_ORDERS_ECL are not indexed.

    Runstats/ REORGCHK

    Yes, these are performed with the right settings. REORGCHK does not advice a reorganization of the table. The table contains just those orders that are still open, so the table is truncated and refilled each night.
    The table currently holds just 42201 records
    The sales table (that gives no trouble) holds 1336883 records (30-fold)

    Explain plan

    I am using a third party tool to analyze the queries,even MS Word is brilliant compared to the utter uselesness of command centre. It seems to freeze up all the time, does not accept straight SQL statements (arrrrrrrggggggggHHHHHH)

    When I take snapshots on the badly performing SQL the amount of rows read is absolutely huge. (hundreds of millions).

    Should I add indices to the FK's? Joins seem to be working quite good without them for fetching data ......
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  7. #7
    Join Date
    Mar 2004
    Posts
    448
    Because there are lot of rows in the table, if possible try to create summary tables and also check what class of query optimization you are using.
    Similarly create indexes on the columns using in the "in" clause and in the foreign key and primary key join.

  8. #8
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    Actually there are less than 50.000 records in the facttable, which makes this so peculiar.
    Creating a summary table on such a small facttable should not be needed.
    Query optimization degree is at 9, so it is allowed maximum time to create a good access path for the query.
    An index on the field referenced by the 'IN' clause would be reasonable, but the condition actually hits about 80% of the rows, so it is questionable if the is a remedy. Similar queries on other facttables are fast enough.......
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

Posting Permissions

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