Performance Tuning...........

UDA
l_k
Enthusiast

Performance Tuning...........

Hi All,
The below query is taking more than 1 hour 30 mintues to produce the result and cosuming more sumIO also.

query:

SELECT
Account_Num
,Account_Modifier_Num
,Acct_Ccy_Cumulative_Invest_Amt
,Acct_Crncy_Redemption_Amt
,Acct_Crncy_Transfer_in_Amt
,Acct_Crncy_Transfer_out_Amt
,Acct_Crncy_Cash_Dividend_Amt
,Acct_Ccy_Curr_Invest_Mkt_value
FROM db_cm.T110_INVEST_ACCT_SUMMARY_DD IASD
GROUP BY ACCOUNT_NUM, ACCOUNT_MODIFIER_NUM
Qualify Rank(IASD.Invest_Account_Summary_Dt) = 1;

Pls anybody help me how to modify the above query to reduce the execution timing in a better way?

Thanks
Kumar
6 REPLIES
Enthusiast

Re: Performance Tuning...........

Have you evaluated the explain plan? What do the table demographics look like? Your group by and qualify functions are certainly part of the performance issues. Can you narrow down the results with a SARG somehow to reduce the number of rows that need to be grouped and qualified?

Is this trying to find the latest date account_summary_dt by account_num and account_modifier_num?
l_k
Enthusiast

Re: Performance Tuning...........

Yes..i have evaluated the EXPLAIN and it's showing high confidence.The column account_summary_dt is a primary index and stat. is update also.Moreover the values in account_summary_dt is unique values.

Can you please let me know what is SARG and how to use that in the above query?

Thanks
Lavakumar
Enthusiast

Re: Performance Tuning...........

The SARG is shorthand for Search Argument, i.e. a where clause. If your query is indeed trying to find the latest account_summary_dt and account modifier_num, there are easier ways to get that answer.
l_k
Enthusiast

Re: Performance Tuning...........

It's finding the latest account_summary_dt....

please Let me know how to achieve that?
Enthusiast

Re: Performance Tuning...........

for each account number?
l_k
Enthusiast

Re: Performance Tuning...........

Yeah..it's finding for each account number............( for the account_summary_dt's rank is =1)

Please help me how to write an optimized query for that?...it's very urgent...