cancel
Showing results for 
Search instead for 
Did you mean: 

Query Parallelism and Multiple Volumes

Former Member
0 Kudos

This question concerns a clarification regarding query parallelism on simple queries and multiple volumes in MaxDB. The documentation references query parallelism of some form, but I cannot seem to find an in-depth discussion in the documentation to clarify what form is used. Your help would be greatly appreciated.

Table1 has 5,000 records and Table2 has 100,000,000 records with indexes on the field ID in both tables.

Given that a database is spread over multiple volumes (multiple hard drives) and given the following:

Select SomeField from Table1, Table2 Where Table1.ID = Table2.ID

If MaxDB is given the above SQL query, will the database access all of the separate volumes in parallel or in series to look up the indexed values on Table2 and resolve the join?

-Frank

Accepted Solutions (1)

Accepted Solutions (1)

lbreddemann
Active Contributor
0 Kudos

Hi Frank,

as usual in case of such rather wide range question the correct answer is: it depends.

It depends with version of SAP DB/MaxDB you're using.

It also depends if a HASH JOIN is employed for this join (depends on data volume and db parameter setup - OK for this join it's rather unlikely).

It's furthermore dependent on the configuration of parallel join feature and the availability of server tasks for this. As only one session at a time can use parallel join the very same statement may be handled in parallel one time and "serial" the other time.

So in the best case (no other query running parallel join) the join will be handled in parallel.

In the worst case, the query (logical I/O) will be done serial but nevertheless: we're still doing asynchronous I/O over striped datavolumes. So whenever a page is not in the cache the session won't block to wait until the I/O is done.

A deeper insight can be gained by attending the MaxDB Internals training offered by SAP. In that training many topics are covered in-depth, including the effects of database parameters and how (some) statements are processed.

Hope that helps a bit,

Lars

Answers (0)