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