CODEWITHSUNDEEP
sql-serveroptimizationviewquery-optimization
UnDiUdin
UnDiUdin

Reputation: 15384

Joins against views: performance issues

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

Upvotes: 5

Views: 11599

Answers (4)

gbn
gbn

Reputation: 432261

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.

Upvotes: 5

Martin Smith
Martin Smith

Reputation: 453278

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

Upvotes: 3

erikkallen
erikkallen

Reputation: 34391

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

Upvotes: 0

Denis Valeev
Denis Valeev

Reputation: 6015

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.

Upvotes: 0

Related Questions