RE: Fun with DB2/400 Optimizer...well, not quite.

Ryan, 

A while back I ran across an IBM article "Art and science of SQL performance
tuning" (http://www.common.be/pdffiles/16042002DB2ArtandScience.pdf) and had
to chuckle at the title as it sure is more art than science, at least in my
experience :)

There are two ways SQL views can be implemented on the DB2 for System i,
view materialization and view composition.  You want to try and avoid view
materialization if possible, and it should be possible with your query as
it's relatively straightforward.
IBM has been doing a good job of getting more queries toward view
composition with new query engine (SQE).  So, one thing to make sure is that
your query is not going down CQE path (see latest Centerfield article on the
method to determine which query engine your statement is hitting -
http://www.centerfieldtechnology.com/publications/archive/April%202006.pdf).

Another thing I learned as I'm tuning SQL on System i is that the answer is
always "It depends".  There are just too many factors query optimizer takes
into consideration when it's making its decisions for anyone to claim
anything with full certainty.  Another thing that exasperates this situation
is that IBM is hard at work changing, enhancing and optimizing the query
optimizer all the time, so what you know today may be outdated tomorrow.

Query optimization is iterative process so view my suggestion as iteration 1
and cross your fingers :)

CREATE INDEX T1IDX1 ON T1 (GLLT ASC, GLDSYJ ASC, GLDGJ DESC)
CREATE INDEX T1IDX2 ON T1 (GLMCU ASC)
CREATE INDEX T2IDX1 ON T2 (MCMCU ASC)

WITH t AS 
{
SELECT GLMCU,
       GLOBJ,
       GLSUB,
       GLEXA,
       GLEXR,
       GLAA,
       GLIVD,
       GLDGJ,
       GLDCT,
       GLPO,
       GLLT,
       GLDSYJ
FROM t1
WHERE GLLT='AA' AND GLDSYJ>=106072 AND GLDGJ<=105365 
}
SELECT 
  t.GLMCU,
  t2.MCDC,
  t2.MCRP01,
  t.GLOBJ,
  t.GLSUB,
  t.GLEXA,
  t.GLEXR,
  t.GLAA,
  t.GLIVD,
  t.GLDGJ,
  t.GLDCT,
  t.GLPO,
  t.GLLT,
  t.GLDSYJ
FROM t INNER JOIN t2 ON t2.MCMCU = t1.GLMCU

Test it first with some simple selects before you building a view on top of
it. I'm curious to see what your testing shows.
Good luck.

Elvis

-----Original Message-----
Subject: Fun with DB2/400 Optimizer...well, not quite.

I have a recurring problem with the DB2/400 optimizer that I'm hoping others
can shed some light on. This post is pretty long (sorry) so I will begin
with my questions before you guys get tired of reading.  ;-)

____________________________________________________________________________
__
My first issue is that DB2/400 seems to want to table scan WAY more often on
this particular data selection than it should.  The selection criteria only
selects 4665 records of the 18.5 million in a table.  There is some index
experimentation I could do here.  For instance, my index is actually
beginning with my least selective column (GLLT), however I've done this
because I've had issues in the past by starting with columns I am doing
range (BETWEEN) or >=/<= selection on.  Is there a way to force the
optimizer to use indexes, or force it to select the 4665 relevant records
BEFORE the join???

My second issue is the creation of views.  Most of these queries I have to
turn into views.  Even after I have a very well tuned query / index
combination, turning the SQL into a view ALMOST ALWAYS (99%) results in
table scans.
____________________________________________________________________________
__

Some general info...

Table t1 is about 100 columns wide, has 18.5 million rows, and is 14GB in
size.  Table t2 is about 50 columns wide, has 4500 rows, and is 3mb in size

There are lots of indexes, but the relevant ones are:

IDX_T1 (GLLT asc, GLDGJ asc, GLDSYJ asc, GLMCU asc)
IDX_t2 (MCMCU asc)

Instead of obfuscating with pseudo code, here is the real deal.

Query1:

SELECT t1.GLMCU
, t2.MCDC
, t2.MCRP01
, t1.GLOBJ
, t1.GLSUB
, t1.GLEXA
, t1.GLEXR
, t1.GLAA
, t1.GLIVD
, t1.GLDGJ
, t1.GLDCT
, t1.GLPO
, t1.GLLT
, t1.GLDSYJ
FROM t1
INNER JOIN t2 ON t2.MCMCU = t1.GLMCU
WHERE t1.GLLT='AA' AND t1.GLDSYJ>=106072 AND t1.GLDGJ<=105365

Results mixed.
50%:  TABLE SCAN on t1, TABLE SCAN on t2 with temp hash table, hash join
50%:  Index Scan IDX_t1, Index Scan IDX_t2, table probes, then nested loop
join.

If I turn Query1 into a view and SELECT * FROM View, this ALWAYS results in
tables scans and overnight processing.

Query2:

SELECT t1.GLMCU
, t2.MCDC
, t2.MCRP01
, t1.GLOBJ
, t1.GLSUB
, t1.GLEXA
, t1.GLEXR
, t1.GLAA
, t1.GLIVD
, t1.GLDGJ
, t1.GLDCT
, t1.GLPO
, t1.GLLT
, t1.GLDSYJ
FROM t1
INNER JOIN t2 ON t2.MCMCU = t1.GLMCU AND t1.GLLT='AA' AND t1.GLDSYJ>=106072
AND t1.GLDGJ<=105365

Results:
100%:  Index Scan IDX_t1, Index Scan IDX_t2, table probes, then nested loop
join.

If it turn Query 2 into a view and SELECT * FROM View, results are mixed.
5%:  Index Scan IDX_t1, Index Scan IDX_t2, table probes, then nested loop
join.
95%  TABLE SCAN on t1, TABLE SCAN on t2 with temp hash table, hash join

Query3:

SELECT t2.MCDC,  t2.MCRP01, ilv1.*
FROM
(
SELECT GLMCU
, GLOBJ
, GLSUB
, GLEXA
, GLEXR
, GLAA
, GLIVD
, GLDGJ
, GLDCT
, GLPO
, GLLT
, GLDSYJ
FROM t1 WHERE GLLT='AA' AND GLDSYJ>=106072 AND GLDGJ<=105365
) ilv1
INNER JOIN t2 ON t2.MCMCU = ilv1.GLMCU

Results:
100%:  TABLE SCAN on t1, TABLE SCAN on t2 with temp hash table, hash join

Turning this into a view has same result.


Any ideas?