Take the 2-minute tour ×

i have created views for my project now i want to optimize them for the speed purpose...how can i identify that the view can be optimize? is index usefull 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 view... is it possible to optimize this view?

share|improve this question
add comment

3 Answers

up vote 4 down vote accepted

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.

share|improve this answer
Thanks for the help... –  girish Apr 30 '10 at 6:55
add comment

A view is a macro that is expanded into the outer query. Unless it's an indexed view and you have enterprise edition, it's simply ignored.

So if you join 3 views and each views uses 5 tables, you have big join with 15 tables.

You best bet is the Database Tuning Advisor or a missing index script:

    CONVERT(decimal(28, 1), migs.avg_total_user_cost * migs.avg_user_impact *
    (migs.user_seeks + migs.user_scans)) AS improvement_measure,
    'CREATE INDEX missing_index_' + CONVERT(varchar, mig.index_group_handle) +
    '_' + CONVERT(varchar, mid.index_handle) + ' ON ' + mid.statement + ' (' +
    ISNULL(mid.equality_columns, '') +
    CASE WHEN mid.equality_columns IS NOT NULL AND
              mid.inequality_columns IS NOT NULL THEN ','
         ELSE ''
    END + ISNULL(mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' +
                                                            mid.included_columns +
                                                            ')', '') AS create_index_statement,
    sys.dm_db_missing_index_groups mig INNER JOIN 
    sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle INNER JOIN
    sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
    (migs.user_seeks + migs.user_scans)) > 10 AND
    database_id = DB_ID()
    migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks +
                                                       migs.user_scans) DESC

Edit, after example

You are nesting views on top of views. No optimisations are possible of the view itself.

As mentioned, this can't be indexed

share|improve this answer
adding index to view is usefull to optimize the speed? –  girish Apr 29 '10 at 7:48
@girish: it's hard to say without more information. Personally, I'd use the DTA or the script above before I consider indexed views –  gbn Apr 29 '10 at 7:53
i have mention example above... –  girish Apr 29 '10 at 9:03
Indexed views can be used in SQL Server editions other than Enterprise but you must provide the NOEXPAND hint in any query that references the view otherwise the index will be ignored. –  Daniel Renshaw Apr 29 '10 at 9:45
add comment

In this case, the view can't be indexed because it contains an OUTER JOIN.

See this article for information on indexing views and the (many) restrictions on them: http://technet.microsoft.com/en-us/library/cc917715.aspx

share|improve this answer
is this mean, above view is perfact as it is... it can't be optimize anymore –  girish Apr 29 '10 at 9:54
Depends on too much to say. As suggested elsewhere, consider optimizing the underlying tables first. If the access pattern needs to use the view extensively such that improving this view will gve the biggest benefits, see if you can alter it so that it meets the indexed view constraints and then add appropriate indexes. –  Daniel Renshaw Apr 29 '10 at 10:10
add comment

Your Answer


