Performance questions DB2 UDB 8.1.9 Linux

[Bob Stearns]

The following query takes 3-5 seconds which seems a bit excessive:

select * from is3.pedigree2
where bhid in
( 322380, 379701, 380901, 394336, 394342, 396039, 400764)

Furthermore, when extended to the desired use, it generates a warning
message for which I can find no explanation for the reason code:

select * from is3.pedigree2
where bhid in
(select bhid from is3.ANIMAL_SETS
where set_name = '000' and USERID = 'jhoughex')

DB2 SQL error: SQLCODE: 437, SQLSTATE: 01602, SQLERRMC: 1
Performance of this complex query may be sub-optimal. Reason code: "1".

Both queries depend on the same complex view. I have used much more
complex sql without generating such a message or having such poor
running times. Any suggestions greatly appreciated. The view is below
(longish):

CREATE VIEW IS3.Pedigree2 AS
SELECT animal.BHID AS BHID,
animal.Namex AS Name,
animal.assoc AS Assoc,
animal.prefix AS Prefix,
animal.regnum AS Regnum,

Sire.Namex AS Name_Sire,
Sire.assoc AS Assoc_Sire,
Sire.prefix AS Prefix_Sire,
Sire.regnum AS Regnum_Sire,
Dam.Namex AS Name_Dam,
Dam.assoc AS Assoc_Dam,
Dam.prefix AS Prefix_Dam,
Dam.regnum AS Regnum_Dam,

Sire_Sire.Namex AS Name_Sire_Sire,
Sire_Sire.assoc AS Assoc_Sire_Sire,
Sire_Sire.prefix AS Prefix_Sire_Sire,
Sire_Sire.regnum AS Regnum_Sire_Sire,
Sire_Dam.Namex AS Name_Sire_Dam,
Sire_Dam.assoc AS Assoc_Sire_Dam,
Sire_Dam.prefix AS Prefix_Sire_Dam,
Sire_Dam.regnum AS Regnum_Sire_Dam,
Dam_Sire.Namex AS Name_Dam_Sire,
Dam_Sire.assoc AS Assoc_Dam_Sire,
Dam_Sire.prefix AS Prefix_Dam_Sire,
Dam_Sire.regnum AS Regnum_Dam_Sire,
Dam_Dam.Namex AS Name_Dam_Dam,
Dam_Dam.assoc AS Assoc_Dam_Dam,
Dam_Dam.prefix AS Prefix_Dam_Dam,
Dam_Dam.regnum AS Regnum_Dam_Dam,

Sire_Sire_Sire.Namex AS Name_Sire_Sire_Sire,
Sire_Sire_Sire.assoc AS Assoc_Sire_Sire_Sire,
Sire_Sire_Sire.prefix AS Prefix_Sire_Sire_Sire,
Sire_Sire_Sire.regnum AS Regnum_Sire_Sire_Sire,
Sire_Sire_Dam.Namex AS Name_Sire_Sire_Dam,
Sire_Sire_Dam.assoc AS Assoc_Sire_Sire_Dam,
Sire_Sire_Dam.prefix AS Prefix_Sire_Sire_Dam,
Sire_Sire_Dam.regnum AS Regnum_Sire_Sire_Dam,
Sire_Dam_Sire.Namex AS Name_Sire_Dam_Sire,
Sire_Dam_Sire.assoc AS Assoc_Sire_Dam_Sire,
Sire_Dam_Sire.prefix AS Prefix_Sire_Dam_Sire,
Sire_Dam_Sire.regnum AS Regnum_Sire_Dam_Sire,
Sire_Dam_Dam.Namex AS Name_Sire_Dam_Dam,
Sire_Dam_Dam.assoc AS Assoc_Sire_Dam_Dam,
Sire_Dam_Dam.prefix AS Prefix_Sire_Dam_Dam,
Sire_Dam_Dam.regnum AS Regnum_Sire_Dam_Dam,
Dam_Sire_Sire.Namex AS Name_Dam_Sire_Sire,
Dam_Sire_Sire.assoc AS Assoc_Dam_Sire_Sire,
Dam_Sire_Sire.prefix AS Prefix_Dam_Sire_Sire,
Dam_Sire_Sire.regnum AS Regnum_Dam_Sire_Sire,
Dam_Sire_Dam.Namex AS Name_Dam_Sire_Dam,
Dam_Sire_Dam.assoc AS Assoc_Dam_Sire_Dam,
Dam_Sire_Dam.prefix AS Prefix_Dam_Sire_Dam,
Dam_Sire_Dam.regnum AS Regnum_Dam_Sire_Dam,
Dam_Dam_Sire.Namex AS Name_Dam_Dam_Sire,
Dam_Dam_Sire.assoc AS Assoc_Dam_Dam_Sire,
Dam_Dam_Sire.prefix AS Prefix_Dam_Dam_Sire,
Dam_Dam_Sire.regnum AS Regnum_Dam_Dam_Sire,
Dam_Dam_Dam.Namex AS Name_Dam_Dam_Dam,
Dam_Dam_Dam.assoc AS Assoc_Dam_Dam_Dam,
Dam_Dam_Dam.prefix AS Prefix_Dam_Dam_Dam,
Dam_Dam_Dam.regnum AS Regnum_Dam_Dam_Dam

FROM is3.animals animal
/* Sire and Dam of the Animal */
JOIN is3.animals Sire ON animal.Sire_bhid = Sire.bhid
JOIN is3.animals Dam ON animal.Dam_bhid = Dam.bhid
/* Sire and Dam of the Sire */
JOIN is3.animals Sire_Sire ON Sire.Sire_bhid = Sire_Sire.bhid
JOIN is3.animals Sire_Dam ON Sire.Dam_bhid = Sire_Dam.bhid
/* Sire and Dam of the Dam */
JOIN is3.animals Dam_Sire ON Dam.Sire_bhid = Dam_Sire.bhid
JOIN is3.animals Dam_Dam ON Dam.Dam_bhid = Dam_Dam.bhid
/* Sire and Dam of the Sire_Sire */
JOIN is3.animals Sire_Sire_Sire ON Sire_Sire.Sire_bhid =
Sire_Sire_Sire.bhid
JOIN is3.animals Sire_Sire_Dam ON Sire_Sire.Dam_bhid =
Sire_Sire_Dam.bhid
/* Sire and Dam of the Sire_Dam */
JOIN is3.animals Sire_Dam_Sire ON Sire_Dam.Sire_bhid =
Sire_Dam_Sire.bhid
JOIN is3.animals Sire_Dam_Dam ON Sire_Dam.Dam_bhid = Sire_Dam_Dam.bhid
/* Sire and Dam of the Dam_Sire */
JOIN is3.animals Dam_Sire_Sire ON Dam_Sire.Sire_bhid =
Dam_Sire_Sire.bhid
JOIN is3.animals Dam_Sire_Dam ON Dam_Sire.Dam_bhid = Dam_Sire_Dam.bhid
/* Sire and Dam of the Dam_Dam */
JOIN is3.animals Dam_Dam_Sire ON Dam_Dam.Sire_bhid = Dam_Dam_Sire.bhid
JOIN is3.animals Dam_Dam_Dam ON Dam_Dam.Dam_bhid = Dam_Dam_Dam.bhid

[mike]

Maybe you could mention what indexes are present
on the underlying tables (animals, pedigree2, etc), and their
cardinality, and
the query-optimisation level you are using, and whether
the statistics are up to date, and what the access
plan shows?

[Ian]

Bob Stearns wrote:

The following query takes 3-5 seconds which seems a bit excessive:

select * from is3.pedigree2
where bhid in
( 322380, 379701, 380901, 394336, 394342, 396039, 400764)

It would be interesting to see the compilation time for this query.
You may find that it is significant.

Furthermore, when extended to the desired use, it generates a warning
message for which I can find no explanation for the reason code:

select * from is3.pedigree2
where bhid in
(select bhid from is3.ANIMAL_SETS
where set_name = '000' and USERID = 'jhoughex')

DB2 SQL error: SQLCODE: 437, SQLSTATE: 01602, SQLERRMC: 1
Performance of this complex query may be sub-optimal. Reason code: "1".

Both queries depend on the same complex view. I have used much more
complex sql without generating such a message or having such poor
running times. Any suggestions greatly appreciated. The view is below
(longish):

I don't know what you mean about "much more complex SQL" - the view is a
15 way join, and adding the subselect to the query make this effectively
a 16-way join.

The problem is that you don't have enough memory (in STMTHEAP) for the
optimizer to compute the optimal join order for the 16 tables, so the
optimizer reverts to greedy join enumeration (which uses less memory,
at a cost of not selecting the optimal plan).
Have you tried making your view recursive? Not sure if this would help.