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?

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

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 ......

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.

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.......