Prev: Operand type clash: int is incompatible with ntext
Next: install ssis on windows server 2003
From: Tony Rogerson on 12 Aug 2010 16:04
"--CELKO--" <jcelko212(a)earthlink.net> wrote in message
news:01461052-35ee-4e81-9de9-e314af0df56a(a)z28g2000yqh.googlegroups.com...
> There are two ways to handle VIEWs.
>
> 1) expand them like an in-line macro in the parse tree. This makes
> them equivalent to a derived table expression. This is the only way
> SQL Server has.
>

WRONG!

We also have view materialisation and the engine determines if the
materialised view can be used or if it expands into the body of the query
before optimisation.

> 2) Determine if more than one session is using a VIEW, then
> materialize it as physical table and share it. This is how Oracle, DB2
> and other products do it. Big performance boost here because of the
> extra space available.
>

Yes, but suffers concurrency problems keeping the materialised view in
realtime sync with the data.

> 3) VIEWs on VIEWs actually have great power if you know how to use the
> WITH CHECK OPTION. And they are great for access control.

WITH CHECK OPTION is useless, doesn't guarantee the data because it can be
bypassed.


--ROGGIE--

From: tshad on 12 Aug 2010 17:07

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:6c8d0f25-7c8e-4c65-a2d2-c9225d490a48(a)l6g2000yqb.googlegroups.com...
> Views built on top of views will not perform slower that equivalent
> query on the base tables. As Erland pointed out a view is expanded
> just like a macro in the query plan. The difference comes in the
> example when you abuse the view to query for data that can be
> retrieved by a query that is a lot simpler than the query on which the
> view is based.
>
So you are saying the article was wrong saying that views built on views are
slower and should be avoided. He doesn't mention bad use of views.

He is saying that you should always write views directly to the base tables.

"Views can especially degrade the performance if they are based on other
views. Therefore, it is recommended NOT to create views based on other
views. All views should be created against base tables."
> --
> Plamen Ratchev
> http://www.SQLStudio.com


From: Plamen Ratchev on 12 Aug 2010 17:37
Yes, if the expanded view code is equivalent to the query needed to
retrieve data then the article is wrong. Plain use of views on top of
views is not slow. You can easily make your own test and look at the
execution plans.

--
Plamen Ratchev
http://www.SQLStudio.com
From: Erland Sommarskog on 12 Aug 2010 18:20
tshad (tfs(a)dslextreme.com) writes:
> He is saying that you should always write views directly to the base
> tables.
>
> "Views can especially degrade the performance if they are based on other
> views. Therefore, it is recommended NOT to create views based on other
> views. All views should be created against base tables."

This is both true and untrue. Technically it is untrue, because as Plamen
says, the expanded query will have the same performance.

But from a practical point of view it is true, because what may happen
is that you have same table joined to itself multiple times, when you
only need it in the query once. That is, you would expand the composed
view into the base tables, you would see that the query can be simplified.

So from that point of view, the advice is not bad, but it is also obvious
that as long as you know what you are doing, views built on views does
not have to be bad.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: m on 12 Aug 2010 18:44
to extend Tony's comments:

Materializing views is an optimization question only since it does not
change the meaning of any query and it might improve or reduce performance.
An improvement in performance might be observed if several concurrent, or
sequentially concurrent, selects execute against a significant portion of
the set of rows covered by the view and the access time for the materialized
rows is significantly less then for the the same rows in the underlying
tables (either because of size of indices); but a decrease in performance
might equally be observed if those same near concurrent selects reference
diverse portions of a large view and the the access time for a materialized
row is not significantly different from the access time for a table row.

At the risk of making an unwarranted characterization, materialized views
are unsuitable for OLTP workload, but can improve parallel data warehouse
queries.

"Tony Rogerson" <tonyrogerson(a)torver.net> wrote in message
news:8371475C-9389-44B5-A983-42429DE044CE(a)microsoft.com...
> "--CELKO--" <jcelko212(a)earthlink.net> wrote in message
> news:01461052-35ee-4e81-9de9-e314af0df56a(a)z28g2000yqh.googlegroups.com...
>> There are two ways to handle VIEWs.
>>
>> 1) expand them like an in-line macro in the parse tree. This makes
>> them equivalent to a derived table expression. This is the only way
>> SQL Server has.
>>
>
> WRONG!
>
> We also have view materialisation and the engine determines if the
> materialised view can be used or if it expands into the body of the query
> before optimisation.
>
>> 2) Determine if more than one session is using a VIEW, then
>> materialize it as physical table and share it. This is how Oracle, DB2
>> and other products do it. Big performance boost here because of the
>> extra space available.
>>
>
> Yes, but suffers concurrency problems keeping the materialised view in
> realtime sync with the data.
>
>> 3) VIEWs on VIEWs actually have great power if you know how to use the
>> WITH CHECK OPTION. And they are great for access control.
>
> WITH CHECK OPTION is useless, doesn't guarantee the data because it can be
> bypassed.
>
>
> --ROGGIE--

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Operand type clash: int is incompatible with ntext
Next: install ssis on windows server 2003