We have a SQLAnywhere10 database running on a Windows 2003 server which has
4 processors and 4GB RAM.

There is a complex stored procedure on the database that is used to return
data for a report.

We have found that this stored procedure is running very slowly, taking
something like 120 seconds to return the data for a day's report.

The odd thing is that the same database with the same data (i.e. an exact
copy) running on a db server with the same parameters on a much less
powerful server box with only one processor (same OS) will bring back the
same report in 8 seconds.

Investigations have shown that it is nothing to do with memory, cache or
disk access. Nor is the 4 processor server overloaded or less powerful in
general. On straightforward queries the 4 processor server is much faster
than the 1 processor server. Only this particular stored procedure seems to
be affected.

We have found that restricting the access of the db server to just one
processor (-gt 1 parameter) does increase the performance of the 4 processor
machine dramatically for this SP though it does not bring it up to that of
the inferior 1 procesor machine.

The stored procedure has a main cursor that brings back a list of entity
keys in a loop that calls a series of queries to get various bits of
aggregate data for each entity. It stores the data in a temporary table and
then returns the contents of this table as the report data after the cursor
loop completes.

We know there is nothing fundamentally wrong with the SQL as it runs nice
and fast on the single processor machine, but there must be something about
it that is not liked by a multi-processor machine.

My question is, has anyone ever seen anything like this before and does
anyone have any ideas about what may be causing the problem?

Hi Aston,

Which exact version and build of 10 is this? (dbsrv10 -v)?

If this is easily reproduced, it would be best to capture graphical
plans with statistics for this SP on both servers (
http://dcx.sybase.com/index.php#html...-queryopt.html
).

After posting the query plans with statistics, we will have a better
idea as to why the query is running so differently in these two conditions.

Regards,

Aston Cockayne wrote:
--
Jeff Albion, Sybase iAnywhere

iAnywhere Developer Community :
http://www.sybase.com/developer/libr...ere-techcorner
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
SQL Anywhere Patches and EBFs :
http://downloads.sybase.com/swd/summ...&timeframe =0
Report a Bug/Open a Case : http://case-express.sybase.com/cx/

Jeff

Its 10.0.1 build 3559.

I haven't been able to produce an execution plan as the tools say it is not
possible for this stored procedure. It does not appear to be the individual
queries in the SP that are the problem but the repeated execution of many
queries within a cursor loop that the procedue demands. It looks as if
somehow, when this work is shared among multiple processors it becomes very
inefficient. I suspect that a detailed knowledge of how SQLAnywhere shares
work between multiple processors will be needed to understand this.

However, I will try to do more work in analysing the individual queries
within the SP in case this throws up some useful information.

Aston

"Jeff Albion [Sybase iAnywhere]" <firstname.lastname (AT) ianywhere (DOT) com> wrote in
message news:4b195b7b$1 (AT) forums-1-dub (DOT) ..

If you utilize the Application Profiling feature of Sybase Central,
you'll be able to capture graphical plans of all of the queries/update
statements within the stored procedure and then analyze them afterwards.
This permits you to analyze the performance of the procedure without
breaking up the statements within the procedure and execute them manually.

The following whitepaper may be of help:

http://iablog.sybase.com/paulley/wp-...08/07/perf.pdf

Glenn

Aston Cockayne wrote:
--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport

That's good advice, thanks.

We have done that and it is possible to see which are the problem queries.
Analysing those queries shows that they use different plans on the
multi-processor machine. They use better plans when the multi-processor
machine is restricted to 1 processor (max_query_tasks) but never the best
plans used by the single processor machine. It is possible, in some cases,
to add clauses to the queries that do make the best plan be used by the
multi-procesor machine when restricted to one processor.

I'm wondering if there is any methodical way to make code that works well on
a single procesor machine also work well on a multi-procesor machine or do
we just have to do all our performance testing on a multi processor machine
to be sure...

I'm not sure, but I think the essence of the problem is something to do with
the fact that the queries are called repeatedly in a loop. The plan info
for the individual queries seems to suggest that the multi-processor plan is
faster to execute once but profiling shows that it is in fact about 40-50
times slower as an aggregate when the query is called repeatedly. That is,
when the query is executed 200 times in a row, the total execution time with
the plan favoured by the multi-processor machine is something like 25000 ms
whereas with the plan favoured by the single processor machine it is
something like 500 ms.

"Glenn Paulley [Sybase iAnywhere]" <paulley (AT) ianywhere (DOT) com> wrote

(ASA 11.0.1 don't remember the build). moved the db to an
older single processor (2000? server) and it ran circles
around the 4proc server. also had a client with a 4 proc
server (2008 64 bit), 16 gig of ram, asa 11.01.1 that would
CRASH dbsrv11 for no reason. it just stopped. installing
ebf 2331 (64 bit) cured it.

That's interesting too. So upgrade to Sybase 11 is not likely to help.

We now have a solution. Or workaround anyway.
Diagnosed the probem down to some very simple queries which explain plans
showed that the optimiser was dividing between 4 processors and then using
working table joins.

The result was the queries taking far longer than they should and, as they
were part of a loop, racking up a large aggregate time. Not only did this
extra processing make the query slow, it killed the whole server as all CPUs
maxed out.

We have had to limit the database to one processor (max_query_tasks = 1)
which makes it much faster and additionally add OPTION (optimization_level =
0) to some queries so they do not use working table joins. It's now
beautifully fast - just as it always has been on single processor machines.

OK, it may be that the database structure and the distribution of data in it
is partly to blame here and our only option is optimiser tweaks of this
kind, but I still would like to know why the optimiser makes such different
interpretations of the same DB statistics on the multi-procesor machine.
You can see that on the 4 processor machine it vastly overestimates the cost
but on the 1 processor machine it gets it about right.

If anyone has any ideas I'd still be interested to know.

<Tom Mangano> wrote

You should open a support case for this. Then the developers at Sybase will
have a look into the details of the plan and the optimizer estimates.
If there is a deficiency in the optimizer it would be fixed this way.


"Aston Cockayne" <aston.cockayne (AT) logica (DOT) com> schrieb im Newsbeitrag
news:4b22882f$1 (AT) forums-1-dub (DOT) ..

An update in case anyone is interested.

We have had a call open with Sybase on this issue. I think we now know the
cause:

There seems to be a bug in version 10.0.1.3559 which was not in earlier
versions and is not in later versions either. We were thrown off the scent
a bit beause the problem was much worse on one server running 3559 than it
was on another - using the same data set. Also, the bug is intermittent and
only appears for certain data sets.

But we have managed to demonstrate now that upgrading from 3559 to a later
version makes a big difference on a one-processor server for a problem data
set. We are yet to prove it has the same effect on a 4-processor machine
but it looks likely.

So if anyone is still running 3559 out there (it's pretty old now I know),
think about upgrading.

"Aston Cockayne" <aston.cockayne (AT) logica (DOT) com> wrote