Link to home
Start Free TrialLog in
Avatar of Hemanth Kumar
Hemanth KumarFlag for India

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


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

Open in new window

Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

Well without data is mainly guessing but the more filtering you perform on base level is better
for example
FROM MDW.CALENDAR_DIM

Open in new window

and then
UPPER(to_char(to_date(MDW.CALENDAR_DIM.PER_NAME_MONTH, 'yyyy /MM'), 'MON-YYYY')) IN ('AUG-2020')

Open in new window

its usually better to do it
 From
FROM (SELECT [Whatever fields you need] FROM MDW.CALENDAR_DIM WHERE 

Open in new window

UPPER(to_char(to_date(MDW.CALENDAR_DIM.PER_NAME_MONTH, 'yyyy /MM'), 'MON-YYYY')) IN ('AUG-2020')) AS CALENDAR_MDW 

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
e.g
INNER JOIN MDW.EXIT_RATE_FACT ON (CALENDAR_MDW.CALENDAR_D_KEY = MDW.EXIT_RATE_FACT.PERIOD_D_KEY)

Open in new window

p.s sorry for the bad formatting but the new coding formatter is killing me
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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);
Avatar of Hemanth Kumar

ASKER

Thanks John,
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
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,

Open in new window

??

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.
>>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.
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
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

Open in new window




ASKER CERTIFIED SOLUTION
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks John and Team. The comments are helpful and provides some guidance.