|
|
|
Supreme Being
Group: Forum Members
Last Login: 9/30/2008 4:39:37 PM
Posts: 105,
Visits: 84
|
|
I am trying to reduce this Query's runtime by 50% or more!
I've tried a number of things...
INSERT INTO MARC_MCGUCKIAN.Rec_Clm_Est_WH_CT1 (policy_id, policy_desc, claim_ver_id, claim_code_desc, region, currency, product_type,estrec_code, riskno, match_est, match_recest, pay_est, rec_est)
SELECT a15.policy_id, a15.policy_desc, a12.claim_ref_id, transfer_claim_ref, a15.Po_corp_unit_id, currency_id, SUBSTR(a13.Pr_type_id,2,3), Ca_est_rec_id, TRIM(SUBSTR(Claim_line_desc,23,2)), 'N', 'N',
CAST(SUM(CAST(F_CL_EST_PAY AS NUMERIC(16,2))) AS NUMERIC(16,2)),
CAST(SUM(CAST(F_CL_EST_RECOVER AS NUMERIC(16,2))) AS NUMERIC(16,2))
FROM MARC_MCGUCKIAN.FAT_BSE_CL_PAY_CRE_TRANS1 a11
JOIN cav_lu_claim_reference a12
ON (a11.claim_ref_id = a12.claim_ref_id)
JOIN prt_lu_product a13
ON (a11.Product_id = a13.Product_id)
JOIN POt_lu_policy a15
ON (a11.Policy_id = a15.Policy_id)
WHERE CT_TYPE_ID <> '90'
GROUP BY a12.claim_ref_id, Ca_est_rec_id, transfer_claim_ref, a15.Po_corp_unit_id, currency_id,
TRIM(SUBSTR(Claim_line_desc,23,2)), a15.policy_id, a15.policy_desc, SUBSTR(a13.Pr_type_id,2,3);
MARC_MCGUCKIAN.FAT_BSE_CL_PAY_CRE_TRANS1 = 2906701 Rows
cav_lu_claim_reference = 97622 Rows
prt_lu_product = 474 Rows
POt_lu_policy = 3506973
All tables have up to date stats, there are no product joins. I have dropped stats completely and the performance reduced by 10%. There are no other recommended stats from diagnostic helpstats. I don't know what to do next. Any ideas? I rarely use indices.
Explanation
1) First, we lock a distinct CQA_data_t."pseudo table" for read on a
RowHash to prevent global deadlock for CQA_data_t.PRt_lu_Product.
2) Next, we lock a distinct CQA_data_t."pseudo table" for read on a
RowHash to prevent global deadlock for CQA_data_t.POT_LU_POLICY.
3) We lock a distinct CQA_DATA_T."pseudo table" for read on a RowHash
to prevent global deadlock for CQA_DATA_T.CAV_LU_CLAIM_REFERENCE.
4) We lock a distinct MARC_MCGUCKIAN."pseudo table" for read on a
RowHash to prevent global deadlock for MARC_MCGUCKIAN.a11.
5) We lock a distinct MARC_MCGUCKIAN."pseudo table" for write on a
RowHash to prevent global deadlock for
MARC_MCGUCKIAN.Rec_Clm_Est_WH_CT1.
6) We lock CQA_data_t.PRt_lu_Product for read, we lock
CQA_data_t.POT_LU_POLICY for read, we lock
CQA_DATA_T.CAV_LU_CLAIM_REFERENCE for read, we lock
MARC_MCGUCKIAN.a11 for read, and we lock
MARC_MCGUCKIAN.Rec_Clm_Est_WH_CT1 for write.
7) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from
CQA_data_t.PRt_lu_Product by way of an all-rows scan with no
residual conditions into Spool 4 (all_amps), which is
duplicated on all AMPs. The size of Spool 4 is estimated
with high confidence to be 3,318 rows. The estimated time
for this step is 0.01 seconds.
2) We do an all-AMPs RETRIEVE step from MARC_MCGUCKIAN.a11 by
way of an all-rows scan with a condition of (
"(MARC_MCGUCKIAN.a11.Ct_type_id <> '90') AND (NOT
(MARC_MCGUCKIAN.a11.Claim_ref_id IS NULL ))") into Spool 5
(all_amps), which is built locally on the AMPs. The input
table will not be cached in memory, but it is eligible for
synchronized scanning. The result spool file will not be
cached in memory. The size of Spool 5 is estimated with high
confidence to be 2,807,964 rows. The estimated time for this
step is 19.68 seconds.
8) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of an
all-rows scan, which is joined to Spool 5 (Last Use) by way of an
all-rows scan. Spool 4 and Spool 5 are joined using a single
partition hash join, with a join condition of ("Product_id =
Product_id"). The result goes into Spool 6 (all_amps), which is
redistributed by hash code to all AMPs. Then we do a SORT to
order Spool 6 by row hash. The size of Spool 6 is estimated with
low confidence to be 2,807,964 rows. The estimated time for this
step is 17.23 seconds.
9) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from
CQA_DATA_T.CAV_LU_CLAIM_REFERENCE by way of an all-rows scan
with no residual conditions into Spool 7 (all_amps), which is
duplicated on all AMPs. Then we do a SORT to order Spool 7
by row hash. The size of Spool 7 is estimated with high
confidence to be 683,354 rows. The estimated time for this
step is 2.17 seconds.
2) We do an all-AMPs JOIN step from CQA_data_t.POT_LU_POLICY by
way of a RowHash match scan with no residual conditions,
which is joined to Spool 6 (Last Use) by way of a RowHash
match scan. CQA_data_t.POT_LU_POLICY and Spool 6 are joined
using a merge join, with a join condition of ("Policy_id =
CQA_data_t.POT_LU_POLICY.Policy_id"). The input table
CQA_data_t.POT_LU_POLICY will not be cached in memory, but it
is eligible for synchronized scanning. The result goes into
Spool 8 (all_amps), which is built locally on the AMPs. Then
we do a SORT to order Spool 8 by row hash. The result spool
file will not be cached in memory. The size of Spool 8 is
estimated with low confidence to be 2,807,964 rows. The
estimated time for this step is 25.17 seconds.
10) We do an all-AMPs JOIN step from Spool 7 (Last Use) by way of a
RowHash match scan, which is joined to Spool 8 (Last Use) by way
of a RowHash match scan. Spool 7 and Spool 8 are joined using a
merge join, with a join condition of ("Claim_ref_id = claim_ref_id").
The result goes into Spool 3 (all_amps), which is built locally on
the AMPs. The result spool file will not be cached in memory.
The size of Spool 3 is estimated with low confidence to be
2,807,964 rows. The estimated time for this step is 5.00 seconds.
11) We do a single-AMP SUM step to aggregate from Spool 3 (Last Use)
by way of an all-rows scan, and the grouping identifier in field 1.
Aggregate Intermediate Results are computed locally, then placed
in Spool 9. The aggregate spool file will not be cached in memory.
The size of Spool 9 is estimated with low confidence to be
2,807,964 rows. The estimated time for this step is 19.21 seconds.
12) We do an all-AMPs RETRIEVE step from Spool 9 (Last Use) by way of
an all-rows scan into Spool 1 (all_amps), which is redistributed
by hash code to all AMPs. Then we do a SORT to order Spool 1 by
row hash. The result spool file will not be cached in memory.
The size of Spool 1 is estimated with low confidence to be
2,807,964 rows. The estimated time for this step is 18.04 seconds.
13) We do an all-AMPs MERGE into MARC_MCGUCKIAN.Rec_Clm_Est_WH_CT1
from Spool 1 (Last Use).
14) We spoil the parser's dictionary cache for the table.
15) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> No rows are returned to the user as the result of statement 1.
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 7/24/2008 8:31:03 AM
Posts: 109,
Visits: 22
|
|
Hi,
Can you provide a li'l more info. Can you provide the table structures so see the primary indexes. And also highlight which of these tables are "look-up" tables?
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 9/17/2008 4:11:04 AM
Posts: 80,
Visits: 91
|
|
Try the below,
INSERT INTO MARC_MCGUCKIAN.Rec_Clm_Est_WH_CT1 (policy_id, policy_desc, claim_ver_id, claim_code_desc, region, currency, product_type,estrec_code, riskno, match_est, match_recest, pay_est, rec_est)
SELECT a15.policy_id, a15.policy_desc, a12.claim_ref_id, transfer_claim_ref, a15.Po_corp_unit_id, currency_id, SUBSTR(a13.Pr_type_id,2,3), Ca_est_rec_id, TRIM(SUBSTR(Claim_line_desc,23,2)), 'N', 'N',
CAST(SUM(CAST(F_CL_EST_PAY AS NUMERIC(16,2))) AS NUMERIC(16,2)),
CAST(SUM(CAST(F_CL_EST_RECOVER AS NUMERIC(16,2))) AS NUMERIC(16,2))
FROM MARC_MCGUCKIAN.FAT_BSE_CL_PAY_CRE_TRANS1 a11
JOIN prt_lu_product a13
ON (a11.Product_id = a13.Product_id)
JOIN cav_lu_claim_reference a12
ON (a11.claim_ref_id = a12.claim_ref_id)
JOIN POt_lu_policy a15
ON (a11.Policy_id = a15.Policy_id)
WHERE CT_TYPE_ID <> '90'
GROUP BY a12.claim_ref_id, Ca_est_rec_id, transfer_claim_ref, a15.Po_corp_unit_id, currency_id,
TRIM(SUBSTR(Claim_line_desc,23,2)), a15.policy_id, a15.policy_desc, SUBSTR(a13.Pr_type_id,2,3);
I have rearranged the join table a13 ahead of a12 as a13 has fewer no of records to join with the left table.
Not sure this will be effective, but u can check if this works :-)
Regards,
Sakthi Do your duty Dont expect the reward, God will give the benefit for you
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 4/22/2008 11:38:42 AM
Posts: 4,
Visits: 11
|
|
try to cheat the optimizer using left join and push the rightside col is not null in the where clause to mimic a inner join. Using this approach you can "pin-down" the join order and try to eliminate as any rows as possible at the early stage. Optimizer sometimes cannot determine the best join order since the permutation of N table innerjion is N!, which can easily be a very large number. However, left join is not commutative, therefore, you can "outsmart" optimizer.
For example:
sel *
from a inner join b inner join c inner join d ... inner join K
where
is the same as
sel k left jion a inner jion b inner jion c... inner join j
where
and a.col is not null
|
|
|
|