Question Re: SELECT performance degradation ... in Technology Q&A

SELECT performance degradation ...

former_SQLA_member1694894 — Mon, 01 Aug 2016 05:24:20 GMT

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.

Re: SELECT performance degradation ...

Breck_Carter — Mon, 01 Aug 2016 12:33:28 GMT

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

Re: SELECT performance degradation ...

former_SQLA_member1694894 — Mon, 01 Aug 2016 12:43:24 GMT

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.

Re: SELECT performance degradation ...

Breck_Carter — Mon, 01 Aug 2016 15:18:13 GMT

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.

Re: SELECT performance degradation ...

former_SQLA_member1694894 — Tue, 02 Aug 2016 04:18:29 GMT

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.

Re: SELECT performance degradation ...

Vlad — Tue, 02 Aug 2016 04:53:51 GMT

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?

Re: SELECT performance degradation ...

Breck_Carter — Tue, 02 Aug 2016 07:35:23 GMT

"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!

Re: SELECT performance degradation ...

former_SQLA_member1694894 — Fri, 05 Aug 2016 05:07:35 GMT

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.