SQL Anywhere has long offered "inter-query parallelism" to allow the simultaneous execution of different requests, and has introduced "intra-query parallelism" in v10 to allow the parallel execution of parts of a single query.
But what about several statements within one code block or batch - may they be run simultaneously, too, or is each statement executed serially?
Say, I have a small code block like the following - would SQL Anywhere try to do the calculations in parallel (as they are based on different tables):
(Yes, when using the SQL Anywhere demo database, a parallel execution would certainly be not worthwhile here, but I hope you get the idea...)
What about code blocks with external functions - might they run in parallel? (AFAIK, the engine uses separate worker threads to handle them.)
Short WAG: "You're SOL, Earth Man!" :)
I think "inter-query parallelism" is the simultaneous execution of separate requests made by separate connections, not just "separate requests".
Presumably, "intra-batch parallelism" might be considered an extension of "intra-query parallelism" since by definition a batch (or block) is sent to and processed by the server as a single unit.
However, just-in-time optimization of each statement is a hallmark of SQL Anywhere. The challenge of "parallelizing sequential programs" has been a topic of advanced research among human beings for decades... throw in just-in-time optimization, and "parallelizing SQL Anywhere batches" becomes a topic for alien super-beings.
Back here on Earth, the docs have some interesting topics...
Parallelism in the SQL Anywhere server
"Normally, the optimizer selects an execution plan for a query every time the query is executed."
"Optimization is essential in generating a suitable access plan for a query. Once each query is parsed, the optimizer analyzes it and decides on an access plan that computes the result using as few resources as possible. Optimization begins just before execution. If you are using cursors in your application, optimization commences when the cursor is opened. Unlike many other commercial database systems, SQL Anywhere usually optimizes each statement just before executing it. Because SQL Anywhere performs just-in-time optimization of each statement, the optimizer has access to the values of host and stored procedure variables, which allows for better selectivity estimation analysis. In addition, just-in-time optimization allows the optimizer to adjust its choices based on the statistics saved after previous query executions."
Parallelism in client applications (probably off-topic, but interesting nonetheless)
"... if you use a single connection, you are restricted to one active request per connection. In a multithreaded application, you should not use the same connection to the database on each thread unless you use a semaphore to control access."
"Each thread must have its own SQLCA."
"All operations on a given database connection must use the same SQLCA that was used when the connection was established."
answered 11 Aug '15, 05:35
It may be a moot point, but the example you show might not benefit from "intra-batch parallelism" because it already exploits "intra-query-parallelism" to the maximum extent.
Here's the plan for one of your queries, using Foxhound's very own Table From Hell (a few days' worth of connection history for a target server with 700+ connections):
select count(*) from rroad_group_2_property_pivot; count() 4605632
Here's part of the plan (SUBQ 3) after a manual rewrite to encourage "more parallelism"... SQL Anywhere implements each of the subqueries separately, and each of them uses intra-query parallelism to the full extent:
SELECT (select count(*) from rroad_sample_set) + (select count(*) from rroad_group_1_property_pivot) + (select count(*) from rroad_group_2_property_pivot) AS nCount; nCount 4637562