Performance question DB2 UDB v8.1.9 Linux

[Bob Stearns]

The following sql is expanded from an example from Tonkuma to my
complete application. It runs for over 6 minutes when my separate sql
statements wrapped with some PHP code can do the same thing in less than
3 seconds. For reasons which I need not go into here, I need to do this
is on sql statement. I take the performance hit because of the 7 nested
full table scans even though cow_bhid is the first primary key column of
taa, tba and tca; (bhid, herd_owner) is the whole primary key of
animals_priv (tab, tbb, tcb). What I would like to know is how to
improve the performance of this query.

select t1.animal_id, t1.locname, t1.bhid,
ta.herd_id, ta.datex, ta.time_code,
ta.datex+283 days as date_due,
tb.herd_id, tb.datex, tb.time_code,
tb.datex+283 days as date_due,
tc.herd_id, tc.in_date, tc.out_end_date,
tc.in_date+282 days as start_date_due,
tc.out_end_date+283 days as end_date_due
from is3.animals2 t1
left outer join
(select taa.*, tab.herd_id,
ROWNUMBER() OVER(PARTITION BY taa.cow_bhid) rn
from is3.service_ai taa
left outer
join is3.animals_priv tab
on tab.herd_owner_id=1
and tab.bhid=taa.bull_bhid
// where taa.cow_bhid=t1.bhid
order by taa.datex) ta
full outer join
(select tba.*, tbb.herd_id,
ROWNUMBER() OVER(PARTITION BY tba.cow_bhid) rn
from is3.service_ai tba
left outer
join is3.animals_priv tbb
on tbb.herd_owner_id=1
and tbb.bhid=tba.bull_bhid
// where tba.cow_bhid=t1.bhid
order by tba.datex) tb
on tb.cow_bhid=ta.cow_bhid
and tb.rn=ta.rn
full outer join
(select tca.*,tcb.herd_id,
ROWNUMBER() OVER(PARTITION BY tca.cow_bhid) rn
from is3.service_pasture tca
left outer
join is3.animals_priv tcb
on tcb.herd_owner_id=1
and tcb.bhid=tca.bull_bhid
// where tca.cow.bhid=t1.bhid
order by tca.in_date) tc
on tc.cow_bhid=coalesce(ta.cow_bhid, tb.cow_bhid)
and tc.rn=coalesce(ta.rn,tb.rn)
on t1.bhid=coalesce(ta.cow_bhid, tb.cow_bhid, tc.cow_bhid)
where t1.bhid in
(select bhid
from is3.animal_sets
where set_name='Bred_Cows'
and userid='jhough')
order by t1.animal_id

[Tonkuma]

Those some ideas came in mind, but not so sure and not tested.
Especially 4th idea might not work.

1) I feel ta and tb are completely same except table correlation names.
So, you may remove tb nested table expression.

2) Move the position of ORDER BY
From:
(select taa.*, tab.herd_id,
ROWNUMBER() OVER(PARTITION BY taa.cow_bhid) rn
from is3.service_ai taa
left outer
join is3.animals_priv tab
on tab.herd_owner_id=1
and tab.bhid=taa.bull_bhid
// where taa.cow_bhid=t1.bhid
order by taa.datex) ta

To:
(select taa.*, tab.herd_id,
ROWNUMBER() OVER(PARTITION BY taa.cow_bhid
ORDER BY taa.datex) rn
from is3.service_ai taa
left outer
join is3.animals_priv tab
on tab.herd_owner_id=1
and tab.bhid=taa.bull_bhid
// where taa.cow_bhid=t1.bhid
) ta

3) Make indexes
(cow_bhid, datex, bull_bhid) for taa, tba and tca.

4) Correlate with t1 by WHERE caluse in nested table expression.
And remove corresponding conditions from ON clause.
From:
(select taa.*, tab.herd_id,
ROWNUMBER() OVER(PARTITION BY taa.cow_bhid) rn
from is3.service_ai taa
left outer
join is3.animals_priv tab
on tab.herd_owner_id=1
and tab.bhid=taa.bull_bhid
// where taa.cow_bhid=t1.bhid
order by taa.datex) ta

To:
TABLE
(select taa.*, tab.herd_id,
ROWNUMBER() OVER(PARTITION BY taa.cow_bhid
ORDER BY taa.datex) rn
from is3.service_ai taa
left outer
join is3.animals_priv tab
on tab.herd_owner_id=1
and tab.bhid=taa.bull_bhid
where taa.cow_bhid=t1.bhid
) ta

Change last ON condition.
From:
on t1.bhid=coalesce(ta.cow_bhid, tb.cow_bhid, tc.cow_bhid)
To:
on 0=0

[Bob Stearns]

Tonkuma wrote:

Those some ideas came in mind, but not so sure and not tested.
Especially 4th idea might not work.

1) I feel ta and tb are completely same except table correlation names.
So, you may remove tb nested table expression.

2) Move the position of ORDER BY
From:
(select taa.*, tab.herd_id,
ROWNUMBER() OVER(PARTITION BY taa.cow_bhid) rn
from is3.service_ai taa
left outer
join is3.animals_priv tab
on tab.herd_owner_id=1
and tab.bhid=taa.bull_bhid
// where taa.cow_bhid=t1.bhid
order by taa.datex) ta

To:
(select taa.*, tab.herd_id,
ROWNUMBER() OVER(PARTITION BY taa.cow_bhid
ORDER BY taa.datex) rn
from is3.service_ai taa
left outer
join is3.animals_priv tab
on tab.herd_owner_id=1
and tab.bhid=taa.bull_bhid
// where taa.cow_bhid=t1.bhid
) ta

3) Make indexes
(cow_bhid, datex, bull_bhid) for taa, tba and tca.

4) Correlate with t1 by WHERE caluse in nested table expression.
And remove corresponding conditions from ON clause.
From:
(select taa.*, tab.herd_id,
ROWNUMBER() OVER(PARTITION BY taa.cow_bhid) rn
from is3.service_ai taa
left outer
join is3.animals_priv tab
on tab.herd_owner_id=1
and tab.bhid=taa.bull_bhid
// where taa.cow_bhid=t1.bhid
order by taa.datex) ta

To:
TABLE
(select taa.*, tab.herd_id,
ROWNUMBER() OVER(PARTITION BY taa.cow_bhid
ORDER BY taa.datex) rn
from is3.service_ai taa
left outer
join is3.animals_priv tab
on tab.herd_owner_id=1
and tab.bhid=taa.bull_bhid
where taa.cow_bhid=t1.bhid
) ta

Change last ON condition.
From:
on t1.bhid=coalesce(ta.cow_bhid, tb.cow_bhid, tc.cow_bhid)
To:
on 0=0

Item 1 was an error on my part; tb should have been base on is3.service_obs

Items 2 and 3 made only slight differences.

Item 4 was the key. After I implemented it properly, the query took 875
ms. I had tried the correlation, as evidenced by the comments, but I did
not have the TABLE keyword. I do not understand how that keyword makes
t1.bhid "visible" in the subselects, whereas without the TABLE, t1.bhid
is not defined therein.

[Serge Rielau]

Bob Stearns wrote:

Tonkuma wrote:

4) Correlate with t1 by WHERE caluse in nested table expression.
And remove corresponding conditions from ON clause.
From:
(select taa.*, tab.herd_id,
ROWNUMBER() OVER(PARTITION BY taa.cow_bhid) rn
from is3.service_ai taa
left outer
join is3.animals_priv tab
on tab.herd_owner_id=1
and tab.bhid=taa.bull_bhid
// where taa.cow_bhid=t1.bhid
order by taa.datex) ta

To:
TABLE
(select taa.*, tab.herd_id,
ROWNUMBER() OVER(PARTITION BY taa.cow_bhid
ORDER BY taa.datex) rn
from is3.service_ai taa
left outer
join is3.animals_priv tab
on tab.herd_owner_id=1
and tab.bhid=taa.bull_bhid
where taa.cow_bhid=t1.bhid
) ta

Change last ON condition.
From:
on t1.bhid=coalesce(ta.cow_bhid, tb.cow_bhid, tc.cow_bhid)
To:
on 0=0

Item 4 was the key. After I implemented it properly, the query took 875
ms. I had tried the correlation, as evidenced by the comments, but I did
not have the TABLE keyword. I do not understand how that keyword makes
t1.bhid "visible" in the subselects, whereas without the TABLE, t1.bhid
is not defined therein.

The history behind TABLE is simple.
Example:
DECLARE x INT;
SELECT * FROM T AS X(x), (SELECT * FROM S AS A(a) WHERE S.a = x) AS Z
In the original behavior from DB2 for zOS which PRECEEDS the SQL
Standard rules x will resolve to the variable x (or a higher level query).
Since being compatible with DB2 zOS at the time was considered quite
important, but the standard folks thought it important to be able to
resolve to X(x) a keyword needed to be introduced.
Originally teh name was TABLE, but it was changed a later stage (after
DB2 incorporated the change) to LATERAL.
That's what "existing customers" do to you. You can't take back semantics.
Nowadays DBS for iSeries 5R4 and DB2 for LUW V8.2 have added LATERAL as
a synonym for TABLE to comply with the standard. I'd expect DB2 for zOS
to fall in line with the next release.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab