Query Performance

UDA
Enthusiast

Query Performance

Hi

Can you please help with the view below, I have problem using the QUALIFY statmement, it takes longer to return the result and when the view below joined with other views it becomes a nightmare.

Please assist if there is a better option i can use to replace the QUALIFY...PARTITION statement.

REPLACE VIEW "development"."VW_Acct_BRI"
AS
LOCKING TABLE production.ACCOUNT_RISK_GRADE FOR ACCESS
SELECT
TRIM(CAST(CAST(Account_Num AS DECIMAL(18,0)) AS VARCHAR(20))
|| CAST(CAST(Account_Modifier_Num AS SMALLINT) AS VARCHAR(20))
|| (COALESCE(CAST(SB_Account_Open_Dt AS CHAR(10)),'')))
AS Account_Num
, Account_Num AS O_Account_Num
, Account_modifier_Num AS O_Account_modifier_Num
, SB_Account_Open_Dt AS O_Account_Open_Dt
, Risk_Rating_Cd AS BRI
FROM
production.ACCOUNT_RISK_GRADE
WHERE
Risk_Rating_Type_Cd IN (1,2)
AND
Risk_Rating_Method_Id = 1
QUALIFY RANK() OVER (PARTITION BY Account_Num, Account_modifier_Num, SB_Account_Open_Dt, Risk_Rating_Type_Cd, Risk_Rating_Method_Id
ORDER BY Risk_Report_Dt DESC) = 1;
3 REPLIES

Re: Query Performance

Dude,

Believe you can change the current where conditions to increse ur performance..

REPLACE VIEW "development"."VW_Acct_BRI"
AS
LOCKING TABLE production.ACCOUNT_RISK_GRADE FOR ACCESS
SELECT
TRIM(CAST(CAST(Account_Num AS DECIMAL(18,0)) AS VARCHAR(20))
|| CAST(CAST(Account_Modifier_Num AS SMALLINT) AS VARCHAR(20))
|| (COALESCE(CAST(SB_Account_Open_Dt AS CHAR(10)),'')))
AS Account_Num
, Account_Num AS O_Account_Num
, Account_modifier_Num AS O_Account_modifier_Num
, SB_Account_Open_Dt AS O_Account_Open_Dt
, Risk_Rating_Cd AS BRI
FROM
production.ACCOUNT_RISK_GRADE
AND
Risk_Rating_Type_Cd IN (1,2)
WHERE
Risk_Rating_Method_Id = 1
QUALIFY RANK() OVER (PARTITION BY Account_Num, Account_modifier_Num, SB_Account_Open_Dt, Risk_Rating_Type_Cd, Risk_Rating_Method_Id
ORDER BY Risk_Report_Dt DESC) = 1;

Still not sure,but you can have a try..

Enthusiast

Re: Query Performance

It seems the objective is to get "distinct" values of certain columns. In which case you might want to try using Group by / distinct to achieve the same. One may be better than the other depending on your data demographics.

If the table is very "wide" and the query is repetitive in nature, you could also explore the options of implementing an index to cover the query.
Enthusiast

Re: Query Performance

I think ur landing up in a product join(if u have seen ur explain) as u have aliased the columns but trying to partition on old column names,which may result in a product join.
U can check that out and see if it helps,otherwise u have to go thru the basics of changing the index of the table as ur selecting out from single table only and may create a single table join index.

Also,i do not understand y ru trying to cast the the columns twice as when ur using a TRIM and concatenate operator,TD internally will cast them to CHARACTER.