BHID id the (only) primary key, and thus index, of Animals, which is the
only real table involved.
Pedigree2 is the view based on Animals.
runstats on table is3.animals ON ALL COLUMNS AND DETAILED INDEXES was
run before the test.
The test was run multiple times to eliminate initial buffering bias.
The execution plan shows lots of relatively expensive nested loop JOINs,
which surprises me because all the JOINs are on the primary key of animals.
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