How can you optimize duplicated work in views?

[malcolm]

This one's kind of hard to explain, so I've opted to post a simplified
version of our view that prompted me to ask this question: The
question is re-asked after the view...

create view MainView (
PrimaryKeyID,
SubTotal1,
SubTotal2,
GrandTotal
)
as

select t.PrimaryKeyID,
sum(t1.Total),
sum(t2.Total),
sum(t1.Total) + sum(t2.Total)
from SomeTable t
join CalculationTable t1 on ...
join AnotherCalculationTable t2 on ...
Notice in the 3rd column called "GrandTotal" how it calls the function
"sum" two more times. Common sense tells me that this is not
necessary. in our case it's orders of magnitude worse... Is the query
optimizer smart enough to only call these sums once per row in
"SomeTable"? Common sense tells me that if we were to break the views
apart into two views it would avoid this ineffeciency:

create view InnerView (
PrimaryKeyID,
SubTotal1,
SubTotal2
)
as

select t.PrimaryKeyID,
sum(t1.Total),
sum(t2.Total)
from SomeTable t
join CalculationTable t1 on ...
join AnotherCalculationTable t2 on ...

create view OuterView (
PrimaryKeyID,
SubTotal1,
SutTotal2,
GrandTotal
)
as

select iv.PrimaryKeyID,
iv.Total1,
iv.Total2,
iv.Total1 + iv.Total2
from InnerView

Notice how it appears that we've tricked the optimizer into thinking
there are less operations. So my question is how does views handle
this situation? Does the optimizer treat both version the same? Or is
one faster than the other? Or is there another, faster way? Does
adding levels of views slow down things, or are views simply like
macros and get removed when compiled (I think I've read the latter is
true actually)

Thanks,
Dave

[Erland Sommarskog]

malcolm (ch********@yahoo.com) writes:

create view MainView (
PrimaryKeyID,
SubTotal1,
SubTotal2,
GrandTotal
)
as

select t.PrimaryKeyID,
sum(t1.Total),
sum(t2.Total),
sum(t1.Total) + sum(t2.Total)
from SomeTable t
join CalculationTable t1 on ...
join AnotherCalculationTable t2 on ...
Notice in the 3rd column called "GrandTotal" how it calls the function
"sum" two more times. Common sense tells me that this is not
necessary. in our case it's orders of magnitude worse... Is the query
optimizer smart enough to only call these sums once per row in
"SomeTable"? Common sense tells me that if we were to break the views
apart into two views it would avoid this ineffeciency:

I ran this query in Northwind:

select OrderID, SUM(UnitPrice), SUM(Discount),
SUM(UnitPrice) + SUM(Discount)
from [Order Details]
group by OrderID

When I looked at the query plan, it appears to compute the first two sums
in a stream aggregate, and then computed the last sum as a sclar value,
being the sum of the two sums.

However, this may not at all be applicable to your actual query, since
your expressions may be more complex and not lend themselves to
arithmetic simplifications.

In general, though, I think you can trust the optimizer to do the
right thing. Rewriting the query in steps as you suggested, can just
as well make matters worse, since you could confuse the optimizer.
(Or even worse confuse yourself, so that you get incorrect results.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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