Views Efficiency

[Andrew Banks]

How efficient is ti to use join views in a database?

I'm developing an e-commerce system and using join views to join the
product, product category and product review tables and wondering if this
would have any adverse effect on performance.

Thanks in advance

[Erland Sommarskog]

Andrew Banks (ba****@nojunkblueyonder.co.uk) writes:

How efficient is ti to use join views in a database?

I'm developing an e-commerce system and using join views to join the
product, product category and product review tables and wondering if this
would have any adverse effect on performance.

Views are essentially macros. That is, there are no query plans
associated with views, so views or direct SELECT statements does
not really make much difference.

There is one exception, and that is you create a clustered index
on the view. In this case the view is materialized, and SELECT:s
against the view are likely to be faster than SELECTs against the
table. The price you pay is that updates takes longer time.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Andrew Banks]

Thanks Erland

"Erland Sommarskog" <so****@algonet.se> wrote in message
news:Xn**********************@127.0.0.1...

Andrew Banks (ba****@nojunkblueyonder.co.uk) writes:

How efficient is ti to use join views in a database?

I'm developing an e-commerce system and using join views to join the
product, product category and product review tables and wondering if this would have any adverse effect on performance.

Views are essentially macros. That is, there are no query plans
associated with views, so views or direct SELECT statements does
not really make much difference.

There is one exception, and that is you create a clustered index
on the view. In this case the view is materialized, and SELECT:s
against the view are likely to be faster than SELECTs against the
table. The price you pay is that updates takes longer time.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Delbert Glass]

FYI

Sometimes when a view is used more then once in a query,
a better plan is generated for the query then when no view is used.

Seems to me, the query optimizer is a bit limited/lazy about realizing
various parts of query are the same. But at least one can use a view
to help the query optimizer come to that realization; upon which,
the query optimizer will consider taking advantage of that fact.

Bye,
Delbert Glass

"Erland Sommarskog" <so****@algonet.se> wrote in message
news:Xn**********************@127.0.0.1...

Andrew Banks (ba****@nojunkblueyonder.co.uk) writes:

How efficient is ti to use join views in a database?

I'm developing an e-commerce system and using join views to join the
product, product category and product review tables and wondering if this would have any adverse effect on performance.

Views are essentially macros. That is, there are no query plans
associated with views, so views or direct SELECT statements does
not really make much difference.

There is one exception, and that is you create a clustered index
on the view. In this case the view is materialized, and SELECT:s
against the view are likely to be faster than SELECTs against the
table. The price you pay is that updates takes longer time.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp