SELECT performance degradation ...

[former_SQLA_member1694894]

Hi all. I'm using SA12.0.1 on Linux. A module of our application execute scheduled and parallel (java multithread) queries (only SELECT statements ...) We've experience of progressive performance degradation passing from 5 parallel executions (of the same query) to 10 executions ... Some annotations: 1) Executing a single instance of the query al works fine ... 2) We've noticed performance degradation using a pool of different queries, too ... How can we tune the system (Sa12 and/or OS ...) to obtain best performance ? Thanks.

[Breck_Carter]

Start by reading all about "performance" in the SQL Anywhere 12 documentation.

[former_SQLA_member1694894]

Hi Breck and thank you for replay. We've already tried all performance "tips" explaned in the documentation: 1) page size setting 2) multiprogramming level setting 3) cache size tuning 4) hardware escalation 5) ... ... without success .. Thanks.

[Breck_Carter]

Have you spent any time looking for performance bottlenecks, or have you concentrated on fiddling with switches?

If the latter, try setting everything back to the defaults and studying the performance statistics that SQL Anywhere makes available on a real-time basis.

There are a number of tools you can use for this: SQL Anywhere Monitor, Sybase Central Performance Monitor, DBConsole Utility, Windows Performance Monitor and Foxhound 3; you can see a comparison here. Foxhound 3 requires SQL Anywhere 16 to run, but it can handle target databases running on Version 12.

[former_SQLA_member1694894]

Hi Breck. I'm not working on potential bottlenecks because the query(ies), executed in a single "session", works fine ... while the same query(ies), executed in a multiple session environment (a tipical procuction scenario) become progressively slower. It seems to be a "stress" problem ... as if the (db)server was struggling to respond to multiple simultaneous requests.

[Vlad]

Can you please tell us the server configuration?
The concurrency of your requests?
What does SA show in metrics: I/O consumption, CPU consumption, memory consumption, disk reads, cache hits... something like that.
If you explain the plan when the server is idle and under the load of the same query. Do you see any difference in costs?

[Breck_Carter]

"I'm not working on potential bottlenecks"

Say what? Bottlenecks can (and often do) happen outside an individual query... but you know what you're doing, so I'm sure you'll be successful!

[former_SQLA_member1694894]

You are (obviously ...) right, Breck. We are investigating about a couple of queries to decrease their executing time (... and relative CPU resources consuption when used concurrently ...). Thanks.