View Performance - view is using derived tables

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(SOURCEas38), QUERYSELECT "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, PASSTHRUIsFalseOrNull [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

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.

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

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.