Why is a JOIN between sources so slow?
Hi,
We are trying to create a single JDBC source for our applications and create views that will join between two database schemas and/or two JDBC sources..
We hoped that the caching and other features would mean that joins between two schemas or JDBC sources would be faster than materialized views, stored procedures and DB Links we currently use. However, an initial test to replace a regular view (that internally makes calls to stored functions) is 600x as slow.
The unusual thing is that the views themselves, when they don't join across sources, are very effective. To get one of the items from "schema A" -- without the columns from schema B only takes 1 second. And to get the results from schema B directly takes two seconds But when joined, the result takes 4 minutes.
I've trimmed the code below to the minimum, hopefully it shows that I sub-select the ASSIST schema, then join that to the "..UNBILLED..." view which is in a different schema. I presumed that it would run the inner select (once), then the outer join with the results. But this still takes 4 minutes.
Any thoughts?
Need actual source code?
Thank you,
Hank
-- Example query, that takes 4 minutes, but the view it replaces only takes 4 seconds.
CREATE VIEW TEST3_VDB (
...
) AS SELECT
...
-- Make the sub select query to schema 1:
FROM (SELECT
...
FROM ASSIST.ACCPT_RPT_DETAIL ard
JOIN ASSIST.PROCUREMENT pr
...
AND aribp.SUBTASK_REF_ID = libp.SUBTASK_REF_ID ) lamibp
-- And join that to the schema 2 query, which is in its own view:
JOIN LAM_BP_UNBILLED_TO_ORIG_VDB unbilled
...
-- Original view that calls stored functions for some columns:
select * from v_lam_accpt_rpt_item_bill_pref where accpt_rpt_item_id = 187518;
Elapsed Time: 0 hr, 0 min, 4 sec, 116 ms.
-- Snapshot view replacing above:
select * from lam_accpt_rpt_item_bill_pref_vdb where accpt_rpt_item_id = 187518;
Elapsed Time: 0 hr, 6 min, 23 sec, 974 ms.
-- Snapshot view replacing stored procedures:
select * from LAM_BP_UNBILLED_TO_ORIG_VDB where subtask_ref_id = 151976;
Elapsed Time: 0 hr, 0 min, 2 sec, 82 ms.
-- Snapshot test that joins only schema A:
select * from TEST2_VDB where accpt_rpt_item_id = 187518;
Elapsed Time: 0 hr, 0 min, 1 sec, 34 ms.
-- Snapshot view in subselect join to table_master VDB view:
select * from TEST3_VDB where accpt_rpt_item_id = 187518
Elapsed Time: 0 hr, 4 min, 1 sec, 853 ms.
