I have created views for my project. Now I want to optimize them for speed... How can I identify that the view can be optimize? Is index useful for this?
Let's say the following example...
SELECT dbo.vw_WebInventory.skref AS SaleID, dbo.vw_WebInventory.lot_number AS LotNumber, dbo.vw_WebInventory.Description,
dbo.vw_WebInventory.Image AS HasImage, dbo.vw_WebInventory.Sold, dbo.vw_WebInventory.Withdrawn, dbo.vw_WebTopBids.TopBid,
ISNULL(dbo.vw_WebInventory.Mins_Extend_y, 0) AS BidTimeExtend, dbo.Sale.SaleTypeID, dbo.Sale.ClosingDate, dbo.vw_WebInventory.ExDate,
dbo.vw_WebInventory.CurrDate, CASE WHEN vw_WebInventory.ExDate > ISNULL(vw_WebInventory.LotClosingDate, Sale.ClosingDate)
THEN 1 ELSE 0 END AS ShowBidMessage
FROM dbo.vw_WebInventory INNER JOIN
dbo.Sale ON dbo.vw_WebInventory.skref = dbo.Sale.SaleID LEFT OUTER JOIN
dbo.vw_WebTopBids ON dbo.vw_WebInventory.skref = dbo.vw_WebTopBids.CatNumber AND dbo.vw_WebInventory.lot_number = dbo.vw_WebTopBids.LotNumber
where vm_webTopBids and vm_WebInventory are two different views...
Is it possible to optimize this view?
Nesting views that call other views is an extremely bad technique for performance. Since it can't be indexed, it has to call the entire underlying view in order to get the one record the top would return. Plus eventually you get enough layers and you hit the limit of how many tables you can call in a view (And if view1 calls view2 and view3 and both call the same underlying tables you are joining to them twice instead of once which is often bad for performance. Stop calling views from views or you will very shortly have a an unuseable system.
We are completely redesigning a system like this because the application developers did this and the multi-million-dollar client is going to leave us unless performance improves and we can't improve it with this struture, so now a we face a complete redesign that the client will not be paying for because the error was ours. DO NOT go down this road. Stop now. Views that call views are very, very bad.