Using the same View repeatedly

[jc]

Hi.
A question I have is with regard to the use of views with SQL2000. If
I have a view called "A_view" and used in the following manner;

----------------
SELECT ...
FROM A_View
WHERE ....

UNION

SELECT ....
FROM A_View
WHERE .....
-----------------

is the view computed twice? Ideally if the view is computationally
expensive I would rather it was only computed once.
Also this would be preferred for data consistency.

Is there a way to ensure the view is only computes once?

Regards JC.......

[Erland Sommarskog]

jc (jc**********@o ptusnet.com.au) writes:

A question I have is with regard to the use of views with SQL2000. If
I have a view called "A_view" and used in the following manner;

----------------
SELECT ...
FROM A_View
WHERE ....

UNION

SELECT ....
FROM A_View
WHERE .....
-----------------

is the view computed twice? Ideally if the view is computationally
expensive I would rather it was only computed once.
Also this would be preferred for data consistency.

Is there a way to ensure the view is only computes once?

You can never be sure what the optimizer is up to, but you are correct
to assume that the view culd be evaluated twice for this type of query.
And if data is being inserted/modified while the query is running, the
two evaluiations could give different result.

To see how the optimizer computes the query, you can run the query in
Query Analyzer. You can just press Ctrl/L or select Query->Display
Estimated Execution Plan. Or you can press Ctrl/K and then run the
query to see the actual plan.

One way to avoid that the view is evaluated twice is to select the
result into a temp table, and then use that table in the query.

However this may have detrimental effect on performance. You see, the
when running the UNION query, SQL Server may never compute the full
view at all. Basically, the optimizer expands the view as a macro,
and applies the WHERE clause to that. As long the result is the same,
the optimizer may rearrange how the query is computed. So, theoretically,
the two parts in the UNION could have very different plans that only
computed a subset of the view.

The only way to find out what's the best is to benchmark.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

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

[--CELKO--]

Using the same table in two SELECTs of a UNION expression is usually
bad programming. You can almost always reduce it to:

SELECT [DISTINCT] ...
FROM Foobar
WHERE ...
OR ...;

When you get to a product with CTE (Common Table Expressions), you can
use a WITH clause to materialize the VIEW.

But having said all of this, you have to trust the optimizer. And most
optimizers are bad about UNION, INTERSECT and EXCEPT operations because
they are not that common.