Joins against views: performance issues

Question

From a performance point of view is it the same doing this:

select * from MYTABLEONE MT1
join MYVIEW MV on MT1.ID = MV.ID

( where the view is

create view MYVIEW as
select MT2.*, MT3.*
from MYTABLETWO MT2
join MYTABLETHREE MT3 on MT2.OtherID = MT3.OtherID

)

Or is it better to do this:

select MT1.*, MT2.*, MT3.*
from MYTABLEONE MT1
join MYTABLETWO MT2 on MT1.ID = MT2.ID
join MYTABLETHREE MT3 on MT2.OtherID = MT3.OtherID

Answer 1

Unless MYVIEW is an indexed view there's no loss or gain performance-wise.

Besides, it's easy to compare two selects by looking at the "estimated subtree cost" in the Actual Execution Plan.

Answer 2

It should be the same. Please, everyone, start trusting DBMSs to be smarter than they were 25 years ago.

Answer 3

One would hope that there is no difference in that straightforward case.

When working with nested Views and Views joined upon Views though it is always worth checking the execution plans to confirm this.

Here's one example of a case where the plans of joined Views are less than optimal.

There can also be issues with predicate pushing in Views

Answer 4

Assuming not an indexed view...

The view will be expanded like a macro and the same plan should be generated.

Having the view adds no value unless it's reused. However, you can then end up with view joining to view joining to view which, when expanded, gives a far more complex plan than expected.

IMHO, don't use a view unless you know what you're doing.