INT: Exchange connections in ASA16 while running composite report

[Former Member]

We're doing some bench marking on a database upgraded from ASA10 to ASA16, and one composite report written in Power Builder that takes a little while to run in 10 (sometimes 20 minutes or so) is taking several hours in 16. The main connection spawns 8 child connections with the name "INT: Exchange", and it just hangs for hours (it does eventually finish).

It seems this is the database trying to run the multiple sub reports in parallel. But the performance is worse. I don't know if ASA10 was also running in parallel, but I don't see the INT: Exchange connections when running the report in 10.

Has anyone had a similar issue, and if so are there possible work arounds?

[Former Member]

I've since noticed that repeated running of the report gets faster. The first running took over two hours, the second less than ten minutes, and the third time and after it runs in only a minute or two.

[Breck_Carter]

The SQL Anywhere query optimizer builds the execution plan from scratch for each execution of a client SQL statement. If the environment changes (data in cache, statistics up-to-date, planets in conjunction, etc) the performance of a query can change dramatically... [flame suppression] usually not quite as dramatically as "hours down to minutes", but with intra-query parallelism all bets are off. [/flame suppression]

[Breck_Carter]

Short answer: SET OPTION PUBLIC.MAX_QUERY_TASKS = '1';

Medium answer: Read The New MaxBPs=768: Set MAX_QUERY_TASKS = '1'.

Long answer: Run Foxhound to see what is really happening...

( There's an even longer answer, but it might start a flame war with Engineering 🙂

[Former Member]

So I set max query tasks to 1, and the report ran for about an hour (I had stopped and restarted the service), and then threw an error, "Temporary space limit exceeded". And upon checking I see the temp file has filled the hard drive...

[Breck_Carter]

Wellll, it sounds like you have a real Query From Hell there 🙂

There have been many changes to the query optimizer between V10 and V16. In your case V10 may consistently choose a good plan for this query and V16 may not. This doesn't happen with many queries, but if it happens with 0.1% of queries that means almost everyone is going to experience it sooner or later... it happens with EVERY RDBMS product, not just SQL Anywhere.

One place to start might be to obtain the "Graphical Plan With Statistics" for V10 (fast) and V16 (slow).

Save the *.saplan files, and post them here or send them to breck dot carter at gmail dot com