Optimization of large SQL query with lots of joins

Question

I'm looking for a way to optimize this Microsoft SQL query.

Specifically I would like to get an answer to the following questions:

1. In what way can I parallelize the below query
2. What can be optimize in the query so it runs quicker? (I'm not expecting anybody to do the job for me, but to put me in the right direction). For example, how can the many joins be done more efficiently?
3. Is there generally a better way to structure such a large SQL query?

Any suggestions are appreciated

    --IF (OBJECT_ID('SATURN_REPORTING.RISKDATA')) IS NOT NULL
    --     DROP TABLE SATURN_REPORTING.RISKDATA    
    --GO

    DECLARE @COB_DATE VARCHAR(10);
    SET @COB_DATE = '2017-06-30';

    SELECT  
            R.COB_DATE,

            FD.ASSET_CLASS,
            FD.SOURCE_SYSTEM,         
            CASE 
                         WHEN TM1.ANALYSIS_TENOR IS NOT NULL AND TM2.ANALYSIS_TENOR IS NOT NULL THEN (TM1.MULTIPLIER * TM2.MULTIPLIER * R.VALUE)
                            ,,,,
                         WHEN TM1.ANALYSIS_TENOR IS NULL AND TM2.ANALYSIS_TENOR IS NOT NULL THEN (TM2.MULTIPLIER * R.VALUE)
                  ELSE R.VALUE END AS CCY_VALUE,
            CASE 
                         WHEN TM1.ANALYSIS_TENOR IS NOT NULL AND TM2.ANALYSIS_TENOR IS NOT NULL THEN (TM1.MULTIPLIER * TM2.MULTIPLIER * R.VALUE * X.GBP_RATE)
                          ...
                         WHEN TM1.ANALYSIS_TENOR IS NULL AND TM2.ANALYSIS_TENOR IS NOT NULL THEN (TM2.MULTIPLIER * R.VALUE * X.GBP_RATE)
                  ELSE (R.VALUE * X.GBP_RATE) END AS GBP_VALUE,
            R.UNIT AS R_UNIT,
            RFC1.RISK_FACTOR_TYPE RFC_RISK_FACTOR_TYPE, 
          ...
            P.TRADE_VERSION AS P_TRADE_VERSION, 
            TR.COUNTER_PARTY_NAME AS TR_COUNTER_PARTY_NAME,
            TR.LOCATION AS TR_LOCATION,
            TR.STRIKE AS TR_STRIKE,
            P.CUST_ID AS P_CUST_ID, 
            P.SENIORITY AS P_SENIORITY,
            P.ISSUER_OPERATION_CTRY_NAME AS P_ISSUER_OPERATION_CTRY_NAME, 
        ...
            P.MATURITY_DATE AS P_MATURITY_DATE, 
            P.NDF AS P_NDF, 
            P.OFFSHORE AS P_OFFSHORE, 
            P.COLLATERAL_COUPON AS P_COLLATERAL_COUPON, 
            P.IN_DEFAULT AS P_IN_DEFAULT,
            ISS.ISSUER_NAME AS ISS_ISSUER_NAME, 
       ...
    ...
            INS.TRANCHE_NAME AS INS_TRANCHE_NAME                       

    INTO    SATURN_REPORTING.RISKDATA1

    FROM    SATURN_REPORTING.RISK R
    INNER JOIN SATURN_REPORTING.RISK_TYPE RT 
        ON RT.RISK_TYPE = R.RISK_TYPE
    INNER JOIN SATURN_REPORTING.FD ON FD.FEED_CODE = R.FEED_CODE
    INNER JOIN SATURN_REPORTING.DIM_TRANSFORM_TYPE TT 
        ON TT.TRANSFORM_KEY = R.TRANSFORM_TYPE                    
    INNER JOIN SATURN_REPORTING.X_RATE X 
        ON X.CURRENCY = R.UNIT
        AND X.COB_DATE = R.COB_DATE
    LEFT JOIN SATURN_REPORTING.RISK_FACTOR_CURVE RFC1 
        ON RFC1.COB_DATE = R.COB_DATE
        AND RFC1.ID = R.DIM_1_CURVE_ID
    LEFT JOIN SATURN_REPORTING.RISK_FACTOR_POINT RFP1
           ON RFP1.ID = R.DIM_1_POINT_ID
           AND RFP1.COB_DATE = @COB_DATE
    LEFT JOIN SATURN_REPORTING.TENOR TMAT
            ON RFP1.COB_DATE = TMAT.COB_DATE
            AND RFP1.MATURITY_TENOR = TMAT.TENOR
            AND TMAT.EXPIRED = '9999-12-31 12:00:00 AM'    
    LEFT JOIN SBA.TENORMAPPING TM1
           ON TM1.SIMPLE_TENOR = TMAT.SIMPLE_TENOR
    LEFT JOIN SATURN_REPORTING.TENOR TEXP
            ON RFP1.COB_DATE = TEXP.COB_DATE
            AND RFP1.EXPIRY_TENOR = TEXP.TENOR
            AND TEXP.EXPIRED = '9999-12-31 12:00:00 AM'    
    LEFT JOIN SBA.TENORMAPPING TM2
           ON TM2.SIMPLE_TENOR = TEXP.SIMPLE_TENOR
    LEFT JOIN SATURN_REPORTING.POSITION P 
        ON P.ID = R.POSITION_ID
    LEFT JOIN SATURN_REPORTING.TRADE TR
      ON TR.TRADE_ID COLLATE SQL_LATIN1_GENERAL_CP1_CS_AS = P.TRADE_ID COLLATE SQL_LATIN1_GENERAL_CP1_CS_AS
      AND TR.TRADE_VERSION = P.TRADE_VERSION
      AND TR.TRADE_ID_TYPE COLLATE SQL_LATIN1_GENERAL_CP1_CS_AS = P.TRADE_ID COLLATE SQL_LATIN1_GENERAL_CP1_CS_AS
    LEFT JOIN SATURN_REPORTING.ISSUER ISS 
        ON ISS.ISSUER_ID = P.ISSUER_ID
        AND ISS.ISSUER_ID_TYPE COLLATE SQL_LATIN1_GENERAL_CP1_CS_AS = P.ISSUER_ID_TYPE COLLATE SQL_LATIN1_GENERAL_CP1_CS_AS
        AND ISS.ISSUER_VERSION = P.ISSUER_VERSION
    LEFT JOIN SATURN_REPORTING.INSTRUMENT INS 
        ON INS.INSTRUMENT_ID COLLATE SQL_LATIN1_GENERAL_CP1_CS_AS = P.INSTRUMENT_ID COLLATE SQL_LATIN1_GENERAL_CP1_CS_AS
        AND INS.INSTRUMENT_ID_TYPE COLLATE SQL_LATIN1_GENERAL_CP1_CS_AS = P.INSTRUMENT_ID_TYPE COLLATE SQL_LATIN1_GENERAL_CP1_CS_AS
        AND INS.INSTRUMENT_VERSION = P.INSTRUMENT_VERSION
    LEFT JOIN SATURN_REPORTING.ASSET_HIERARCHY_MAPPING AHM
        ON AHM.ASSET COLLATE SQL_LATIN1_GENERAL_CP1_CS_AS = RFC1.INSTRUMENT_LABEL COLLATE SQL_LATIN1_GENERAL_CP1_CS_AS
        AND R.COB_DATE BETWEEN AHM.FROM_COB_DATE AND AHM.TO_COB_DATE
    AND    AHM.EXPIRED = 'Dec 31 9999 12:00AM'
    WHERE   R.COB_DATE = @COB_DATE
    AND     R.EXPIRED = '9999-12-31'
    AND     TT.TRANSFORM = 'FINAL'
    AND     X.EXPIRED = '9999-12-31 00:00:00'
    AND R.RISK_TYPE IN (
           'CONUDL',
      ...
           'IRIN',
           'IRINT',
      ...
           'SARO',
       ...
    )
    GO        

    /****** Object:  Index [RISKDATA_IDX_001]    Script Date: 17/02/2015 14:29:18 ******/
    CREATE NONCLUSTERED INDEX [RISKDATA_IDX_001] ON [SATURN_REPORTING].[RISKDATA1]
    (
           [RISK_BOOK] ASC,
           [FEED_CODE] ASC,
           [RISK_TYPE] ASC,
           [MAT_BUCKET] ASC,
           [EXP_BUCKET] ASC,
           [R_UNIT] ASC,
           [RFC_SOURCE_PRICING_CURVE] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO

Answer 1

The magic word is INDEX, as long the join conditions can use an index you will be OK.

Also try to setup your table fields to the right collation from the beginning. There shouldn't be need to indicate collation during the JOIN

This condition AND TEXP.EXPIRED = '9999-12-31 12:00:00 AM' if you have a magic value for infinity is better use NULL

Also following Guillaume suggestion use Query Analyzer. But don't start with the whole query.

How you eat an elephant? one small piece each time.

Start with two table, test it, optimize, add another table and repeat

Answer 2

It is hard to say without putting a lot of work into analyzing your query and without having more information about your database. We have no clue as to the loads on this query from the different tables. For example, it would be an entirely different ballgame if you have one table with 100M records, and 12 tables with a handful of records each, vs. 13 tables with 7.5M records each.

However, one possible optimization that I see would be that I notice you are doing different calculations for your results depending on whether tm1.analysis_tenor or tm2.analysis_tenor are null. If you break up the query into multiple queries where each one does one of those cases separately, you would eliminate the need for the decision making (case expression) on each result, and on three of the resulting queries you would eliminate one or more joins.

I say "possible" because it may not end up being faster. Like @GuillaumeCR said, the optimizer does a very good job, and you may find that this change adds more overhead than it takes away.

Answer 3

We would need to see your database' structure to give you insight. SQL is fantastically good at parallelizing what it can, so I would not worry about trying to do it manually: you are much more likely to get in the way of the optimizer than to help it.

This article, although old, still has some good tips on how to use the query plan visualizer to help understand the bottlenecks of your query. Do not start optimizing until you understand exactly which part SQL is having trouble with.

Give us the result of the query plan visualizer to help us help you.