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

asked 14 Oct '12, 10:16

Arthur%20Hefti's gravatar image

Arthur Hefti
1668816
accept rate: 0%

Can't tell for that particular situation,but in general predicates on views are "pushed down" - cf. this FAQ.

As usual, you may show the two queries and their according plans here.

(14 Oct '12, 16:04) Volker Barth

I did some more research:

  • when the view consists of a single select the where condition is pushed down.

  • when the view consists of a union the where condition is pushed down only when the column is not calculated in the select. When using a computed column on the table instead of calculating the value in the view the where condition is pushed down

  • when the view consists of one or more unions the where condition is not pushed down to any select if the column used for the where is calculated in at least one select

(15 Oct '12, 00:41) Arthur Hefti

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

permanent link

answered 15 Oct '12, 10:00

Nica%20_SAP's gravatar image

Nica _SAP
866722
accept rate: 3%

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
(15 Oct '12, 10:32) Arthur Hefti
Replies hidden

So your view does use the same SELECT block twice in the UNION?

FWIW, I could imagine that comparing datetime values as date values is more efficient when simply casting to DATE, such as

WHEN CAST(faellig AS DATE) < CURRENT DATE
(15 Oct '12, 11:21) Volker Barth

This is not the original view but just a simple sample. I used the same select twice to make it simpler to show the problem. Thanks for the hint about casting a date but I assume this wont be the cause of the problem.

(15 Oct '12, 14:23) Arthur Hefti

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.

permanent link

answered 15 Oct '12, 18:59

Nica%20_SAP's gravatar image

Nica _SAP
866722
accept rate: 3%

That means that using views (not materialized) with union all and columns computed on the fly is something SQL Anywhere can't handle and should not be used...

(16 Oct '12, 00:53) Arthur Hefti

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.

(16 Oct '12, 10:59) Nica _SAP

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.

(16 Oct '12, 11:34) Arthur Hefti
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×31
×10

question asked: 14 Oct '12, 10:16

question was seen: 3,209 times

last updated: 16 Oct '12, 11:34