3 ms·
This can be dangerous if over used. It can be very tempting to quickly pull in a 'left join blah' to grab some fields you may need. But that blah view could be
by weavie 9y ago
This can be dangerous if over used. It can be very tempting to quickly pull in a 'left join blah' to grab some fields you may need. But that blah view could be hiding some massive performance sapping beast.
When it comes to optimizing a slow running query, having to step through layer after layer of nested view can be incredibly challenging.
- gregmac 9y agoWorse, someone is using the view (that joins several tables) in a join to get one extra field, which would be a simple single join -- they just didn't bother to check where it came from because 95% of the time that view gets used, and no one questions it. I've seen it happen more than once.
- PeterisP 9y agoWhy would that be a problem? Unless you're fetching all the unneeded fields as well, in any decent DBMS both approaches would result in the exact same execution plan with the exact same performance, the unneeded parts/joins of the view wouldn't be executed.
- gfody 9y agoI 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.