I have a view that joins 2 tables (same result with more than to). Once column is called status (which on at least one table is a computed column). When I do a select with "status = 1" on the view it looks like the view generates the whole result set and applies the where condition to the result set (which is quite slow). When I change the SQL of the view and set "status = 1" in the view itself the performance is obviously much better. Is there a way to push the where condition to the view columns.
Using SQL Anywhere 10.0.1.4213.
Any Suggestions? Arthur
asked 14 Oct '12, 10:16
Please post the query and the view definition if at all possible. The local predicates (i.e., predicates used in the query which refer only to view columns, e.g., "V.X+1 = 20+V.Y") on a view are pushed down into the view's query blocks if it is semantically correct to do that. For example, if the view most outer query block has a TOP N/ ORDER BY clause, the push down would be incorrect.
Please post/email your query/view.
Thanks A. Nica
answered 15 Oct '12, 10:00
In the view without the UNION ALL, the predicate is not pushed down: because the view is flattened into the main query block, the predicate "status= 'open'" is now in the unique WHERE clause and it maybe applied directly on the table scan.
In these examples, the view's column doesn't qualify to have its predicates pushed down. In general, the pushdown algorithm will try to pushdown predicates which are sargable after pushdown, in the view's query blocks. Theoretically, many other predicates could be useful to pushdown, for example, the ones which can be evaluated before joins in the view's query blocks.
answered 15 Oct '12, 18:59