on 2012 Oct 14 11:16 AM
Hi
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
Request clarification before answering.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
That is incorrect. For example, if the view's column referenced in the main query block predicate is a simple base table column, then the predicate will be pushed all the way to the table scans, in all the UNION ALL query blocks. However, if the view's column is a complex expression, even if the predicate is pushed, the pushed predicate will be evaluated at the root of the view's query blocks as the predicate cannot be pushed at the scan. In your example, you can check that the performance of rewriting your query with the predicate added in all UNION ALL blocks is not improved comparing with the performance of the original query. The original design of the pushed down algorithm is to provide extra sargable predicates in the view's query blocks.
The actual view has about 20 columns where some of them are calculated. The test table I use has about 43'000 records, where about 500 match the status = 'open'. When I set the where condition to the view it takes minutes to get all the data. When I set the where condition each union it's way faster because it applies the filter to the rows before calculating all the 43'000 values of the other columns.
Looks like I have to change something in design.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Just a simple part from the view with the column used to filter Query:
select * from ctview where status = 'open'
Pushed down:
CREATE VIEW "org"."ctview" as select (CASE WHEN erledigt IS NOT NULL THEN 'done' WHEN dateformat(faellig,'yyyymmdd') < dateformat(getdate(),'yyyymmdd') THEN 'overdue' ELSE 'open' END) as status from org.pendenz where externerfallid IS NULL
Not pushed Down (only a union with the same select) Filter is applied to union:
CREATE VIEW "org"."ctview" as select (CASE WHEN erledigt IS NOT NULL THEN 'done' WHEN dateformat(faellig,'yyyymmdd') < dateformat(getdate(),'yyyymmdd') THEN 'overdue' ELSE 'open' END) as status from org.pendenz where externerfallid IS NULL union all select (CASE WHEN erledigt IS NOT NULL THEN 'done' WHEN dateformat(faellig,'yyyymmdd') < dateformat(getdate(),'yyyymmdd') THEN 'overdue' ELSE 'open' END) as status from org.pendenz where externerfallid IS NULL
User | Count |
---|---|
50 | |
10 | |
8 | |
6 | |
5 | |
5 | |
5 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.