Posts
10
Comments
14
Trackbacks
0
Why Views are evil.

Synopsis: Views are evil, bad, buggy, temperamental, tortuous, and should be avoided.

Years ago, when I started out as a wee developer graduating from Microsoft Access, I tried and did port my favorite functionality of Access to Sql Server 2000.  The idea was simple, take some complicated table joins and hide all of those complicated relationships.  In MS Access, that's called a Query.  In Sql Server, it is called a View. 

In my enthusiasm, I told the report developer, "No more duplicating the same joins across all those stupid stored procedures!  Do it the right way, centralize that code up into a view.  See, how easy this is?"

I was sooooooo smart, everyone admitted it, except the CIO at raise time ;).  Um, until about a year later, when that same report writer came to me saying that the reports with views weren't running so well.  The why, I still don't know.  I honestly didn't have time to find out.  Though to be honest with myself and you, I had noticed an trend with the views which was already making me queasy.    As time progressed, tables were added.  Lots of tables.  And since, surprise, surprise, the database was reasonably well de-normalized, and we were just making complicated things simple, we added LOTS of tables to those darned views. 

So, here is why I say views are evil:

  1. I've historically been burned by them.  Is that not reason enough?
  2. sp_refreshview  --- http://technet.microsoft.com/en-us/library/ms187821.aspx, google terms: SQL2005 BOL sp_RefreshView.  Can I have an Amen to anyone who learned this the hard way?
  3. Views hide complexity by hiding joins.  It is as simple as that.  Joins are a major cause of performance problems.  That hidden simplicity encourages poor T-SQL programming practices.  What I frequently have seen is select X_column1 from viewXY group by X_column1.  Looks fine to me, to you, to everyone.  Wrong!  The view says: Select X_column1....Y_column50 from tableX X join tableY Y on X.xID = Y.XID.  A simple select from the tableX which has a unique index on column1 would have done the trick and done the trick efficiently!
  4. One must work especially hard to make a view updatable/insertable. 

What about indexed views you ask?  Well, I have an answer!

http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx  .  Follow along with me under:

Benefits of Using Indexed Views

"Analyze your database workload before implementing indexed views." 

Hmmmm.  A little further down. 

"Applications that benefit from the implementation of indexed views include:

• Decision support workloads

• Data marts

• Data warehouses

• Online analytical processing (OLAP) stores and sources

• Data mining workloads"

Whew, finally!

"On the contrary, online transaction processing (OLTP) systems with many writes, or database applications with frequent updates, may not be able to take advantage of indexed views because of the increased maintenance cost associated with updating both the view and underlying base tables."

oh, oh, here comes my point:

"Identifying an appropriate set of indexes for a database system can be complex."

So, my answer(s).  A) Views and indexed views are not the same thing.  B) Indexed views may help, but ONLY if you are willing to spend quite a bit of time helping them help you.  In my experience, sadly in most development shops, time isn't given for such labor to occur. C) I work primarily on OLTP systems.

=====================

More reading on views:

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx  Not all editions of support Indexed views the same. 

http://sql-server-performance.com/Community/forums/p/7447/43319.aspx  Adrian's explanation was wonderful.

http://www.sql-server-performance.com/article_print.aspx?id=154&type=tip Tips on creating effective indexed views.

http://www.sql-server-performance.com/tips/views_general_p1.aspx Oh look, someone already beat me to it.  There is nothing new under the sun.  I read this AFTER I wrote most of this blog.  AFTER.

http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1170220_tax301334,00.html?adg=301324&bucket=ETA#views  Nuff said.

=====================

Conclusion:

Honestly, after writing this post, I'm starting to think maybe I should reevaluate just where I could use indexed views.  Since I live an ignorant life,  I had written them off without investigating them more thoroughly.  I'm thinking, investigate, recommend, implement, get noticed by the CIO, get a raise.....


=====================
PS
2008-02-20

=====================

Scripts:
I was asked to show what was said in above post was accurate in SQL 2005.  Feel free to copy, paste, read, and then run the code below.

/**==============================================================================================================================================================================================
--#region Setup Test Schema**/


USE master

go

IF NOT EXISTS ( SELECT TOP 1
                    1
                FROM
                    sys.databases
                WHERE
                    name = 'MaasSqlTest' ) 
    BEGIN
        CREATE DATABASE MaasSqlTest
    END

GO

USE MaasSqlTest

GO

IF ( OBJECT_ID('dbo.ViewTest_Detail') IS NOT NULL ) 
    BEGIN
        DROP TABLE dbo.ViewTest_Detail
    END
GO

IF ( OBJECT_ID('dbo.ViewTest_Group') IS NOT NULL ) 
    BEGIN
        DROP TABLE dbo.ViewTest_Group
    END
GO

CREATE TABLE dbo.ViewTest_Group
    (
      ViewTest_Group_ID INT IDENTITY(1 , 1)
                            PRIMARY KEY CLUSTERED
    , name NVARCHAR(50) UNIQUE
    )

GO

CREATE TABLE dbo.ViewTest_Detail
    (
      ID INTEGER IDENTITY(1 , 1)
                 PRIMARY KEY CLUSTERED
    , ViewTest_Group_ID INTEGER
    , DateEntered DATETIME DEFAULT ( GETUTCDATE() )
    , name NVARCHAR(50) 
      FOREIGN KEY ( ViewTest_Group_ID ) REFERENCES dbo.ViewTest_Group ( ViewTest_Group_ID )
    )

GO

IF ( OBJECT_ID('dbo.ViewTest_GroupDetail') IS NOT NULL ) 
    BEGIN
        DROP VIEW dbo.ViewTest_GroupDetail
    END

GO

CREATE VIEW dbo.ViewTest_GroupDetail
AS  SELECT
        G.ViewTest_Group_ID
    ,   G.name GroupName
    ,   D.name DetailName
    ,   D.DateEntered DetailTime
    FROM
        MaasSqlTest.dbo.ViewTest_Group G
        JOIN MaasSqlTest.dbo.ViewTest_Detail D
            ON G.ViewTest_Group_ID = D.ViewTest_Group_ID

GO

/**
--#endregion Setup Test Schema
==============================================================================================================================================================================================**/

/**==============================================================================================================================================================================================
--#region Populate our new tables**/

GO
SET NOCOUNT ON ;

DECLARE @cntr INTEGER ;
SET @cntr = 1 ;

WHILE @cntr <= 10
    BEGIN
        INSERT INTO
            dbo.ViewTest_Group ( name )
        VALUES
            (
              'GroupID = ' + CAST(@cntr AS NVARCHAR(50))
        )
        SET @cntr = @cntr + 1 ;
    END

SET @cntr = 1 ;

WHILE @cntr <= 10
    BEGIN
        INSERT INTO
            dbo.ViewTest_Detail
            (
              ViewTest_Group_ID
            , name 
        )
            SELECT
                G.ViewTest_Group_ID
            ,   'Detail=' + G.Name
            FROM
                dbo.ViewTest_Group G

        SET @cntr = @cntr + 1 ;
    END

GO

/**
--#endregion Populate our new tables
==============================================================================================================================================================================================**/


/**==============================================================================================================================================================================================
--#region Test out various queries using use "Display Estimated Execution Path" button**/
BEGIN

    --run query plan on this one
    SELECT
        *
    FROM
        MaasSqlTest.dbo.ViewTest_GroupDetail

    --now run query plan on this one
    SELECT
        ViewTest_Group_ID
    ,   GroupName
    FROM
        MaasSqlTest.dbo.ViewTest_GroupDetail
    GROUP BY
        ViewTest_Group_ID
    ,   GroupName

    --maybe the group by caused both tables to be accessed, so run query plan on this one
    SELECT
        ViewTest_Group_ID
    ,   GroupName
    FROM
        MaasSqlTest.dbo.ViewTest_GroupDetail

    --can we make it more efficient?
    SELECT
        ViewTest_Group_ID
    ,   GroupName
    FROM
        MaasSqlTest.dbo.ViewTest_GroupDetail
    WHERE
        ViewTest_Group_ID = 1
            
    /**==============================================================================================================================================================================================
    --#region Highlight between the two ==== lines**/
    BEGIN
        --Group by via the view
        SELECT
            ViewTest_Group_ID
        ,   GroupName
        FROM
            MaasSqlTest.dbo.ViewTest_GroupDetail
        GROUP BY
            ViewTest_Group_ID
        ,   GroupName


        --Get rid of the view.  Query the underlying table explicitly..
        SELECT
            ViewTest_Group_ID
        ,   Name GroupName
        FROM
            MaasSqlTest.dbo.ViewTest_Group
        GROUP BY
            ViewTest_Group_ID
        ,   Name
    END
    /**
    --#endregion Highlight between the two ==== lines
    ==============================================================================================================================================================================================**/

END
/**
--#endregion Test out various queries using use "Display Estimated Execution Path" button
==============================================================================================================================================================================================**/

posted on Tuesday, February 12, 2008 12:28 AM Print
Comments
Gravatar
# re: Why Views are evil.
Joe Webb
2/12/2008 1:57 PM
I've found that in some cases views can be useful, but by and large I tend to avoid them for performance reasons.

For example, when you

SELECT
*
FROM
myView
WHERE
col1 < 100

The select statement inside the view is applied with its where clause then your col1 < 100 where clause is applied. That's extra work for SQL Server.

Joe

Gravatar
# re: Why Views are evil.
Jeff
2/12/2008 2:07 PM
Funny, one of my upcoming posts is going to be about how great Views are -- because they are great if you use them well; like anything -- store procedures, tables, databases, classes, interfaces, generics, etc -- they can be abused. As always, never blame the tool, blame the person using the tool or the way that tool was used. If you have been burned by Views in the past, then someone was doing something wrong -- it is as simple as that. It's like proclaiming "ints are evil!" because you once wrote an app that tried to use ints to stored dates or phone numbers or something.
Gravatar
# # re: Why Views are evil.
Jeff M
2/12/2008 2:52 PM
I agree completely. NEVER blame the tool. Blame the idiot who implemented views in the reporting system without understanding how they worked. I blame me!..........Whilst one should not blame the tool, one should in fact develop a healthy respect for how the tool should be used. For instance, chainsaws are best used upon trees and wood. And yes, Leatherface is really the evil one, but don't we all jerk a little at the thought of a misused chainsaw? Most implementations I have seen of views are evil....................

I can't wait to read your post on views and direct colleagues to it!.
Gravatar
# re: Why Views are evil.
Vignesh Kannan
2/18/2008 2:49 PM
Using Views in your query doesn't make a trip to the D/B Server instead the View results are stored in the Cache.Hence Complex Queries such as Report Queries tend to run much faster when making joins with views than using tables. For small tables there s literally the time advantage s not really felt,but if the table s too large the difference s really felt.
Happy coding :)
Gravatar
# re: Why Views are evil.
jeff m
2/20/2008 2:03 PM
I tried to contact Vignesh Kannan via the email left along with the post to get clarification. Also, I did some research. I am unable to validate the comment left by Vignesh other than to assume what was meant was "Indexed Views" as opposed to Views. I believe that angle was sufficiently covered in the blog post. Views, to my knowledge, do not have any special caching powers over any other MS SQL Server object.
Comments have been closed on this topic.
News