how can i optimize views in sql server for speed

Question

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?

Answer 1

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:

SELECT
    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,
    migs.*,
    mid.database_id,
    mid.[object_id],
    mig.index_group_handle,
    mid.index_handle
FROM
    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
WHERE
    CONVERT(decimal(28, 1), migs.avg_total_user_cost * migs.avg_user_impact *
    (migs.user_seeks + migs.user_scans)) > 10 AND
    database_id = DB_ID()
ORDER BY
    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

Answer 2

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

Answer 3

Nesting views that call other views is an extremely bad technique for performance. Since it can't be indexed, it has to call the entier 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 tabless you can call in a view (And if view 1 calls view2 and view 3 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 shorty have a an unuseable system.

We are completely redeigning a system like this because the application devliopers did this and the multi-million-dollar client is going to leave us unless performance improves and we can't imporve it with this struture, so now a we face a complete redisgn 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 verym very bad.