Parallel execution of table functions in SQL with the JOIN_PARALLELIZATION hint

Wiki (en)
,
1

Go to Dutch version

Table functions

Table functions are used in many queries. This is usually the case with platforms that work with so-called ‘point queries’ and perform numerous I/Os such as:

select *  
from   customers ctr
join   httpdownload(ctr.website_url) htp

A table function is often called thousands or millions of times with large datasets. The execution always takes place sequentially:

  • Retrieve a handful of rows from the base table, such as customers above.
  • Execute the table function for the first row.
  • Wait until it is finished.
  • Execute the table function for the second row.
  • Wait until it is finished.
  • … etc.

Many table functions call an API or website, which can cause delays ranging from a few milliseconds to hundreds of milliseconds per call.

For example, processing the data behind https://zoek.officielebekendmakingen.nl requires 6 to 12 million calls in certain use cases.

Faster processing

With the new join_parallelization hint, the Invantive UniversalSQL engine can call table functions in parallel with multiple threads where possible:

  • Retrieve a handful of rows from the base table, such as customers above.
  • Start the execution for the first row with the table function.
  • Start the execution for the second row with the table function.
  • … etc.
  • Wait until one is finished and start a new one.
  • … etc.

In the most optimal situation, the processing duration shortens linearly: with parallelisation over n times as many threads, the total elapsed clock time is n times shorter.

If the number of threads is not specified, twice the number of processors will be used. The maximum number of active threads is always capped at 128 per query.

A linear speed increase will not always be achievable, but a significant acceleration is certainly possible for I/O-intensive operations.

A large speed gain is not to be expected for calculations that are already limited because they already make optimal use of the available computing power and other resources.

In any case, parallelisation is only recommended after trying a smarter approach through tuning.

The possibilities are illustrated using two I/O-intensive examples.

Teamleader

Data from the following query can be retrieved in parallel:

select count(*)
from   Teamleader.V2.Companies l
join   Teamleader.V2.CompanyById(l.id) p

The number of threads (in this example 8) can be set with the hint join_parallelization:

select /*+ join_parallelization(dl, true, 8) */count(*)
from   Teamleader.V2.Companies l
join   Teamleader.V2.CompanyById(l.id) p

If there are 100 companies registered in Teamleader Focus, then all calls from CompanyById will fit within the rate limiter of 200 API calls per minute. There will, of course, be other speed reducing factors such as a speed limit within the Teamleader Focus database itself or processing queues with limited capacity.

Measurements show that the throughput of initially 4 rows per second increases through parallelisation as follows:

#Threads Throughput (rows/sec)
1 4
2 6
4 9
8 13
16 16
32 17
64 18
128 19

The speed gain is significant up to approximately 8 to 16 threads, and a speed increase greater than approximately 5 does not seem feasible using this approach.

Binary download

The following query retrieves a 1 MB file 1000 times via an Internet connection with a maximum of 8 threads:

select /*+ join_parallelization(dl, true, 8) */ r.value rval 
from   range(1000, 1) r 
join   httpdownload
       ( url => 'https://ipv4.download.thinkbroadband.com/1MB.zip'
       , ignoreInvalidCertificate => true
       , ignoreWebError => true
       ) dl

The performance gain is highly dependent on the available bandwidth. With 8 threads, the query is completed 3x faster, and with 128 threads, it is 28x faster.

Availability

The join_parallelization hint is available from BETA release 24.1.122, which is expected to be available before 15 July 2025.