Upgrade from SQL 9 to 16, big drop in VIEW performance

[vlad1]

Hi,

We are testing the upgrade of our client's SQL Anywhere from the very old database engine 9.0.2 to SQL Anywhere 16, build 2234. We created the database in version 16, did unload of the complete old database and import into new one, and everything seems 100% correct, the time of response for most of the actions is improved, as expected.

What was unexpected is that several reports (DB queries) that rely on one big view are very drastically slower. These used to run between 10 and 15 seconds in the "old" database running on version 9, and now the same queries on the same data converted to SQL Anywhere 16 run near 5 minutes.

Now, I'm not going to lie - this is a very big and complex view, consisting of 5 UNION ALLs, and 3 of those UNIONS are getting data from very large tables (800000, 150000 and 5000 rows respectively).

I've narrowed it down to the first UNION section of the Combined View (which is SELECT from largest table of 800000 rows). Basically:

1. If I run the non-view Select query that is the first part of the combined view, the results are almost instant, 10 seconds or so

2. If I run the Select from the Combined View, but I limit it by a flag field to only get the data from the first table (first section of the view), just as in (1), the query takes over 5 minutes.

3. If I run the Select from the Combined View, but I limit it by a flag to get the data from the TABLES OTHER THAN FIRST ONE (big one), results are very quick

Of course, I don't expect people to try to debug our SQL here (if necessary, I will re-code it), but this behaviour strikes me as fairly unexpected - you would think that after the leap of 4 versions of SQL Anywhere, time of response would be quicker, not 20 times slower. So basically, this is a bit of hail-mary question - is there something that I should know about how SQL Anywhere 16 handles VIews with Unions, like some database parameters that can fine-tune the behaviour of SQL Anywhere when it comes to this?

(yes, I realise it sounds a bit far-fetched, but I'm just surprised at such a drastic drop in performance just for one view)

Thanks

[Former Member · Accepted solution]

Hi Vlad,

Comparing your 3 test cases with graphic plan could tell you where the differences come from each cases - if there's any. Make sure you run them on warm cache (i.e. run it couple of times).

DCX reference: DocCommentXchange

Jinwoo

[vlad1 · Accepted solution]

Thanks Jinwoo. Never really had to use one of these before, I tried it and I believe I found one of the unions (surprisingly, not the one with the largest table) is having the largest number of records, just not too sure what to do about it yet. I'm looking through the joins (nothing terribly complex, 2 left outer joins, 2 inner joins), nothing too obvious to cause a problem. I'll keep looking.

I'm just so amazed to find such a big difference between a very old version and a recent version.