eXtremeSQL Parallel Query Execution

The keyword PARALLEL can be combined with any comparison operator. However, it will take effect only for:

    - IN  <array defined by literal or parameter >
			    

    - IS NOT NULL
					

    - between <num1> and <num2>
					

The number of worker threads is controlled by the xsql variable parallel_workers:

     
      SET parallel_workers=<N>
					 

 

(By default, there are 8 worker threads.)

This variable is local for the current session. Its value can be set only before the first query containing PARALLEL is executed. Subsequent assignments are ignored.

Parallel execution is implemented via the class SqlAggregator: several connections and threads are created and each connection is started in its own thread. The condition is split between the threads.

This feature may be handy for processing of large sequences since it allows making use of many CPU cores.

Example

Let's simulate a long-running operation with a delay:

     
    xsql -trace


    create table par(x int, y int);

    INSERT INTO par VALUES (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7), (8,8), (9,9);


    XSQL>select x, sleep(x*1000) from par where x in [1,2,3,4,5,6];

    ...

    Selected records: 6

    Elapsed time: 21001
					 

Total time is 21 seconds, i.e. sum of all sleep() times.

Now let's use parallel execution:

     
						
    XSQL>select x, sleep(x*1000) from par where x parallel in [1,2,3,4,5,6];

    ...

    Elapsed time: 6002

    Max allocated: 59334
					 

Total time is 6 seconds, i.e. time of the longest sleep().

More examples

     
    XSQL>select x, sleep(x*1000) from par where x parallel is not null;

    ...

    Elapsed time: 10001
					 

Here 9 computations were done with 8 worker threads. The first threads completed the first computation in 1 second and proceeded to the ninth computation. Total time is 10 seconds.

     
    XSQL>select x, sleep(x*1000) from par where x parallel between 1 and 6;

    ...

    Elapsed time: 6001

     

Total time is 6 seconds.