Using a view is degrading Select performance

Question

Using a view is degrading Select performance

Will Casey

Mr or Mrs. 500

Points: 570
More actions
December 11, 2009 at 4:03 am

#207347

Hi All,
I'm trying to troubleshoot a query in someone else's (honestly it is someone else's) database where the performance is unacceptable, when I use a view in the select statement it takes over 8 minutes to run. When I run the code in the view with the additional joins the select runs under 2 seconds
I can see from the Execution plans that when I use the view, 2 Hash Match (Right outer joins) are introduced. Are these the issue and if so how can I get rid of these.
All the join columns are included in indexes , is it a matter of updating the statistics?
I'm a developer here and I dont have a DBA unfortunately so any help would be appreciated (maybe its time to go on a course)
Let me know if anyone needs more detail.
Thanks
Will
(I have posted this query on the simple talk forums as well)
USING THE VIEW
SELECT * FROM vw_PositionPnl p
inner Join Securitydata sd on sd.Security = p.security
inner Join StrategyData sdy ON sdy.PM=p.PM and sdy.Strategy=p.Strategy
where TradeDate='12/9/09'
USING THE UNDERLYING VIEW CODE DIRECTLY PLUS THE 2 ADDITIONAL INNER JOINS
Select
p.Pos_Id,p.TradeDate,p.LoadDate,p.Fund,p.PM,p.AssetClass,p.Region,p.Strategy,p.InstClass,p.InstType,p.FXPair,p.Maturity,
IsNull(cs.Security, p.Security) Security,
p.BBTicker,p.Ccy,p.Posn,p.PnL_Tdy,p.PnL_Mtd,p.PnL_Ytd,p.ContractSize,p.SecExpiry,p.SecLastTradeDate,p.CallPut,p.ExrPrice,p.Price,p.ContractId,
p.ContractMaturity,p.Coupon,p.CommodityDesc,p.CptyRefId,p.ISIN,p.FXRate,p.PnL_Itd,p.MktValue,p.EntryPrice,p.CptyShrt,p.CntContId,p.CntCptyRefId,
p.CntPayRcvInd,p.CntRcvCcy,p.CntRcvNotnl,p.CntRcvRate,p.CntRcvFreq,p.CntPayCcy,p.CntPayNotnl,p.CntPayRate,p.CntPayFreq,p.CntStartDate,p.CntrctMatDate,
p.CntrctCptyDesc,p.CntLastReset,p.CntPayCoupDate,p.CntRcvCoupDate,p.ThemeName,p.Adjustment,p.ThemeId3,p.ThemeId4,p.ThemeId5,p.ThemeId6,p.ThemeId7,
p.ThemeId8,p.ThemeId9,p.ThemeId10,p.BasisPt,
Case when Ccy in ('NZD', 'EUR', 'GBP', 'AUD') then FXRate else 1/FXRate end NormalizedFXRate,
pt.Name ProductType, pt.IsLinear, Case when pt.Name='FX Forward' then FXPair else IsNull(cs.Security, p.Security) end RiskSecurity,
Case when p.Strategy='EUR_SHARE_HEDGE' or p.AssetClass = 'Treasury' or p.PM not in ('WD', 'SW', 'CG', 'MM') then 0 else 1 end IncludePnl,
Case when p.AssetClass='CC' then 'CC' else p.PM end ActualPM
From RiskpnlDW..PositionPnl p
Left Join InstClass c on c.InstClass=p.InstClass and p.InstType=c.InstType
Left Join ProductType pt on pt.ProductTypeId=c.ProductTypeId
Left Join ContractSecurity cs on cs.ContractId = p.ContractId
inner Join Securitydata sd on sd.Security = p.security
inner Join StrategyData sdy ON sdy.PM=p.PM and sdy.Strategy=p.Strategy
where TradeDate='12/9/09'

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

joeroshan SSChampion Points: 10377 More actions · Answer 1

can you post the execution plan?

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Will Casey Mr or Mrs. 500 Points: 570 More actions · Answer 2

Will Casey

Mr or Mrs. 500

Points: 570

December 11, 2009 at 6:50 am

#1091133

Is XML format OK Joe?

Will Casey Mr or Mrs. 500 Points: 570 More actions · Answer 3

Joe please find the good and bad sql plans attached just delete the .txt which I added to ensure they'd upload.

Thanks

Will

Will Casey Mr or Mrs. 500 Points: 570 More actions · Answer 4

Will Casey

Mr or Mrs. 500

Points: 570

December 11, 2009 at 7:05 am

#1091151

Sql plans now attached in post above.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Since this is SQL Server 2005, you can save the Actual Execution plan as .sqlplan file and upload that.

Will Casey Mr or Mrs. 500 Points: 570 More actions · Answer 6

Thanks Lynn, I've posted them above and got rid of the xml

W

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 7

Try updating the statistics , on particularly ContractSecurity and SecurityData. The estimates are well out from the actual row counts.

Clear Sky SQL
My Blog[/url]

Will Casey Mr or Mrs. 500 Points: 570 More actions · Answer 8

Thanks Dave I've set the stats to update on the entire database tonight and I'll let you know how I get on on Monday.

W

Will Casey Mr or Mrs. 500 Points: 570 More actions · Answer 9

Morning All,

So Dave's rebuild the statistics suggestion got the code to run in 20 seconds approx (massive gain there) so thanks very much Dave hats off to you, I'd better check whats actually happening with the nightly maintenance plans.

The view is still 10 times slower than using the underlying code.

We have a brand new execution plan (for those interested) where the major cost now is Parallelism(Gather Streams) I added a OPTION (MAXDOP 1) hint to try and get around this but it made things worse it took 90 seconds.

Will

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 10

Glad its moving better,

Looking at you new plan , the issue is now with the amount of rows returned by the securitydata table.

I think this is because its being join to on the security column of the view which is 'IsNull(cs.Security, p.Security) Security' , Try returning both the cs.Security and p.Security in the view and then outer joining twice to the security table and isnull the resulting columns from that.

In your 'fast' version the join is 'inner Join Securitydata sd on sd.Security = p.security'

hope this is clear

Clear Sky SQL
My Blog[/url]

Will Casey Mr or Mrs. 500 Points: 570 More actions · Answer 11

Dave , run time is 1 second, you I believe, are the man.

Thanks for all the help and Merry Christmas

W

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 12

Dave Ballantyne

SSC-Dedicated

Points: 33667

December 14, 2009 at 5:46 am

#1091879

No worries , glad to help

Clear Sky SQL
My Blog[/url]