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

    Unanswered: non-indexed approach 4 times faster

    Consider a million-row facttable with some additional dimensiontables on item, customer and groupanalysis dimensions.

    A typical query that uses no index whatsoever (objects used from facttable that are non-indexed) runs in 6 seconds.
    Now adding a dimensiontable on customer to the query so that the condition on customer can use the index of the dimensiontable.

    Explain plan shows that this really happens, but query response is 4 times as SLOW. I noticed that the customer field in dimensiontable is decimal (6,0) and in facttable (integer).
    Explain plan indicates 'Predicate data type mismatch' for the join over the customer fields.

    1. Is the data type mismatch causing a lot of performance?
    2. Even with use of the index the query path seems a lot less efficient ,but why and how?

    Non-indexed query:

    SELECT
    BIKE_SALES_DAILY.ORDER,
    BIKE_SALES_DAILY.COST_PRICE,
    BIKE_SALES_DAILY.NET_PRICE,
    BIKE_SALES_DAILY.ITEM,
    SUM(BIKE_SALES_DAILY.QTY),
    sum(BIKE_SALES_DAILY.QTY * BIKE_SALES_DAILY.NET_PRICE * BIKE_SALES_DAILY.DISC_FACTOR * BIKE_SALES_DAILY.EXCHANGE_RATE)
    FROM
    BIKE_SALES_DAILY,
    BIKE_IREF01_TRANSLATION,
    DIM_ITEM
    WHERE
    ( DIM_ITEM.ITEM_ID=BIKE_SALES_DAILY.ITEM )
    AND ( BIKE_IREF01_TRANSLATION.IREF01=DIM_ITEM.IREF01 )
    AND (
    BIKE_IREF01_TRANSLATION.COM_INDELING IN ('ELITE ', 'FIR ', 'FLIGHT DECK ', 'HOSHI ')
    AND BIKE_SALES_DAILY.CUSTOMER > 699999
    AND (BIKE_SALES_DAILY.WEEK = WEEK(CURRENT DATE)
    OR BIKE_SALES_DAILY.WEEK = ( WEEK(CURRENT DATE) )-1)
    )
    GROUP BY
    BIKE_SALES_DAILY.ORDER,
    BIKE_SALES_DAILY.COST_PRICE,
    BIKE_SALES_DAILY.NET_PRICE,
    BIKE_SALES_DAILY.ITEM


    Indexed query:

    SELECT
    BIKE_SALES_DAILY.ORDER,
    BIKE_SALES_DAILY.COST_PRICE,
    BIKE_SALES_DAILY.NET_PRICE,
    BIKE_SALES_DAILY.ITEM,
    SUM(BIKE_SALES_DAILY.QTY),
    sum(BIKE_SALES_DAILY.QTY * BIKE_SALES_DAILY.NET_PRICE * BIKE_SALES_DAILY.DISC_FACTOR * BIKE_SALES_DAILY.EXCHANGE_RATE)
    FROM
    BIKE_SALES_DAILY,
    BIKE_IREF01_TRANSLATION,
    DIM_ITEM,
    BIKE_CUSTOMER
    WHERE
    ( DIM_ITEM.ITEM_ID=BIKE_SALES_DAILY.ITEM )
    AND ( BIKE_IREF01_TRANSLATION.IREF01=DIM_ITEM.IREF01 )
    AND ( BIKE_CUSTOMER.CCUST = BIKE_SALES_DAILY.CUSTOMER )
    AND (
    BIKE_IREF01_TRANSLATION.COM_INDELING IN ('ELITE ', 'FIR ', 'FLIGHT DECK ', 'HOSHI ')
    AND BIKE_CUSTOMER.CCUST > 699999
    AND (BIKE_SALES_DAILY.WEEK = WEEK(CURRENT DATE)
    OR BIKE_SALES_DAILY.WEEK = ( WEEK(CURRENT DATE) )-1)
    )
    GROUP BY
    BIKE_SALES_DAILY.ORDER,
    BIKE_SALES_DAILY.COST_PRICE,
    BIKE_SALES_DAILY.NET_PRICE,
    BIKE_SALES_DAILY.ITEM
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  2. #2
    Join Date
    Apr 2003
    Posts
    191

    Re: non-indexed approach 4 times faster

    Hi,

    just a couple of thoughts on this performance issue:

    DB2 may not use customer indexes on the fact table at all. If DB2 thinks it would make more sense to do the aggregation first and then do the joins to the lookups, there is no index on the aggregation available.

    You *may* be able to speed things up with Summary Tables or Materialized Query Tables.

    1. Is the data type mismatch causing a lot of performance?
    Well, if the mismatch prevents DB2 from making use of an index ... Anyway, why not recreate the smaller tables with a decent data type and check?
    2. Even with use of the index the query path seems a lot less efficient ,but why and how?
    Can't tell without seeing the path.

    My recommendation would be to bit by bit rewrite query and data model until you come across a fast query. And don't forget to read up on ASTs (vs. 7) or MQTs (vs. 8).

    Johann

Posting Permissions

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