SQL Server Performance

View Performance - view is using derived tables

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by sush_nmv@yahoo.com, Aug 20, 2008.

  1. sush_nmv@yahoo.com New Member

    I have the following view's select statement - I have indexes on all the columns used in the joins and wheres and still - "select *" on this view is taking 2 minutes for getting 3493 rows. I see an "INDEX SPOOL" in the query execution plan. I broke it down to multiple views and tried joining, but not much performance gain and looks like the data is already cached. I cannot clear the cache as I dont have test server. I am also including the plan text. Please provide any inputs for speeding this up. Thanks...here is the view's select statement.SELECT ev.ev_id AS [access_default_id],
    cl.cl_id AS [access_class_id],
    ins.in_id AS [access_instrument_id],
    ob.ob_moodysinternalid AS [moodys_org_id],
    ins.in_moodysdebtid AS [moodys_debt_id],
    ob.ob_name AS [name],
    ob.ob_siccode AS [sic_code],
    ob.ob_moodysindustrygrp AS [moodys_industry],
    ev.ev_obligordefaultdate AS [date_of_obligor_default],
    ev.ev_emergencedate AS [date_of_emergence],
    ev.ev_defaulttype AS [default_type],
    cl.cl_classtag AS [creditor_class_number],
    cl.cl_classname AS [creditor_class_description],
    ins.in_instrumenttype AS [instrument_type],
    ins.in_instrumentdesc AS [instrument_description],
    ins.in_collateralrank AS [instrument_ranking],
    ins.in_collateraltype AS [collateral],
    ins.in_maturitydate AS [maturity_date],
    ins.in_originationdate AS [origination_date],
    ins.in_originalamount AS [original_issuer_amount],
    (select case ins.in_discount_ind
    when 0 then (select case ins.in_principaldefaultamount when null then 0 else ins.in_principaldefaultamount end)
    else (select case ins.in_principaldefaultamount when 0 then null else ins.in_principaldefaultamount end)
    end) as [principal_amount_at_default],
    ins.in_accretedamount AS [accreted_amount],
    ev.ev_totaldebt AS [issuer_total_debt],
    (select case ins.in_debtabove when null then 0 else ins.in_debtabove end) as [principal_above_$],
    (select case ins.in_percentabove when null then 0 else ins.in_percentabove end) as [principal_above_%],
    (select case ins.in_debtbelow when null then 0 else ins.in_debtbelow end) as [principal_below_$],
    (select case ins.in_percentbelow when null then 0 else ins.in_percentbelow end) as [debt_cushion_%],
    ins.in_CUSIP AS [cusip_number],
    ins.in_defaultdate AS [date_of_instrument_default],
    ins.in_lastdatecashpaid AS [last_date_interest_paid],
    (select case ins.in_didnotdefault when 1 then 'Y' else 'N' end) as [never_defaulted],
    ins.in_interestbaserate AS [interest_rate_index],
    ins.in_valueoverbaserate/100 AS [spread_over_index],
    ins.in_effectiveinterestrate/100 AS [effective_interest_rate],
    cl.cl_settlementdesc AS [treatment],
    cl.cl_preferredmethod AS [recovery_method_recommended],
    (select case cl.cl_preferredmethod
    when 'Settlement' then ins.in_nominalsettlementtotal
    else (select case cl.cl_preferredmethod
    when 'Trading Price' then ins.in_nominaltradingprice
    else (select case cl.cl_preferredmethod when 'Liquidity' then ins.in_nominalliquiditytotal else Null end)
    end)
    end)/100 AS [recommended_nominal_recovery],
    (select case cl.cl_preferredmethod
    when 'Settlement' then ins.in_discountsettlementtotal
    else (select case cl.cl_preferredmethod
    when 'Trading Price' then ins.in_discounttradingprice
    else (select case cl.cl_preferredmethod when 'Liquidity' then ins.in_discountliquiditytotal else Null end)
    end)
    end)/100 AS [recommended_discounted_recovery],
    (select case cl.cl_useTradingPrice when 1 then ins.in_nominaltradingprice/100 else Null end) AS [trading_price_nominal_recovery],
    (select case cl.cl_useTradingPrice when 1 then in_discounttradingprice/100 else Null end) AS [trading_price_discounted_recovery],
    (select case cl.cl_useSettlement when 1 then ins.in_nominalsettlementtotal/100 else Null end) AS [settlement_nominal_recovery],
    (select case cl.cl_useSettlement when 1 then ins.in_discountsettlementtotal/100 else Null end) AS [settlement_discounted_recovery],
    (select case cl.cl_useLiquidity when 1 then ins.in_nominalliquiditytotal/100 else Null end) AS [liquidity_event_nominal_recovery],
    (select case cl.cl_useLiquidity when 1 then ins.in_discountliquiditytotal/100 else Null end) AS [liquidity_event_discounted_recovery],
    ev.ev_familyrecovery/100 AS [family_recovery],
    (select case ins.in_tradingprice30day when 0 then Null else ins.in_tradingprice30day end) AS [30_day_trading_price],
    (select case ins.in_tradingprice30day when 0 then Null else ins.in_tradingprice30daydate end) AS [30_day_trading_date],
    mp.aristeia_issuer_id AS [aristeia_issuer_id],
    sv.sv_serial_number AS [capiq_serial_number],
    P.product_id AS [product_id]
    , (select top 1 value from as13.hal_db_readonly.dbo.product_detail PE
    where PE.aristeia_field_code = 'PRIMARY_RISK_ID '
    and P.product_id = PE.product_id
    and PE.source_id = 0 and PE.expiration_date > ev.ev_obligordefaultdate
    order by PE.expiration_date) AS [primary_risk_id]
    FROM
    (
    (
    (
    as13.IM_DB.dbo.moodys_URD_obligor AS ob
    LEFT JOIN (
    as13.IM_DB.dbo.moodys_URD_event AS ev
    LEFT JOIN (
    as13.IM_DB.dbo.moodys_URD_class AS cl
    LEFT JOIN as13.IM_DB.dbo.moodys_URD_instrument AS ins
    ON cl.cl_id=ins.in_classid
    )
    ON ev.ev_id=cl.cl_eventid
    )
    ON ob.ob_id=ev.ev_obligorid
    )
    LEFT JOIN (
    as13.IM_DB.dbo.moodys_org_id_issuer_id_mapping as mp
    LEFT JOIN as13.IM_DB.dbo.stock_val_issuer_id_mapping as sv
    ON mp.aristeia_issuer_id = sv.aristeia_issuer_id
    and sv.expiration_date = '12/31/9999'
    )
    ON ob.ob_moodysinternalid = mp.moodys_org_id
    and mp.expiration_date = '12/31/9999'
    )
    LEFT JOIN as13.hal_db_readonly.dbo.product_detail P
    ON P.value = ins.in_CUSIP
    and P.aristeia_field_code in ('ID_CUSIP','LOAN_XID')
    and P.expiration_date = '12/31/9999'
    and P.source_id = 0
    )
    WHERE (ev.ev_posttoexcel= 1)
    Here is the text plan:
    StmtText
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    |--Compute Scalar(DEFINE:([Expr1038]=CASE WHEN [IM_DB].[dbo].[moodys_URD_class].[cl_preferredmethod] as [cl].[cl_preferredmethod]=N'Settlement' THEN [IM_DB].[dbo].[moodys_URD_instrument].[in_nominalsettlementtotal] as [ins].[in_nominalsettlementtotal] EL
    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([ev].[ev_obligordefaultdate], [P].[product_id]))
    |--Nested Loops(Inner Join, OUTER REFERENCES:([cl].[cl_preferredmethod]))
    | |--Nested Loops(Inner Join, OUTER REFERENCES:([cl].[cl_preferredmethod]))
    | | |--Nested Loops(Inner Join, OUTER REFERENCES:([ins].[in_discount_ind]))
    | | | |--Merge Join(Right Outer Join, MANY-TO-MANY MERGE:([Expr1082])=([ins].[in_CUSIP]), RESIDUAL:([Expr1082]=[IM_DB].[dbo].[moodys_URD_instrument].[in_CUSIP] as [ins].[in_CUSIP]))
    | | | | |--Sort(ORDER BY:([Expr1082] ASC))
    | | | | | |--Compute Scalar(DEFINE:([Expr1082]=CONVERT_IMPLICIT(nvarchar(840),[Hal_DB_readonly].[dbo].[product_detail].[value] as [P].[value],0)))
    | | | | | |--Filter(WHERE:([Hal_DB_readonly].[dbo].[product_detail].[source_id] as [P].[source_id]=(0)))
    | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([P].[product_detail_id]))
    | | | | | |--Index Seek(OBJECT:([Hal_DB_readonly].[dbo].[product_detail].[IX_product_detail_fieldcode_productid_expdate] AS [P]), SEEK:([P].[aristeia_field_code]='ID_CUSIP' OR [P].[aristeia_field_code]='LOAN_XID'),
    | | | | | |--Clustered Index Seek(OBJECT:([Hal_DB_readonly].[dbo].[product_detail].[PK_product_detail] AS [P]), SEEK:([P].[product_detail_id]=[Hal_DB_readonly].[dbo].[product_detail].[product_detail_id] as [P].[pro
    | | | | |--Sort(ORDER BY:([ins].[in_CUSIP] ASC))
    | | | | |--Hash Match(Right Outer Join, HASH:([mp].[moodys_org_id])=([ob].[ob_moodysinternalid]), RESIDUAL:([IM_DB].[dbo].[moodys_URD_obligor].[ob_moodysinternalid] as [ob].[ob_moodysinternalid]=[IM_DB].[dbo].[moodys_org_id_iss
    | | | | |--Merge Join(Left Outer Join, MERGE:([mp].[aristeia_issuer_id])=([as38].[fundamental_db].[dbo].[stock_val_issuer_id_mapping].[aristeia_issuer_id]), RESIDUAL:([IM_DB].[dbo].[moodys_org_id_issuer_id_mapping].[ariste
    | | | | | |--Index Scan(OBJECT:([IM_DB].[dbo].[moodys_org_id_issuer_id_mapping].[IX_mapping_unique] AS [mp]), WHERE:([IM_DB].[dbo].[moodys_org_id_issuer_id_mapping].[expiration_date] as [mp].[expiration_date]='9999-12-
    | | | | | |--Remote Query(SOURCE:(as38), QUERY:(SELECT "Tbl1016"."sv_serial_number" "Col1109","Tbl1016"."aristeia_issuer_id" "Col1110" FROM "fundamental_db"."dbo"."stock_val_issuer_id_mapping" "Tbl1016" WHERE "Tbl1016".
    | | | | |--Compute Scalar(DEFINE:([Expr1022]=CASE WHEN [IM_DB].[dbo].[moodys_URD_instrument].[in_discount_ind] as [ins].[in_discount_ind]=(0) THEN [IM_DB].[dbo].[moodys_URD_instrument].[in_principaldefaultamount] as [ins].
    | | | | |--Merge Join(Right Outer Join, MANY-TO-MANY MERGE:([cl].[cl_eventid])=([ev].[ev_id]), RESIDUAL:([IM_DB].[dbo].[moodys_URD_event].[ev_id] as [ev].[ev_id]=[IM_DB].[dbo].[moodys_URD_class].[cl_eventid] as [cl].[
    | | | | |--Sort(ORDER BY:([cl].[cl_eventid] ASC))
    | | | | | |--Merge Join(Right Outer Join, MANY-TO-MANY MERGE:([ins].[in_classid])=([cl].[cl_id]), RESIDUAL:([IM_DB].[dbo].[moodys_URD_class].[cl_id] as [cl].[cl_id]=[IM_DB].[dbo].[moodys_URD_instrument].[in_cl
    | | | | | |--Sort(ORDER BY:([ins].[in_classid] ASC))
    | | | | | | |--Table Scan(OBJECT:([IM_DB].[dbo].[moodys_URD_instrument] AS [ins]))
    | | | | | |--Clustered Index Scan(OBJECT:([IM_DB].[dbo].[moodys_URD_class].[cix_cl_id] AS [cl]), ORDERED FORWARD)
    | | | | |--Sort(ORDER BY:([ev].[ev_id] ASC))
    | | | | |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([ev].[ev_obligorid])=([ob].[ob_id]), RESIDUAL:([IM_DB].[dbo].[moodys_URD_obligor].[ob_id] as [ob].[ob_id]=[IM_DB].[dbo].[moodys_URD_event].[ev_obligorid]
    | | | | |--Sort(ORDER BY:([ev].[ev_obligorid] ASC))
    | | | | | |--Compute Scalar(DEFINE:([Expr1055]=[IM_DB].[dbo].[moodys_URD_event].[ev_familyrecovery] as [ev].[ev_familyrecovery]/(1.000000000000000e+002)))
    | | | | | |--Clustered Index Scan(OBJECT:([IM_DB].[dbo].[moodys_URD_event].[cix_ev_id] AS [ev]), WHERE:([IM_DB].[dbo].[moodys_URD_event].[ev_posttoexcel] as [ev].[ev_posttoexcel]=(1)))
    | | | | |--Clustered Index Scan(OBJECT:([IM_DB].[dbo].[moodys_URD_obligor].[cix_ob_id] AS [ob]), ORDERED FORWARD)
    | | | |--Nested Loops(Inner Join, PASSTHRU:([IM_DB].[dbo].[moodys_URD_instrument].[in_discount_ind] as [ins].[in_discount_ind]=(0)))
    | | | |--Nested Loops(Inner Join, PASSTHRU:(IsFalseOrNull [IM_DB].[dbo].[moodys_URD_instrument].[in_discount_ind] as [ins].[in_discount_ind]=(0)))
    | | | | |--Constant Scan
    | | | | |--Constant Scan
    | | | |--Constant Scan
    | | |--Nested Loops(Inner Join, PASSTHRU:([IM_DB].[dbo].[moodys_URD_class].[cl_preferredmethod] as [cl].[cl_preferredmethod]=N'Settlement'))
    | | |--Constant Scan
    | | |--Nested Loops(Inner Join, PASSTHRU:([IM_DB].[dbo].[moodys_URD_class].[cl_preferredmethod] as [cl].[cl_preferredmethod]=N'Trading Price'))
    | | |--Constant Scan
    | | |--Constant Scan
    | |--Nested Loops(Inner Join, PASSTHRU:([IM_DB].[dbo].[moodys_URD_class].[cl_preferredmethod] as [cl].[cl_preferredmethod]=N'Settlement'))
    | |--Constant Scan
    | |--Nested Loops(Inner Join, PASSTHRU:([IM_DB].[dbo].[moodys_URD_class].[cl_preferredmethod] as [cl].[cl_preferredmethod]=N'Trading Price'))
    | |--Constant Scan
    | |--Constant Scan
    |--Index Spool(SEEK:([ev].[ev_obligordefaultdate]=[IM_DB].[dbo].[moodys_URD_event].[ev_obligordefaultdate] as [ev].[ev_obligordefaultdate] AND [P].[product_id]=[Hal_DB_readonly].[dbo].[product_detail].[product_id] as [P].[product_id]))
    |--Sort(TOP 1, ORDER BY:([PE].[expiration_date] ASC))
    |--Nested Loops(Inner Join, OUTER REFERENCES:([PE].[product_detail_id]))
    |--Index Seek(OBJECT:([Hal_DB_readonly].[dbo].[product_detail].[IX_product_detail_unique] AS [PE]), SEEK:([PE].[product_id]=[Hal_DB_readonly].[dbo].[product_detail].[product_id] as [P].[product_id] AND [PE].[aristeia_field_code]=
    |--Clustered Index Seek(OBJECT:([Hal_DB_readonly].[dbo].[product_detail].[PK_product_detail] AS [PE]), SEEK:([PE].[product_detail_id]=[Hal_DB_readonly].[dbo].[product_detail].[product_detail_id] as [PE].[product_detail_id]) LOOKU
  2. HanShi New Member

    Try rewriting this
    into this:
    Code:
    CASE ins.in_discount_ind
    when 0 then
    COALESCE(ins.in_principaldefaultamount, 0)
    else
    CASE ins.in_principaldefaultamount
    when 0 then
    NULL
    else
    ins.in_principaldefaultamount
    end
    end
    AS [principal_amount_at_default]
    
    You don't need to use a sub-select to execute a CASE function. Rewrite all this sub-selects to a CASE like I did above.
    Sample:
    new code:
    case ins.in_didnotdefault when 1 then 'Y' else 'N' end as [never_defaulted],

    This way SQL will use one query to select all data instead of several small queries and putting the results together.
  3. HanShi New Member

    I've changed all the CASE in your SELECT. This is the new code:
    SELECT
    ev.ev_id AS [access_default_id],
    cl.cl_id AS [access_class_id],
    ins.in_id AS [access_instrument_id],
    ob.ob_moodysinternalid AS [moodys_org_id],
    ins.in_moodysdebtid AS [moodys_debt_id],
    ob.ob_name AS [name],
    ob.ob_siccode AS [sic_code],
    ob.ob_moodysindustrygrp AS [moodys_industry],
    ev.ev_obligordefaultdate AS [date_of_obligor_default],
    ev.ev_emergencedate AS [date_of_emergence],
    ev.ev_defaulttype AS [default_type],
    cl.cl_classtag AS [creditor_class_number],
    cl.cl_classname AS [creditor_class_description],
    ins.in_instrumenttype AS [instrument_type],
    ins.in_instrumentdesc AS [instrument_description],
    ins.in_collateralrank AS [instrument_ranking],
    ins.in_collateraltype AS [collateral],
    ins.in_maturitydate AS [maturity_date],
    ins.in_originationdate AS [origination_date],
    ins.in_originalamount AS [original_issuer_amount],
    case ins.in_discount_ind
    when 0 then
    COALESCE(ins.in_principaldefaultamount, 0)
    else
    case ins.in_principaldefaultamount
    when 0 then null else ins.in_principaldefaultamount end
    end as [principal_amount_at_default],
    ins.in_accretedamount AS [accreted_amount],
    ev.ev_totaldebt AS [issuer_total_debt],
    COALESCE(ins.in_debtabove, 0) as [principal_above_$],
    COALESCE(ins.in_percentabove, 0) as [principal_above_%],
    COALESCE(ins.in_debtbelow, 0) as [principal_below_$],
    COALESCE(ins.in_percentbelow, 0) as [debt_cushion_%],
    ins.in_CUSIP AS [cusip_number],
    ins.in_defaultdate AS [date_of_instrument_default],
    ins.in_lastdatecashpaid AS [last_date_interest_paid],
    case ins.in_didnotdefault when 1 then 'Y' else 'N' end as [never_defaulted],
    ins.in_interestbaserate AS [interest_rate_index],
    ins.in_valueoverbaserate/100 AS [spread_over_index],
    ins.in_effectiveinterestrate/100 AS [effective_interest_rate],
    cl.cl_settlementdesc AS [treatment],
    cl.cl_preferredmethod AS [recovery_method_recommended],
    case cl.cl_preferredmethod
    when 'Settlement'
    then ins.in_nominalsettlementtotal
    else
    case cl.cl_preferredmethod
    when 'Trading Price'
    then ins.in_nominaltradingprice
    else
    case cl.cl_preferredmethod
    when 'Liquidity'
    then ins.in_nominalliquiditytotal
    else Null
    end
    end
    end /100 AS [recommended_nominal_recovery],
    case cl.cl_preferredmethod
    when 'Settlement'
    then ins.in_discountsettlementtotal
    else
    case cl.cl_preferredmethod
    when 'Trading Price'
    then ins.in_discounttradingprice
    else
    case cl.cl_preferredmethod
    when 'Liquidity'
    then ins.in_discountliquiditytotal
    else Null
    end
    end
    end /100 AS [recommended_discounted_recovery],
    case cl.cl_useTradingPrice when 1 then ins.in_nominaltradingprice/100 else Null end AS [trading_price_nominal_recovery],
    case cl.cl_useTradingPrice when 1 then in_discounttradingprice/100 else Null end AS [trading_price_discounted_recovery],
    case cl.cl_useSettlement when 1 then ins.in_nominalsettlementtotal/100 else Null end AS [settlement_nominal_recovery],
    case cl.cl_useSettlement when 1 then ins.in_discountsettlementtotal/100 else Null end AS [settlement_discounted_recovery],
    case cl.cl_useLiquidity when 1 then ins.in_nominalliquiditytotal/100 else Null end AS [liquidity_event_nominal_recovery],
    case cl.cl_useLiquidity when 1 then ins.in_discountliquiditytotal/100 else Null end AS [liquidity_event_discounted_recovery],
    ev.ev_familyrecovery/100 AS [family_recovery],
    case ins.in_tradingprice30day when 0 then Null else ins.in_tradingprice30day end AS [30_day_trading_price],
    case ins.in_tradingprice30day when 0 then Null else ins.in_tradingprice30daydate end AS [30_day_trading_date],
    mp.aristeia_issuer_id AS [aristeia_issuer_id],
    sv.sv_serial_number AS [capiq_serial_number],
    P.product_id AS [product_id]
    , (select top 1
    value
    from as13.hal_db_readonly.dbo.product_detail PE
    where PE.aristeia_field_code = 'PRIMARY_RISK_ID '
    and P.product_id = PE.product_id
    and PE.source_id = 0
    and PE.expiration_date > ev.ev_obligordefaultdate
    order by PE.expiration_date
    ) AS [primary_risk_id]
    FROM .......
  4. moh_hassan20 New Member

    can you exclude (just for test) the inner select top 1 , and give feed back about performance
    can you post the expected no of rows for tables used in the query
    Can you avoid nested left join.

Share This Page