I am having some serious performance problems using outer join. Any insight or hints that you can offer would be most appreciated!
Here is my timing script.
echo "Inner Join"
date
db2 "select count(*) from ( OSI join SSL on OSI.S_Key = SSL.S_Key and OSI.SubCaty = SSL.SubCat)"
echo "Union with inner join union all Not Exists"
date
db2 "select 'Inner: ', count(*) from OSI join SSL on OSI.S_Key = SSL.S_Key and OSI.SubCat = SSL.SubCat UNION ALL select 'Not Exists: ', count(*) from OSI where not exists (select 'x' from SSL where OSI.S_Key = SSL.S_Key and OSI.SubCat = SSL.SubCat)"
echo "Outer Join (RIGHT)"
date
db2 "select count(*) from OSI right outer join SSL on OSI.S_Key = SSL.S_Key and OSI.SubCat = SSL.SubCa)"
date
Results in:
Inner Join
Fri Oct 12 16:04:50 MDT 2001
1
-----------
895100
1 record(s) selected.
Union with inner join union all Not Exists
Fri Oct 12 16:04:58 MDT 2001
1 2
------------ -----------
Not Exists: 135225
Inner: 895100
2 record(s) selected.
Outer Join (RIGHT)
Fri Oct 12 16:09:16 MDT 2001
Or: Inner join runs in about 10 seconds, inner union not exists in about 4.25 minutes, Right outer hasn't yet returned (I keep cancelling it) - been running 20 minutes at this point.
So, the questions are: What is so slow about the outer joins? Is there anything I can do to speed them up? The performance on the inner join would indicate that I have appropriate indexing in place (I believe I do).
TTIA
Here is my timing script.
echo "Inner Join"
date
db2 "select count(*) from ( OSI join SSL on OSI.S_Key = SSL.S_Key and OSI.SubCaty = SSL.SubCat)"
echo "Union with inner join union all Not Exists"
date
db2 "select 'Inner: ', count(*) from OSI join SSL on OSI.S_Key = SSL.S_Key and OSI.SubCat = SSL.SubCat UNION ALL select 'Not Exists: ', count(*) from OSI where not exists (select 'x' from SSL where OSI.S_Key = SSL.S_Key and OSI.SubCat = SSL.SubCat)"
echo "Outer Join (RIGHT)"
date
db2 "select count(*) from OSI right outer join SSL on OSI.S_Key = SSL.S_Key and OSI.SubCat = SSL.SubCa)"
date
Results in:
Inner Join
Fri Oct 12 16:04:50 MDT 2001
1
-----------
895100
1 record(s) selected.
Union with inner join union all Not Exists
Fri Oct 12 16:04:58 MDT 2001
1 2
------------ -----------
Not Exists: 135225
Inner: 895100
2 record(s) selected.
Outer Join (RIGHT)
Fri Oct 12 16:09:16 MDT 2001
Or: Inner join runs in about 10 seconds, inner union not exists in about 4.25 minutes, Right outer hasn't yet returned (I keep cancelling it) - been running 20 minutes at this point.
So, the questions are: What is so slow about the outer joins? Is there anything I can do to speed them up? The performance on the inner join would indicate that I have appropriate indexing in place (I believe I do).
TTIA