I've noticed, updating from SA 220.127.116.1184 to 18.104.22.16827 or 22.214.171.12413, that the queries on some of our views are really slow.
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 126.96.36.19984:
Define a view which contains a sorted list:
Then query the view (incl the WHERE clause)
(please don't mind the columns, it's just an example).
I was expecting Sybase to first filter by "someID" (which it does, on 188.8.131.5284). 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.
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.
I have tried to reproduce this change in behaviour and I haven't been able to do so. I tested with the following query:
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
answered 17 Apr '14, 10:59
Ivan T. Bowman