A View to a Kill



  • Just got through a week of load testing.  Database servers pegging at 100% CPU utilization.  Managed to reduce it to 30%.  Did not manage to retain sanity.

     Imagine a view so horrible that, from a performance standpoint, the following is necessary to select from it efficiently:

     

    SELECT ProductView.* FROM (
        SELECT TOP (@PageSize) ProductID, Rank FROM (
            SELECT ProductID, ROW_NUMBER() OVER ( ORDER BY SortNumber, ProductID ) AS Rank FROM Product
            WHERE Enabled = 1 AND Quantity > 0
        ) AS Results
        WHERE Rank > ( @PageIndex * @PageSize )
        ORDER BY Rank
    ) AS Results
    INNER JOIN ProductView ON ProductView.ProductId = Results.ProductId
    ORDER BY Rank

     

    The view had 13 joins.  One join in particular was against a view aggregating the results of a sub query depending on values from another view which in turn got its results from aggregating additional data.

    Or, quite aptly,

     One View to rule them all, One View to find them, One View to bring them all, and in the darkness bind them.

     

     



  • Nothing wrong with 13 joins.  But joining on a view is generally a bad idea, as you see.  The database usually has to instantiate the entire view-- which will not be indexed-- before it can do the join.  Views should be used for publishing data outside a database (viewing it).  Inside the database where you know how the view was created and how the view was really structured you can access the needed tables directly.



  • @jcoehoorn said:

    Nothing wrong with 13 joins.  But joining on a view is generally a bad idea, as you see.  The database usually has to instantiate the entire view-- which will not be indexed-- before it can do the join.  Views should be used for publishing data outside a database (viewing it).  Inside the database where you know how the view was created and how the view was really structured you can access the needed tables directly.

     

    Agreed. Ditch the view, no reason to use it. Make sure the indexes are decent on all tables you're accessing, and stick the lot into an SP so the execution plan is predetermined.



  • I had a developer who created a view joininng tables A, B, C, & D, and then wrote a query joining this view to tables A & B.

     If two different people had conspired to do that, I would have just been pissed at them for not communicating to each other or documenting properly...but for the same person to do both of those things is truly sick...


Log in to reply