SMP and recursive queries

 

Recursive queries can benefit as much from symmetric multiprocessing (SMP) as do other queries on the system.

Recursive queries and parallelism however present some unique requirements. Because the initialization fullselect of a recursive query (the fullselect that seeds the initial values of the recursion), is likely to produce only a small fraction of the ultimate results that cycle through the recursion process, the query optimizer does not want each of the threads running in parallel to have a unique queue object that feeds only itself. This results in some threads having way too much work to do and others threads quickly depleting their work. The best way to do this is to have all the threads share the same queue allowing a thread to enqueue a new recursive key value just as a waiting thread is there to dequeue that request. A shared queue allow all threads to actively contribute to the overall depletion of the queue entries until no thread is able to contribute more results. Having multiple threads share the same queue however requires some management by the Query runtime so that threads do not prematurely end. Some buffering of the initial seed values might be necessary. Illustrated in the query below, where there are two fullselects that seed the recursion, a buffer is provide so that no thread hits a dequeue state and terminates before the query has seeded enough recursive values to get things going.

The following Visual Explain diagram illustrates the plan for the following query run with CHGQRYA DEGREE(*NBRTASKS 4). It illustrates that the results of the multiple initialization fullselects are buffered up and that multiple threads (illustrated by the multiple arrow lines) are acting on the enqueue and dequeue request nodes. As with all SMP queries, the multiple threads (in this case 4) are putting their results in to a Temporary List object which become the output for the main query.

cl:chgqrya degree(*nbrtasks 4);


WITH destinations (departure, arrival, connects, cost )AS
(
   SELECT f.departure, f.arrival, 0 , ticket 
			FROM flights f WHERE f.departure='Chicago' 
   UNION ALL
   SELECT t.departure, t.arrival, 0 , ticket 
			FROM trains t WHERE t.departure='Chicago' 
   UNION ALL 
   SELECT
      r.departure,b.arrival, r.connects+1 ,
      r.cost+b.ticket 
				FROM destinations r, flights b 
				WHERE r.arrival=b.departure 
   UNION ALL
    SELECT
      r.departure,b.arrival, r.connects+1 ,
      r.cost+b.ticket 
				FROM destinations r, trains b 
				WHERE r.arrival=b.departure) 

SELECT departure, arrival, connects,cost 
		FROM destinations;

 

Parent topic:

Recursive query optimization