SQL SERVER – Adding Column is Expensive by Joining Table Outside View – Limitation of the Views Part 2

Update: Please read the summary post of all the 11 Limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more…

Note: I have updated the title based on feedback of Davide Mauri (Solid Quality Mentors). Thank you for your help.

Let’s see another reason why I do not like Views. Regular queries or Stored Procedures give us flexibility when we need another column; we can add a column to regular queries right away. If we want to do the same with Views, we will have to modify them first. This means any query that does not need this column will start having the column’s data additionally. This will lead to added network traffic as well as it will reduce the performance of the part where the View is used. This further leads to a conclusion: it may not be good idea to alter the View in order to add an additional column if the View happens to be used at multiple places. An alternative solution would be adding the column outside the View. However, this solution can be very expensive.

In today’s limitation of the View, we will see how adding an additional column outside the view can be very expensive, whereas the same situation does not happen with regular T-SQL query.

Let us first create a View using a sample database called AdventureWorks.

USE AdventureWorks
GO
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[vw_ViewLimit1]'))
DROP VIEW [dbo].[vw_ViewLimit1]
GO
-- Create View on sample tables
CREATE VIEW vw_ViewLimit1
AS
SELECT
[SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber]
,[OrderQty],sod.[ProductID],[SpecialOfferID],[UnitPrice],[UnitPriceDiscount]
,[LineTotal],[ReferenceOrderID]
FROM Sales.SalesOrderDetail sod
INNER JOIN Production.TransactionHistory th ON sod.SalesOrderID = th.ReferenceOrderID
GO

Now let us compare the performance of the view with same SELECT statement used in the view.

/* Now compare the execution plan of the view and same definition of T-SQL statement */
-- Filter View with WHERE condition
SELECT *
FROM vw_ViewLimit1
WHERE SalesOrderDetailID > 111111
GO
-- Regular SELECT statement with WHERE condition
SELECT [SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber]
,[OrderQty],sod.[ProductID],[SpecialOfferID],[UnitPrice],[UnitPriceDiscount]
,[LineTotal],[ReferenceOrderID]
FROM Sales.SalesOrderDetail sod
INNER JOIN Production.TransactionHistory th ON sod.SalesOrderID = th.ReferenceOrderID
WHERE SalesOrderDetailID > 111111
GO

You will notice that the performance of the View and the SELECT statement is the same since the query cost of both statements is 50%.

Let us assume that we have to retrieve one more row from the table used in the View. As explained in the first paragraph, altering the View by adding a column to that View may actually lead to unnecessary data for the query using the View, but not looking for that column. The natural alternative solution to this is to use JOIN and add the column to the Views. In the case of T-SQL, we would not have to do the same since we will just go ahead and add the column to the statement.

/* Now let us try to retrieve the column which is not in View */
/* When you compare the performance you will notice View is more expensive*/
-- View with extra column
SELECT v1.*
,
th.[Quantity]
FROM vw_ViewLimit1 v1
INNER JOIN Production.TransactionHistory th ON v1.SalesOrderID = th.ReferenceOrderID
WHERE SalesOrderDetailID > 111111
GO
-- Select statement with extra column
SELECT [SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber]
,[OrderQty],sod.[ProductID],[SpecialOfferID],[UnitPrice],[UnitPriceDiscount]
,[LineTotal],[ReferenceOrderID]
,th.[Quantity]
FROM Sales.SalesOrderDetail sod
INNER JOIN Production.TransactionHistory th ON sod.SalesOrderID = th.ReferenceOrderID
WHERE SalesOrderDetailID > 111111
GO
-- Clean up
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[vw_ViewLimit1]'))
DROP VIEW [dbo].[vw_ViewLimit1]
GO

We can now check the performance of both queries using the execution plan.  This will clearly show that a regular T-SQL statement with an additional column is truly less expensive than View that is retrieving an additional column using JOIN.

Click on the image to enlarge it.

Again, if you are not using the View at multiple places, I suggest that you modify it just fine because doing so will not cause you to lose any performance.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

17 thoughts on “SQL SERVER – Adding Column is Expensive by Joining Table Outside View – Limitation of the Views Part 2

  1. I’ve tried to phrase this comments couple of times and I still isn’t exactly what I wanted to say so I’ll just post it and ask if you don’t understand something :)

    Views and especially schemabound views has one upside which is ironically quite the opposite what’s this article about.

    When I create schemabound view it’ll force me to make sure that:

    a) I’ve thought about the situation that understand what I’m doing
    b) Later on, if I need to alter table schema schemabinding makes sure that I can’t do things that’ll break lots of things up

    So if I suddenly realize that I need to add some column to my query, which is not in the view, I need to think that is this what I really want and need. Is this something one time only or is it something that is used a lot?

    If former I’ll just make ad-hoc query and be done with it.

    But if latter I’ll add the column to the view. DB then goes and creates execution plans and statistics for the changed view and possibly optimizes execution plan and speeds up the query.

  2. It may add overhead to add the column outside the view, but using the view consumes far less processing time. I just rewrote a stored procedure that was taking 27 minutes to execute. I created a schema-bound view, which I joined to another view in the sproc. The query now executes in 6 minutes.

  3. Pingback: SQLAuthority News – A Monthly Round Up of SQLAuthority Blog Posts – August 2010 Journey to SQL Authority with Pinal Dave

  4. Pingback: SQL SERVER – View Over the View Not Possible with Index View – Limitations of the View 11 Journey to SQL Authority with Pinal Dave

  5. Interesting article. I’m glad I found as I was about to make somewhat of a similar change.

    I have a view consisting of 12 unions and all 12 unions join to customer table. I thought if I take the customer join outside the view and do 1 join to customer table the performance should be much better. Thinking logically joining once should be better than joining 12 times?

    What do you guys think?

  6. Two questions:

    1) Why are you using SELECT * … ? As an SQL Authority you should know that not explicitly specifying what columns you need will lead to brittle and potentially underperformant queries.

    2) Assuming that you explicitly select the columns you need eg. SELECT [SalesOrderID],[SalesOrderDetailID], … (see question 1) what is the performance impact of adding an additional column to the view?

    Best regards,
    Andreas

    • Yeah! right thanks… better focus the content, we will publish a new article about the code of conduct and tsql etiquette & ethics.

      Regards,
      David Smith

  7. Thank you, that saved me some time. How much of your time have you spent blogging SQL Server? You must be an online legend at it!

    Thanks again, Mick.

  8. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehouseing Concepts – Day 20 of 31 Journey to SQLAuthority

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #008 « SQL Server Journey with SQL Authority

  10. Pingback: SQL SERVER – Weekly Series – Memory Lane – #044 | Journey to SQL Authority with Pinal Dave

  11. Pingback: SQL SERVER – Weekly Series – Memory Lane – #048 | Journey to SQL Authority with Pinal Dave

  12. Pingback: SQL SERVER – Weekly Series – Memory Lane – #049 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s