The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

Hi,

I've noticed, updating from SA 11.0.1.2584 to 11.0.1.3027 or 11.0.1.3113, that the queries on some of our views are really slow.

Update

The issues seems to e caused by sorted lists. So here is a simple scenario which - at least in our case, offers very poor performance compared to 11.0.1.2584:

Define a view which contains a sorted list:

CREATE VIEW VIEW_01 AS
 SELECT T1.PRODUCT_ID,
  LIST(T1.IND_AREA ORDER BY T1.IND_AREA) AS LIST_AREA
 FROM TABLE_1 T1
 GROUP BY T1.PRODUCT_ID

Then query the view (incl the WHERE clause)

SELECT V1.PRODUCT_ID, V1.LIST_AREA
FROM VIEW_01 V1
WHERE V1.PRODUCT_ID = 'someID'

(please don't mind the columns, it's just an example).

I was expecting Sybase to first filter by "someID" (which it does, on 11.0.1.2584). Since the update, though, for some reason we have yet to find or understand, this doesn't happen.

The query time grew from a couple of seconds to over 12 minutes.

Initial questions

For example, given the following hierarchy: VIEW_2 <- VIEW_1 <- TABLE_1, with TABLE_1 containing (product_id, valid_from, valid_to). A query on TABLE_1 with a filter on the product_id and validity, runs as expected (that is, fast). Same for a query with an identical where clause on VIEW_1. A third identical query on VIEW_2, however, first builds VIEW_1 completely and only applies the filter afterwards. This leads to significant performance issues.

I'll add that proper indexes are used and that the statistics are up to date.

Is there a change introduced by the updates that I'm not aware of and which is not highlighted in the release notes/comes from an older updated, related to how query plans work on views/predicate push down etc work?

We've also encountered another oddity - if TABLE_1 calls "list" for some of its columns, similar performance issues arise.

Regards, GM

asked 11 Apr '14, 10:38

George%20M's gravatar image

George M
36116
accept rate: 0%

edited 14 Apr '14, 08:46

Please post a "graphical plan with statistics" for a slow query. If this forum's software won't let you post the *.saplan file, please email it to me breck dot carter at gmail dot com.

See How To Capture A Graphical Plan

For more information about graphical plans, see this google search.

(11 Apr '14, 12:08) Breck Carter

Hi, thank you for feedback.

I'm not sure if I can attach files - I'll check the forum options again, I might have missed something.

(14 Apr '14, 02:27) George M
Replies hidden

Also tested with SA 16, with identical performance issues.

(14 Apr '14, 08:49) George M

> please email it to me breck dot carter at gmail dot com.

(14 Apr '14, 11:03) Breck Carter

I have tried to reproduce this change in behaviour and I haven't been able to do so. I tested with the following query:

create view V as 
select manager_id, list(emp_fname order by emp_id) L
from employee
group by manager_id;

select *
from V
where V.manager_id=501;

With my testing, I see consistently (10.0.1(3415), 11.0.1(2044), 11.0.1(2584), 11.0.1(2878)) that the predicate V.manager_id=501 is evaluated after the GROUP BY if there is an ORDER BY in the LIST aggregate. Without the ORDER BY, the predicate is evaluated before the GROUP BY. This is a known restriciton of our predicate pushdown semantic transformations. In discussions with colleagues we have not been able to identify what would have ever allowed that predicate pushdown correctly, but we surmised that perhaps there was a software defect that unintentionally allowed the pushdown. While it is safe in this case, the logic to ensure it is safe is not part of the current servers and the server should not be doing the predicate pushdown here. This is an enhancement that we are considering. The limitation currently affects any aggregate with an ORDER BY (array_agg, list, xmlagg) and any window function.

permanent link

answered 17 Apr '14, 10:59

Ivan%20T.%20Bowman's gravatar image

Ivan T. Bowman
2.8k22732
accept rate: 39%

Thank you, I appreciate your answer - that is basically what I wanted to know.

(22 Apr '14, 02:09) George M

As a note: our view represents a union. I did some more testing, and this causes causes the predicate pushdown in 11.0.1.2485.

(22 Apr '14, 04:23) George M
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:

×238
×24
×22

question asked: 11 Apr '14, 10:38

question was seen: 513 times

last updated: 22 Apr '14, 04:23