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 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?

Answer 1

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.

Answer 2

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 3

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