Generate view sql dynamically

Question

I have a view with code as follows

create view v1
as
section1 --I get column1 data here
union all
section2 --I get column2 data here
union all
section3 --I get column3 data here

Now, this view is used in multiple places and will be joined on column1 or 2 or 3 depending upon the place where we use this like below.

select * from tabl1 t1 join v1 on t1.column1 = v1.column1
select * from tabl1 t2 join v1 on t2.column2 = v1.column2

etc

But if it is joining on column1, computation for column2,3 i.e, section 2,3 is not needed. As per current business rules, we cant split the view into multiple views. Now, what I need is that if view gets joined on column1, section2,3 should not be computed and similar is case with column2,section1,3 and column3,section1,2

Could someone please help me how to achieve this

Thanks, Sree

Sometimes SQL server optimizer can ignore unneeded parts of union all by itself. Have you looked at query plans? — Arvo
– Arvo, Commented Jan 2, 2017 at 10:54
A View is a shorthand for a (sometimes) complex SELECT. If you need a variable view, either define different views, one for each case or simply use the explicit SELECT(s). If your concern is performance, this would be the most efficient approach. — FDavidov
– FDavidov, Commented Jan 2, 2017 at 10:58

Lukas Eder · Accepted Answer · 2017-01-02 11:06:41Z

In order to hint the optimiser that it does not need to generate any rows (and those not do any computation) in a specific union subquery, you have to tell it that you don't actually need the info. In some cases, it would be sufficient to just not SELECT * (i.e. not select v1.column2 and v1.column3):

select t1.*, v1.column1 from tabl1 t1 join v1 on t1.column1 = v1.column1
select t1.*, v1.column2 from tabl1 t2 join v1 on t2.column2 = v1.column2

There may be edge cases where the optimiser still cannot prove that the calculation is unneeded, depending on your actual view. In those cases, it may help to have a constant column that clearly discriminates each subquery:

create view v1
as
select 'section1' AS discriminator
-- rest of section1
union all
select 'section2' AS discriminator
-- rest of section2
union all
select 'section3' AS discriminator
-- rest of section3

And now, use a constant in your query as well (not a bind variable) to select the discriminator:

select t1.*, v1.column1 
from tabl1 t1 join v1 on t1.column1 = v1.column1 
where discriminator = 'section1'

select t1.*, v1.column2 
from tabl1 t2 join v1 on t2.column2 = v1.column2
where discriminator = 'section2'

Of course, at this point, one may wonder whether you perhaps shouldn't create 3 distinct views and select from them directly, but I don't know your requirements...

Collectives™ on Stack Overflow

Generate view sql dynamically

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related