Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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
like image 299
LaBracca Avatar asked Sep 07 '10 11:09

LaBracca


Video Answer


2 Answers

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.

like image 174
gbn Avatar answered Sep 25 '22 21:09

gbn


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

like image 41
Martin Smith Avatar answered Sep 23 '22 21:09

Martin Smith