Myujth

I have a view that pulls from approximately 30 different tables. There is one master table, and 29 tables that left join to the master.

-- master view
select
 m.mkey, m.x, m.y, a.i, func(b.j) bjfunc, c.k ...
from
 master m left join
 atable a on
 m.akey = a.akey left join
 btable b on
 m.bkey = b.bkey left join
 ctable c on
 m.ckey = c.ckey
 ...

We have an application that requires the view as is, using all data returned from it, so I need to maintain it as is. As we continue to develop, we're finding ourselves selecting sub-sets of the view as well for other applications. In this case we simply can't use the view as it's far too heavy for what's needed, and we end up creating new views, that duplicate a portion of the main view.

-- sub-view abview
select
 m.mkey, m.y, a.i, func(b.j) bjfunc
from
 master m left join
 atable a on
 m.akey = a.akey left join
 btable b on
 m.bkey = b.bkey

I'm thinking I can substitute this sub-view back into the master view as follows:

-- master view
select
 m.mkey, m.x, ab.y, ab.i, ab.bjfunc, c.k ...
from
 master m left join
 abview ab on
 m.mkey = ab.mkey left join
 ctable c on
 m.ckey = c.ckey
 ...

This would help to keep the two views synchronized, and make the master view a little more aesthetically pleasing by cutting the joins/aliases in half.

Finally:
My question is how much would this affect the performance of the master view? I'm not sure how well the optimizer would handle bringing those sub-views together. Linking each sub-view on a master table lookup vs. the original master view linking all tables on the master table at once.

Am I better off just maintaining separate views?

Edit: Using SQL Server 2016

I've been doing some benchmarking.

I can see the number of logical reads against the master table increasing each time I break out a new sub-view.

The query duration appears to be only slightly affected. I think the question is now of maintainability vs performance, and where to balance the two.