03 January 2008 09:10 tonyrogerson

VIEWS - they offer no optimisation benefits; they are simply inline macros - use sparingly.

What are views? Do you think they hold any optimisation properties from being precompiled?

What is a View?

A View is simply an alias name given to a query that can be used in other queries; logically (and it only is logically) you can visualise it as a virtual table, but from an optimisation perspective they are not virtual tables – this is extremely important to remember when designing queries that you want to scale and perform well in a real environment.

How does SQL Server use the View?

The View is stored in the system catalogue as meta data, there is no execution plan associated with a view, it is never compiled.

When you use your View in a query SQL Server gets the View meta data and expands it into the point in your query where your View is used. Once this expansion has taken place there is no longer a concept of an individual view, SQL Server has simply copied and pasted your View definition to the point of reference in the query you are executing. The optimiser then creates a single execution plan for that combined query.

Background reading: http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx

Example

create table blah (

    c1  int not null primary key clustered,

    c2  int not null

)

go

 

create table blah_detail (

    cd1  int not null primary key clustered,

    cd2  int not null references blah( c1 )

)

go

 

insert blah ( c1, c2 ) values( 1, 1 )

insert blah ( c1, c2 ) values( 2, 1 )

insert blah ( c1, c2 ) values( 3, 1 )

go

 

insert blah_detail ( cd1, cd2 ) values( 1, 1 )

insert blah_detail ( cd1, cd2 ) values( 2, 1 )

insert blah_detail ( cd1, cd2 ) values( 3, 2 )

go


create
view dbo.vw_blah

    with schemabinding

as

    select c1, c2

    from dbo.blah b

        inner join dbo.blah_detail bd on bd.cd2 = b.c1

    where c2 % 2 = 1 -- odd number

go

Let’s look at two queries, the first uses the View:

update dbo.vw_blah

    set c2 = 5

where c1 = 1

Look at the Execution Plan:

  |--Clustered Index Update(OBJECT:([tempdb].[dbo].[blah].[PK__blah__15BCB523]), SET:([tempdb].[dbo].[blah].[c2] = [Expr1011]))

       |--Compute Scalar(DEFINE:([Expr1011]=(5)))

            |--Stream Aggregate(DEFINE:([ b].[c1]=ANY([tempdb].[dbo].[blah].[c1] as [ b].[c1])))

                 |--Nested Loops(Inner Join)

                      |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[blah].[PK__blah__15BCB523] AS [ b]), SEEK:([ b].[c1]=(1)),  WHERE:([tempdb].[dbo].[blah].[c2] as [ b].[c2]%(2)=(1)) ORDERED FORWARD)

                      |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[blah_detail].[PK__blah_detail__5C934F3C] AS [ bd]), WHERE:([tempdb].[dbo].[blah_detail].[cd2] as [ bd].[cd2]=(1)))

There is no mention of the view vw_blah anywhere in this Estimated execution plan. The View has been expanded into the main query and then optimised.

Let’s look at another query:

update dbo.blah

    set c2 = 5

from dbo.blah b

    inner join dbo.blah_detail bd on bd.cd2 = b.c1

where c2 % 2 = 1 -- odd number

  and c1 = 1

  |--Clustered Index Update(OBJECT:([tempdb].[dbo].[blah].[PK__blah__15BCB523]), SET:([tempdb].[dbo].[blah].[c2] = [Expr1004]))

       |--Compute Scalar(DEFINE:([Expr1004]=(5)))

            |--Stream Aggregate(DEFINE:([ b].[c1]=ANY([tempdb].[dbo].[blah].[c1] as [ b].[c1])))

                 |--Nested Loops(Inner Join)

                      |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[blah].[PK__blah__15BCB523] AS [ b]), SEEK:([ b].[c1]=(1)),  WHERE:([tempdb].[dbo].[blah].[c2] as [ b].[c2]%(2)=(1)) ORDERED FORWARD)

                      |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[blah_detail].[PK__blah_detail__5C934F3C] AS [ bd]), WHERE:([tempdb].[dbo].[blah_detail].[cd2] as [ bd].[cd2]=(1)))


Notice how the query is exactly the same as the one using the View?

Views are nothing but inline macros, the macro part being the View Expansion into the main query; you will not see the term inline macro, in Books Online and in talking about Views we use the term Expansion.

There is one optimisation benefit you can gain with a View and that is indexing it; but, in reality you aren’t actually gaining a performance because of the View you are gaining a performance benefit because the index is filtered, it only contains the data that the View filters to thus there is less data to consume. Think of Index Views as summary tables – separate physical tables in their own right.

Using Views in the real world

Please oh please think what you are doing when using Views and for god sake do not nest them!

My bread and butter comes from consultancy, I do remote support by the hour or on site visits for companies having performance problems or just need advice on SQL Server.

Consider that once I’ve identified what statement is causing the performance problem I then need to investigate optimisation, if that statement is just SELECT x,y,z FROM YourMassiveBlah where YourMassiveBlah is a multi-table, multi-view View I need to expand the who thing just to see what is going on and what bits I can optimise to take advantage of things like derived tables, temporary table, Common Table Expressions or other tricks of the trade, to put a figure on it - would you rather me spend a billable hour on cutting and pasting to reconstruct the query that is being executed because you used Views or me spend a billable hour on tuning.

Another common sense tick is to prefix or suffix View names so they are easily identified without any digging, my preference is to use vw_<object>.

I’ll look at the WITH CHECK OPTION on the View shortly (see http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/01/15/with-check-option-on-create-view-what-is-it-how-s-it-work-what-other-options-are-available-for-referntial-integrity-ri-part-1.aspx) and in depth and will compare various mechanisms for keeping data consistency i.e. using Views WITH CHECK OPTION, CHECK constraints and TRIGGERS.

Filed under:

Comments

# re: VIEWS - they offer no optimisation benefits; they are simply inline macros - use sparingly.

03 January 2008 10:48 by GrumpyOldDBA

don't put people off using views, I need the work too! I'm always amused to see posts under "performance" for views, sort of an oxymoron. I think they can have uses but too often they disguise aweful data structures that are almost impossible to tune, one point though is that from sql 2005 you can use plan guides to control parallelism or indeed add other hints - that's if you can't get rid of them in the first place.

# re: VIEWS - they offer no optimisation benefits; they are simply inline macros - use sparingly.

03 January 2008 14:56 by AdamMachanic

Agreed, they're certainly not good for performance, but they're not necessarily bad either; I don't use them too often, but on some occasions they can be invaluable for encapsulating commonly used joins and simplifying client/stored proc code.  BTW, I mostly use inline TVFs instead of views these days.  Unlike straight views, they do arguably have some performance benefit, since you can force the caller to use a selective predicate... Best of both worlds :)

# re: VIEWS - they offer no optimisation benefits; they are simply inline macros - use sparingly.

03 January 2008 19:27 by simonsabin

Indexed views on the other hand do offer performance improvements, however it will impact your wirte performance and you have to jump through some hoops to get them to be used.

# re: VIEWS - they offer no optimisation benefits; they are simply inline macros - use sparingly.

03 January 2008 20:31 by tonyrogerson

Indexed Views and hoops; man - that is one ruddy big hoop dude!

Not only are there all the required ANSI settings that usually break existing applications there is the Serialisation of transactions when updating the Index View.

Great technique in theory; yet to use in practice... - personally, I prefer my own summary tables and keeping them up-to-date using triggers...

# re: VIEWS - they offer no optimisation benefits; they are simply inline macros - use sparingly.

04 January 2008 08:57 by GrumpyOldDBA

Now there's an interesting comment Tony. I was working on optimising Navision for some time, by default this product maintains summary tables using triggers and boy does it screw the system big time - no inplace updates, lots of page splits, rubbish performance ( ok there are other issues ) - so microsoft's solution to the triggers is to replace the summary tables with indexed views, as the summary tables already exist it won't add more data storage but it will remove triggers from the transactional tables. I quite like indexed views myself.

# re: VIEWS - they offer no optimisation benefits; they are simply inline macros - use sparingly.

04 January 2008 09:18 by tonyrogerson

I'll write a blog up in the next couple of days on it, may be today if I get chance (and get my invoices out :)).

Say you have this table...

create table raw_data (

   raw_data_id     int not null identity primary key clustered,

   individual_id   int not null,

   some_count      int not null,

   make_row_bigger char(1024) not null default( 'asdasd' )

)

create index ncidx on raw_data( individual_id )

go

And this view...

create view dbo.vw_sum_raw_data_individual

   with schemabinding

as

   select individual_id, sum_some_count = sum( some_count ), cntbig = count_big( * )

   from dbo.raw_data

   group by individual_id

go

create unique clustered index iv_clidx on dbo.vw_sum_raw_data_individual( individual_id )

go

Your app does this...

begin tran

update raw_data

   set some_count = some_count + 1

where individual_id = 5

 and raw_data_id   = 5

Note, that it's updating a single row in raw_data!

In another connection go and do this, which for the same individual updates a different row...

begin tran

update raw_data

   set some_count = some_count + 1

where individual_id = 5

 and raw_data_id   = 15

This will block; the reason - because the view is summarised to individual_id level and that is the granularity of the lock; basically - if you update a row in raw_data then row in the view for that individual will be locked preventing any other connection from reading or updating it - very very big concurrency problem.

Ok - the summary table approach will suffer the same problem but it can be managed better for instance deferring the update of the summary table until the last thing you do in the transaction batch.

# re: VIEWS - they offer no optimisation benefits; they are simply inline macros - use sparingly.

06 January 2008 20:57 by GrumpyOldDBA

ah - but which will be quicker?  and to be honest in a financial system serialisable transactions are not uncommon - Navision offers serialisable selects as an option!

You see this sort of stuff is real world, don't get this in books or training courses ( very often )  perhaps you could do a demo at a user group night? can I have a 100 million row table at least 4k wide ( maybe 100 columns with 20 being summarised ) as the source though < grin >  ta!

# re: VIEWS - they offer no optimisation benefits; they are simply inline macros - use sparingly.

08 January 2008 22:09 by Alex_Kuznetsov

Hi Simon,

I see two big problems with indexed views:

1. If they contain aggregates, they cause lock contention and or deadlocks.

2. If they contain pre-joined tables, they might be much bigger than the tables they join. So a query against that view might actually be slower than a join.

Based on that, I always make sure my applications work even if the DBA has dropped the indexed view. On standard edition, instead of just issuing a select with NOEXPAND, I always check if the view is still indexed. If it is not, I issue another select without the hint.

This is why I am usually avoiding indexed views in OLTP, unless they are used to enforce business rules.

# Nested views - locking | keyongtech

18 January 2009 17:05 by Nested views - locking | keyongtech

Pingback from  Nested views - locking | keyongtech

# How to create a One to One relationship in SQL Server using DRI, Triggers and Views

23 January 2010 10:51 by Tony Rogerson's ramblings on SQL Server

It&#39;s beyond me to create a one to one relationship in SQL Server if you are inserting directly into