Prev: Operand type clash: int is incompatible with ntext
Next: install ssis on windows server 2003
From: tshad on 12 Aug 2010 02:32
Are there issues with Views?

I have heard that views can be a problem because they use a lot of memory
and that it is better to write the queries against the actual tables instead
of using views. That views are inefficient.

I had thought that it is identical to the code it replaces however, so not
sure if this is the case.

If a view were replaced with a subquery - wouldn't the query plan be the
same and therefore the same time and resources?

One question is that if you have a view, your programmers may depend on them
and where they may be efficient in one place they would be inefficient in
another. For example, if you only need one column from a table but the view
passes back 8 or 9 since the view would be more generic.

Thanks,

Tom


From: Erland Sommarskog on 12 Aug 2010 03:30
tshad (tfs(a)dslextreme.com) writes:
> I have heard that views can be a problem because they use a lot of
> memory and that it is better to write the queries against the actual
> tables instead of using views. That views are inefficient.
>
> I had thought that it is identical to the code it replaces however, so not
> sure if this is the case.

Correct. A view is just a macro which is pasted into the query, and the
optimizer works with the expanded query.

Indexed views are kind of a special case - but only if you use the NOEXPAND
hint.

> One question is that if you have a view, your programmers may depend on
> them and where they may be efficient in one place they would be
> inefficient in another. For example, if you only need one column from a
> table but the view passes back 8 or 9 since the view would be more
> generic.

If you have a view that retrieves values from 7 tables, and you write a
query against the view that retrieves only values from 4 tables, and the
other table does not affect the result set, the optimizer should be able
to factor away those tables.

But if the view is the complex, this may not be possible - maybe because
there would be a subtle change of meaning. Moreover of if you have views
built on views a simple:

SELECT a FROM someview WHERE b = 12

This may prove to be a monster query that takes a lot or resources. When in
fact it would have been possible to achieve the same result with a quite
simple query.


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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: tshad on 12 Aug 2010 10:46
Makes sense.

One article said that views built on views would be a little slow so not a
good idea.

I assume the author was talking about views compared to equivelent code
would still be slower just because they were views.

http://sqlserverpedia.com/wiki/Views_-_Advantages_and_Disadvantages

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.

Thanks,

Tom

"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9DD260C02AAFEYazorman(a)127.0.0.1...
> tshad (tfs(a)dslextreme.com) writes:
>> I have heard that views can be a problem because they use a lot of
>> memory and that it is better to write the queries against the actual
>> tables instead of using views. That views are inefficient.
>>
>> I had thought that it is identical to the code it replaces however, so
>> not
>> sure if this is the case.
>
> Correct. A view is just a macro which is pasted into the query, and the
> optimizer works with the expanded query.
>
> Indexed views are kind of a special case - but only if you use the
> NOEXPAND
> hint.
>
>> One question is that if you have a view, your programmers may depend on
>> them and where they may be efficient in one place they would be
>> inefficient in another. For example, if you only need one column from a
>> table but the view passes back 8 or 9 since the view would be more
>> generic.
>
> If you have a view that retrieves values from 7 tables, and you write a
> query against the view that retrieves only values from 4 tables, and the
> other table does not affect the result set, the optimizer should be able
> to factor away those tables.
>
> But if the view is the complex, this may not be possible - maybe because
> there would be a subtle change of meaning. Moreover of if you have views
> built on views a simple:
>
> SELECT a FROM someview WHERE b = 12
>
> This may prove to be a monster query that takes a lot or resources. When
> in
> fact it would have been possible to achieve the same result with a quite
> simple query.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


From: Plamen Ratchev on 12 Aug 2010 11:13
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.

--
Plamen Ratchev
http://www.SQLStudio.com
From: --CELKO-- on 12 Aug 2010 12:27
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.

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.

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.
 |  Next  |  Last
Pages: 1 2 3
Prev: Operand type clash: int is incompatible with ntext
Next: install ssis on windows server 2003