SQL Server Performance

View Performance - View is using derived tables

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' 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. satya Moderator

    Welcome to the forums.
    How often you are reindexing the relevant indexes?

Share This Page