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?

asked 01 Jul '16, 10:35

timoccci's gravatar image

timoccci
86179
accept rate: 0%

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.

(01 Jul '16, 11:49) timoccci
Replies hidden

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]

(01 Jul '16, 15:03) 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 :)

permanent link

answered 01 Jul '16, 14:41

Breck%20Carter's gravatar image

Breck Carter
25.7k428592852
accept rate: 20%

edited 01 Jul '16, 14:53

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...

(01 Jul '16, 16:44) timoccci
Replies hidden

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

(03 Jul '16, 11:21) Breck Carter
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:

×53

question asked: 01 Jul '16, 10:35

question was seen: 270 times

last updated: 03 Jul '16, 11:27