Hemanth Kumar
asked on
How do I simplify a SQL query to make it short and better performance
Dear Team,
I am pasting a SQL query which is being used for reporting on monthly basis. I believe the query is too long and runs for long to generate the report. Please look into it and let me know if I can simplify it to make it a shorter query and better faster performance
I am pasting a SQL query which is being used for reporting on monthly basis. I believe the query is too long and runs for long to generate the report. Please look into it and let me know if I can simplify it to make it a shorter query and better faster performance
SELECT
BILL_TO_SIEBEL_ACCOUNT.ACCOUNT_NUMBER as Bill_To_Account_Number,
COMPANY_CD_DIM.INTEGRATION_ID,
BILL_TO_SIEBEL_ACCOUNT.SUBSCRIBER_NUM,
SIEBEL_ACCOUNTS.ACCOUNT_NUMBER,
SIEBEL_ACCOUNTS.ACCOUNT_MANAGER as Location_Acct_Manager,
SIEBEL_ACCOUNTS.ACCOUNT_NAME,
SIEBEL_ACCOUNTS.COUNTRY,
SIEBEL_ACCOUNTS.REGION,
SIEBEL_ACCOUNTS.FAMILY,
SIEBEL_ACCOUNTS.ACCOUNT_CLASS,
SIEBEL_ACCOUNTS.BUSINESS_CHANNEL,
SIEBEL_ACCOUNTS.SIBL_ULTIMT_PARENT_NAME,
SIEBEL_ACCOUNTS.X_REL_MODEL as Relationship_Model,
SIEBEL_ACCOUNTS.ULITMATE_PARENT_CATEGORY as Ultimate_Parent_Category,
SIEBEL_ACCOUNTS.ORGANIZATION_TYPE,
Sum(MDW.EXIT_RATE_FACT.EXIT_RATE*(MDW.ASSET_ALLOC_BRIDGE.ALLOCATION_PERCENT + MDW.ASSET_ALLOC_BRIDGE.CORRECTION_FACTOR) *CURRENT_PLAN_RATE.USD_EXCHANGE_RATE::DOUBLE) Exit_rate,
EXIT_RATE_FACT.SOURCE_SYSTEM,
EXIT_RATE_FACT.SOURCE_COMPANY,
nvl(SIEBEL_ACCOUNTS.CUSTOMER_GROUP,'Unassigned') as Customer_Group,
CALENDAR_DIM.PER_NAME_MONTH as Month_Year
FROM
MDW.CALENDAR_DIM INNER JOIN MDW.EXIT_RATE_FACT ON (MDW.CALENDAR_DIM.CALENDAR_D_KEY= MDW.EXIT_RATE_FACT.PERIOD_D_KEY)
INNER JOIN MDW.PRODUCT_DIM PRODUCT ON (PRODUCT.PRODUCT_D_KEY=MDW.EXIT_RATE_FACT.SAP_MATERIAL_D_KEY)
INNER JOIN MDW.PRODUCT_DIM PRODUCT_SAP ON (PRODUCT_SAP.PRODUCT_D_KEY=PRODUCT.SAP_MATERIAL_D_KEY)
LEFT OUTER JOIN MDW.CURRENCY_HISTORY_DIM CURRENT_PLAN_RATE ON (NVL(MDW.EXIT_RATE_FACT.CURRENCY_D_KEY,-1)=CURRENT_PLAN_RATE.CURRENCY_D_KEY AND CURRENT_PLAN_RATE.CURRENT_FLAG='Y')
INNER JOIN MDW.ACCOUNT_DIM SHIP_TO_ACCOUNTS ON (SHIP_TO_ACCOUNTS.DW_CUSTOMER_D_KEY=MDW.EXIT_RATE_FACT.SHIP_TO_CUSTOMER_D_KEY)
INNER JOIN MDW.ACCOUNT_DIM SHIP_TO_NATIVE_ACCOUNTS ON (SHIP_TO_NATIVE_ACCOUNTS.DW_CUSTOMER_D_KEY=SHIP_TO_ACCOUNTS.SIEBEL_CUSTOMER_D_KEY)
INNER JOIN MDW.ACCOUNT_DIM SIEBEL_ACCOUNTS ON (SIEBEL_ACCOUNTS.DW_CUSTOMER_D_KEY=SHIP_TO_NATIVE_ACCOUNTS.SIEBEL_CUSTOMER_D_KEY)
INNER JOIN MDW.ACCOUNT_DIM BILL_TO_ACCOUNTS ON (MDW.EXIT_RATE_FACT.BILL_TO_CUSTOMER_D_KEY=BILL_TO_ACCOUNTS.DW_CUSTOMER_D_KEY)
INNER JOIN MDW.ACCOUNT_DIM BILL_TO_NATIVE_ACCOUNTS ON (BILL_TO_ACCOUNTS.SIEBEL_CUSTOMER_D_KEY=BILL_TO_NATIVE_ACCOUNTS.DW_CUSTOMER_D_KEY)
INNER JOIN MDW.ACCOUNT_DIM BILL_TO_SIEBEL_ACCOUNT ON (BILL_TO_NATIVE_ACCOUNTS.SIEBEL_CUSTOMER_D_KEY=BILL_TO_SIEBEL_ACCOUNT.DW_CUSTOMER_D_KEY)
LEFT OUTER JOIN MDW.SAP_DUMMY_CODES ON (MDW.SAP_DUMMY_CODES.DUMMY_REVENUE_TYPE=MDW.EXIT_RATE_FACT.REVENUE_TYPE AND MDW.SAP_DUMMY_CODES.PRODUCT_D_KEY=MDW.EXIT_RATE_FACT.SAP_MATERIAL_D_KEY)
INNER JOIN MDW.COMPANY_CD_DIM ON (MDW.EXIT_RATE_FACT.DW_COMPANY_CODE_D_KEY=MDW.COMPANY_CD_DIM.COMP_CD_D_KEY)
INNER JOIN MDW.VERSION_DIM ON (MDW.EXIT_RATE_FACT.DW_VERSION_D_KEY=MDW.VERSION_DIM.VERSION_D_KEY)
INNER JOIN MDW.ASSET_ALLOC_GROUP_DIM ON (MDW.ASSET_ALLOC_GROUP_DIM.ASSET_ALLOC_GROUP_D_KEY=MDW.EXIT_RATE_FACT.ASSET_ALLOC_GROUP_D_KEY)
INNER JOIN MDW.ASSET_ALLOC_BRIDGE ON (MDW.ASSET_ALLOC_GROUP_DIM.ASSET_ALLOC_GROUP_D_KEY=MDW.ASSET_ALLOC_BRIDGE.ASSET_ALLOC_GROUP_D_KEY)
WHERE
( (SRC_PERIOD_MYR = 'EXIT_RATE_FACT_OA' ) OR (SRC_PERIOD_MYR != 'EXIT_RATE_FACT_OA' AND CASE WHEN MDW.SAP_DUMMY_CODES.DUMMY_REVENUE_TYPE = MDW.EXIT_RATE_FACT.REVENUE_TYPE THEN
MDW.SAP_DUMMY_CODES.REVENUE_TYPE_NAME ELSE PRODUCT_SAP.REVENUE_TYPE_NAME END IN ('Recurring Revenue','Recoveries','Recurring')
AND MDW.EXIT_RATE_FACT.REVENUE_TYPE IN
('Recurring Revenue','Recoveries','Recurring')) )
AND
(
UPPER(to_char(to_date(MDW.CALENDAR_DIM.PER_NAME_MONTH,'yyyy /MM'),'MON-YYYY')) IN ( 'AUG-2020' )
AND
MDW.VERSION_DIM.VERSION_NAME IN ( 'Actual Input' )
AND
CASE WHEN MDW.SAP_DUMMY_CODES.DUMMY_REVENUE_TYPE = MDW.EXIT_RATE_FACT.REVENUE_TYPE THEN MDW.SAP_DUMMY_CODES.PRODUCT_LEVEL_1_NAME ELSE PRODUCT_SAP.PRODUCT_LEVEL_1_NAME END NOT IN ( 'TradeWeb','Corporates' )
AND
CASE WHEN MDW.SAP_DUMMY_CODES.DUMMY_REVENUE_TYPE = MDW.EXIT_RATE_FACT.REVENUE_TYPE THEN MDW.SAP_DUMMY_CODES.PRODUCT_LEVEL_2_NAME ELSE PRODUCT_SAP.PRODUCT_LEVEL_2_NAME END NOT IN ( 'Webhosting - L2','Consumer Online - L2','Advisory Services - L2','Tradeweb - L2' )
AND
SIEBEL_ACCOUNTS.INTERNAL_ACCOUNT_FLAG IN ( 'N' )
AND
SIEBEL_ACCOUNTS.ACCOUNT_NAME NOT LIKE 'Refinitiv%'
)
GROUP BY
BILL_TO_SIEBEL_ACCOUNT.ACCOUNT_NUMBER,
COMPANY_CD_DIM.INTEGRATION_ID,
BILL_TO_SIEBEL_ACCOUNT.SUBSCRIBER_NUM,
SIEBEL_ACCOUNTS.ACCOUNT_NUMBER,
SIEBEL_ACCOUNTS.ACCOUNT_MANAGER,
SIEBEL_ACCOUNTS.ACCOUNT_NAME,
SIEBEL_ACCOUNTS.COUNTRY,
SIEBEL_ACCOUNTS.REGION,
SIEBEL_ACCOUNTS.FAMILY,
SIEBEL_ACCOUNTS.SIBL_ULTIMT_PARENT_NAME,
SIEBEL_ACCOUNTS.ACCOUNT_CLASS,
SIEBEL_ACCOUNTS.BUSINESS_CHANNEL,
SIEBEL_ACCOUNTS.X_REL_MODEL,
SIEBEL_ACCOUNTS.ULITMATE_PARENT_CATEGORY,
SIEBEL_ACCOUNTS.ORGANIZATION_TYPE,
EXIT_RATE_FACT.SOURCE_SYSTEM,
EXIT_RATE_FACT.SOURCE_COMPANY,
nvl(SIEBEL_ACCOUNTS.CUSTOMER_GROUP,'Unassigned'),
CALENDAR_DIM.PER_NAME_MONTH
HAVING
Sum(MDW.EXIT_RATE_FACT.EXIT_RATE*(MDW.ASSET_ALLOC_BRIDGE.ALLOCATION_PERCENT + MDW.ASSET_ALLOC_BRIDGE.CORRECTION_FACTOR) *CURRENT_PLAN_RATE.USD_EXCHANGE_RATE::DOUBLE) > 0
Post your database version (all 4 numbers please) and the execution plan:
https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/EXPLAIN-PLAN.html#GUID-FD540872-4ED3-4936-96A2-362539931BA0
explain plan for select ...the rest of your query;
To see the plan:
select * from table(dbms_xplan.display);
https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/EXPLAIN-PLAN.html#GUID-FD540872-4ED3-4936-96A2-362539931BA0
explain plan for select ...the rest of your query;
To see the plan:
select * from table(dbms_xplan.display);
ASKER
Thanks John,
Would like to clarify in more details what you are proposing. Could you please elaborate it
Would like to clarify in more details what you are proposing. Could you please elaborate it
Actually, we're not knowing anything about the underlying stuff (indexes, Oracle version, statistics, exec plans etc..), so there is nothing to propose right now! Even John has only given sort of general support! We need way more information!
btw, what is this -> ::
in
Never seen this in Oracle before!?
in
Sum(MDW.EXIT_RATE_FACT.EXIT_RATE*(MDW.ASSET_ALLOC_BRIDGE.ALLOCATION_PERCENT + MDW.ASSET_ALLOC_BRIDGE.CORRECTION_FACTOR) *CURRENT_PLAN_RATE.USD_EXCHANGE_RATE::DOUBLE) Exit_rate,
??Never seen this in Oracle before!?
First of all: without knowing the model, it's hard to tell what you can do..
In general: Check the existing indices. Maybe you need some additional ones.
In the concrete case: Using the account dimension seven times with the given JOIN conditions indicate a dimensional model issue.
In general: Check the existing indices. Maybe you need some additional ones.
In the concrete case: Using the account dimension seven times with the given JOIN conditions indicate a dimensional model issue.
>>Never seen this in Oracle before!?
Missed that. Looks like Postgres. I saw NVL and thought Oracle.
@Hemanth,
Please tell us your database product and version so we can get this in the correct Topic Area.
Missed that. Looks like Postgres. I saw NVL and thought Oracle.
@Hemanth,
Please tell us your database product and version so we can get this in the correct Topic Area.
Well as said without hard data and working on exactly the same setup is hard to judge
But generally in every case of an optimization issue i work in the concept of less is best
So you have a big table that you perform all kind of joins ...etc
We need to filter this table in order to return as less data as possible (based on your criteria) to have it present less to the other tables
Suppose this table has 1 million records but the records regarding August 2020 is just 10,000 ...just filter it and feed this to the rest of the table that you join
Of course each DB engine does quite some work on optimizing everything under the hood but it is always best to provide the less
test this and give us some feedback
But generally in every case of an optimization issue i work in the concept of less is best
So you have a big table that you perform all kind of joins ...etc
We need to filter this table in order to return as less data as possible (based on your criteria) to have it present less to the other tables
Suppose this table has 1 million records but the records regarding August 2020 is just 10,000 ...just filter it and feed this to the rest of the table that you join
Of course each DB engine does quite some work on optimizing everything under the hood but it is always best to provide the less
test this and give us some feedback
SELECT BILL_TO_SIEBEL_ACCOUNT.ACCOUNT_NUMBER AS Bill_To_Account_Number
,COMPANY_CD_DIM.INTEGRATION_ID
,BILL_TO_SIEBEL_ACCOUNT.SUBSCRIBER_NUM
,SIEBEL_ACCOUNTS.ACCOUNT_NUMBER
,SIEBEL_ACCOUNTS.ACCOUNT_MANAGER AS Location_Acct_Manager
,SIEBEL_ACCOUNTS.ACCOUNT_NAME
,SIEBEL_ACCOUNTS.COUNTRY
,SIEBEL_ACCOUNTS.REGION
,SIEBEL_ACCOUNTS.FAMILY
,SIEBEL_ACCOUNTS.ACCOUNT_CLASS
,SIEBEL_ACCOUNTS.BUSINESS_CHANNEL
,SIEBEL_ACCOUNTS.SIBL_ULTIMT_PARENT_NAME
,SIEBEL_ACCOUNTS.X_REL_MODEL AS Relationship_Model
,SIEBEL_ACCOUNTS.ULITMATE_PARENT_CATEGORY AS Ultimate_Parent_Category
,SIEBEL_ACCOUNTS.ORGANIZATION_TYPE
,Sum(MDW.EXIT_RATE_FACT.EXIT_RATE * (MDW.ASSET_ALLOC_BRIDGE.ALLOCATION_PERCENT + MDW.ASSET_ALLOC_BRIDGE.CORRECTION_FACTOR) * CURRENT_PLAN_RATE.USD_EXCHANGE_RATE::DOUBLE) Exit_rate
,EXIT_RATE_FACT.SOURCE_SYSTEM
,EXIT_RATE_FACT.SOURCE_COMPANY
,nvl(SIEBEL_ACCOUNTS.CUSTOMER_GROUP, 'Unassigned') AS Customer_Group
,CALENDAR_MDW.PER_NAME_MONTH AS Month_Year
FROM (SELECT CALENDAR_D_KEY,PER_NAME_MONTH FROM MDW.CALENDAR_DIM WHERE UPPER(to_char(to_date(PER_NAME_MONTH, 'yyyy /MM'), 'MON-YYYY')) IN ('AUG-2020')) AS CALENDAR_MDW
INNER JOIN MDW.EXIT_RATE_FACT ON (CALENDAR_MDW.CALENDAR_D_KEY = MDW.EXIT_RATE_FACT.PERIOD_D_KEY)
INNER JOIN MDW.PRODUCT_DIM PRODUCT ON (PRODUCT.PRODUCT_D_KEY = MDW.EXIT_RATE_FACT.SAP_MATERIAL_D_KEY)
INNER JOIN MDW.PRODUCT_DIM PRODUCT_SAP ON (PRODUCT_SAP.PRODUCT_D_KEY = PRODUCT.SAP_MATERIAL_D_KEY)
LEFT OUTER JOIN MDW.CURRENCY_HISTORY_DIM CURRENT_PLAN_RATE ON (
NVL(MDW.EXIT_RATE_FACT.CURRENCY_D_KEY, - 1) = CURRENT_PLAN_RATE.CURRENCY_D_KEY
AND CURRENT_PLAN_RATE.CURRENT_FLAG = 'Y'
)
INNER JOIN MDW.ACCOUNT_DIM SHIP_TO_ACCOUNTS ON (SHIP_TO_ACCOUNTS.DW_CUSTOMER_D_KEY = MDW.EXIT_RATE_FACT.SHIP_TO_CUSTOMER_D_KEY)
INNER JOIN MDW.ACCOUNT_DIM SHIP_TO_NATIVE_ACCOUNTS ON (SHIP_TO_NATIVE_ACCOUNTS.DW_CUSTOMER_D_KEY = SHIP_TO_ACCOUNTS.SIEBEL_CUSTOMER_D_KEY)
INNER JOIN MDW.ACCOUNT_DIM SIEBEL_ACCOUNTS ON (SIEBEL_ACCOUNTS.DW_CUSTOMER_D_KEY = SHIP_TO_NATIVE_ACCOUNTS.SIEBEL_CUSTOMER_D_KEY)
INNER JOIN MDW.ACCOUNT_DIM BILL_TO_ACCOUNTS ON (MDW.EXIT_RATE_FACT.BILL_TO_CUSTOMER_D_KEY = BILL_TO_ACCOUNTS.DW_CUSTOMER_D_KEY)
INNER JOIN MDW.ACCOUNT_DIM BILL_TO_NATIVE_ACCOUNTS ON (BILL_TO_ACCOUNTS.SIEBEL_CUSTOMER_D_KEY = BILL_TO_NATIVE_ACCOUNTS.DW_CUSTOMER_D_KEY)
INNER JOIN MDW.ACCOUNT_DIM BILL_TO_SIEBEL_ACCOUNT ON (BILL_TO_NATIVE_ACCOUNTS.SIEBEL_CUSTOMER_D_KEY = BILL_TO_SIEBEL_ACCOUNT.DW_CUSTOMER_D_KEY)
LEFT OUTER JOIN MDW.SAP_DUMMY_CODES ON (
MDW.SAP_DUMMY_CODES.DUMMY_REVENUE_TYPE = MDW.EXIT_RATE_FACT.REVENUE_TYPE
AND MDW.SAP_DUMMY_CODES.PRODUCT_D_KEY = MDW.EXIT_RATE_FACT.SAP_MATERIAL_D_KEY
)
INNER JOIN MDW.COMPANY_CD_DIM ON (MDW.EXIT_RATE_FACT.DW_COMPANY_CODE_D_KEY = MDW.COMPANY_CD_DIM.COMP_CD_D_KEY)
INNER JOIN MDW.VERSION_DIM ON (MDW.EXIT_RATE_FACT.DW_VERSION_D_KEY = MDW.VERSION_DIM.VERSION_D_KEY)
INNER JOIN MDW.ASSET_ALLOC_GROUP_DIM ON (MDW.ASSET_ALLOC_GROUP_DIM.ASSET_ALLOC_GROUP_D_KEY = MDW.EXIT_RATE_FACT.ASSET_ALLOC_GROUP_D_KEY)
INNER JOIN MDW.ASSET_ALLOC_BRIDGE ON (MDW.ASSET_ALLOC_GROUP_DIM.ASSET_ALLOC_GROUP_D_KEY = MDW.ASSET_ALLOC_BRIDGE.ASSET_ALLOC_GROUP_D_KEY)
WHERE (
(SRC_PERIOD_MYR = 'EXIT_RATE_FACT_OA')
OR (
SRC_PERIOD_MYR != 'EXIT_RATE_FACT_OA'
AND CASE
WHEN MDW.SAP_DUMMY_CODES.DUMMY_REVENUE_TYPE = MDW.EXIT_RATE_FACT.REVENUE_TYPE
THEN MDW.SAP_DUMMY_CODES.REVENUE_TYPE_NAME
ELSE PRODUCT_SAP.REVENUE_TYPE_NAME
END IN (
'Recurring Revenue'
,'Recoveries'
,'Recurring'
)
AND MDW.EXIT_RATE_FACT.REVENUE_TYPE IN (
'Recurring Revenue'
,'Recoveries'
,'Recurring'
)
)
)
AND (
MDW.VERSION_DIM.VERSION_NAME IN ('Actual Input')
AND CASE
WHEN MDW.SAP_DUMMY_CODES.DUMMY_REVENUE_TYPE = MDW.EXIT_RATE_FACT.REVENUE_TYPE
THEN MDW.SAP_DUMMY_CODES.PRODUCT_LEVEL_1_NAME
ELSE PRODUCT_SAP.PRODUCT_LEVEL_1_NAME
END NOT IN (
'TradeWeb'
,'Corporates'
)
AND CASE
WHEN MDW.SAP_DUMMY_CODES.DUMMY_REVENUE_TYPE = MDW.EXIT_RATE_FACT.REVENUE_TYPE
THEN MDW.SAP_DUMMY_CODES.PRODUCT_LEVEL_2_NAME
ELSE PRODUCT_SAP.PRODUCT_LEVEL_2_NAME
END NOT IN (
'Webhosting - L2'
,'Consumer Online - L2'
,'Advisory Services - L2'
,'Tradeweb - L2'
)
AND SIEBEL_ACCOUNTS.INTERNAL_ACCOUNT_FLAG IN ('N')
AND SIEBEL_ACCOUNTS.ACCOUNT_NAME NOT LIKE 'Refinitiv%'
)
GROUP BY BILL_TO_SIEBEL_ACCOUNT.ACCOUNT_NUMBER
,COMPANY_CD_DIM.INTEGRATION_ID
,BILL_TO_SIEBEL_ACCOUNT.SUBSCRIBER_NUM
,SIEBEL_ACCOUNTS.ACCOUNT_NUMBER
,SIEBEL_ACCOUNTS.ACCOUNT_MANAGER
,SIEBEL_ACCOUNTS.ACCOUNT_NAME
,SIEBEL_ACCOUNTS.COUNTRY
,SIEBEL_ACCOUNTS.REGION
,SIEBEL_ACCOUNTS.FAMILY
,SIEBEL_ACCOUNTS.SIBL_ULTIMT_PARENT_NAME
,SIEBEL_ACCOUNTS.ACCOUNT_CLASS
,SIEBEL_ACCOUNTS.BUSINESS_CHANNEL
,SIEBEL_ACCOUNTS.X_REL_MODEL
,SIEBEL_ACCOUNTS.ULITMATE_PARENT_CATEGORY
,SIEBEL_ACCOUNTS.ORGANIZATION_TYPE
,EXIT_RATE_FACT.SOURCE_SYSTEM
,EXIT_RATE_FACT.SOURCE_COMPANY
,nvl(SIEBEL_ACCOUNTS.CUSTOMER_GROUP, 'Unassigned')
,CALENDAR_DIM.PER_NAME_MONTH
HAVING Sum(MDW.EXIT_RATE_FACT.EXIT_RATE * (MDW.ASSET_ALLOC_BRIDGE.ALLOCATION_PERCENT + MDW.ASSET_ALLOC_BRIDGE.CORRECTION_FACTOR) * CURRENT_PLAN_RATE.USD_EXCHANGE_RATE::DOUBLE) > 0
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks John and Team. The comments are helpful and provides some guidance.
for example
Open in new window
and thenOpen in new window
its usually better to do itFrom
Open in new window
Open in new window
and use that as source for Subsquent joins in order to filter the MDW.CALENDAR_DIM right away and present only the useful values
Open in new window
p.s sorry for the bad formatting but the new coding formatter is killing me