Re:Literate SQL
by gfody on Sunday June 25, 2017 @05:14PM
(#14632224)
I can't speak for other engines but at least SQL Server and Oracle will eliminate unnecessary joins for inlined table-valued functions, e.g.:
create function thing (a, b) returns table as
select * from table_1 left join table_2 on ...
select (only cols from table 1) from thing(a, b)
..and table_2 is not accessed
predicates from the topmost query will be pushed down to the function's query as well so the functions performance is generally equivalent to the adhoc version.
on the whole I find this strategy to be very effective at reducing the complexity of adhoc queries without performance penalties. in the case of indexed views it can greatly improve performance.
create function thing (a, b) returns table as select * from table_1 left join table_2 on ...
select (only cols from table 1) from thing(a, b)
..and table_2 is not accessed
predicates from the topmost query will be pushed down to the function's query as well so the functions performance is generally equivalent to the adhoc version.
on the whole I find this strategy to be very effective at reducing the complexity of adhoc queries without performance penalties. in the case of indexed views it can greatly improve performance.