Posts
10
Comments
14
Trackbacks
0
Monday, February 11, 2008
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 @ Tuesday, February 12, 2008 12:28 AM | Feedback (5)
My momma always said share.
My momma always told me to share.  Thankfully, sharing isn't always a two way street. 

Thankfully, because, I've never been great at it.  I used to feel guilty every time I used the Internet to solve a problem at work.  After all, weren't they paying "me" not "them" to sweat?  

Long story short, Internet Usage Guilt ( IUG ) is no longer on my medical chart.  In fact, I'm hoping to not just continue my Internet usage, but to start on that two way street of giving.  My mom would be proud.  If she knew what the Internet is.

It's been said that as you give, so you will receive.  Before even posting once on SqlTeam, I've been given some great news I longed for over many days: http://www.ssmstoolspack.com
Mladen, going by the handle Spirit1 here on SqlTeam, has RedGate-ish software that I'm jumping to try.  Reading the features list makes my mouth salivate with glee!  I will of course be PayPal-ing, as soon as the next PayCheck arrives....

So basically, I hope this blog will serve as my two way payback for all the years of sharing on SqlTeam.
posted @ Monday, February 11, 2008 3:17 AM | Feedback (1)
News