PARALLEL EXECUTION not parallel

[Former Member]

Hi folks,

It looks as though PARALLEL EXECUTION modifier isn't playing along nicely. I have a scenario, that as far as I understand - meets the requirements for including this modifier in a procedure signature (and/or against nested BEGIN/END blocks). Reference is the SQLScript guide's description (and limitations) of PARALLEL EXECUTION modifier syntax.

As noted below, the PlanViz shows sequential execution.

I'm hoping rich.heilman can comment - and/or perhaps lars.breddemann has some insight. 🙂

Requisite system details, sample code, screenshots, etc - below.

/*
Drop tables
*/
DROP TABLE       BIG_TABLE;
DROP TABLE   STG_BIG_TABLE;
DROP TABLE     SMALL_TABLE;
DROP TABLE STG_SMALL_TABLE;
/*
Create 4 tables. 
One big, 
one small, and 
staging tables for each.
*/
CREATE COLUMN TABLE        BIG_TABLE(FIELD INTEGER, PRIMARY KEY(FIELD)); -- to demonstrate error handling issues later
CREATE COLUMN TABLE    STG_BIG_TABLE(FIELD INTEGER, PRIMARY KEY(FIELD)); -- to verify load time
CREATE COLUMN TABLE      SMALL_TABLE(FIELD INTEGER); 
CREATE COLUMN TABLE  STG_SMALL_TABLE(FIELD INTEGER);
/*
Stage data in the staging tables. Not worried about manual delta merge at this point.
4m records in STG_BIG_TABLE.    -- instant
40 records in STG_SMALL_TABLE.  -- takes ~ 10 seconds on a small (HXE) system
*/ 
INSERT INTO STG_BIG_TABLE SELECT ELEMENT_NUMBER FROM SERIES_GENERATE_INTEGER(1, 1, 5000001);
INSERT INTO STG_SMALL_TABLE SELECT ELEMENT_NUMBER FROM SERIES_GENERATE_INTEGER(1, 1, 51);
-- Simple parallel load procedure
DROP PROCEDURE LOAD_STG_TO_TARGET_PARALLEL;
CREATE PROCEDURE LOAD_STG_TO_TARGET_PARALLEL
AS
BEGIN PARALLEL EXECUTION
INSERT INTO SMALL_TABLE SELECT * FROM STG_SMALL_TABLE;
INSERT INTO BIG_TABLE SELECT * FROM STG_BIG_TABLE;
END;
-- PlanViz shows SEQUENTIAL, not PARALLEL EXECUTION
CALL LOAD_STG_TO_TARGET_PARALLEL;

Sequential execution below...

System details here below...

[lbreddemann · Accepted solution]

Yeah, ok, that's a counter-intuitive display here.

Looking at the prepared PlanViz provides a better picture:

And looking into the timeline of the executed statement it becomes clear as well:

Finally, you could compare the start and end times for the INSERT operator nodes (in the nodes details).

For the reproduced scenario on my HANA 2 NUC system that showed:

INSERT (small_table): Execution Start Time: 16.969 ms / End Time: 23.639 ms

INSERT (big_table): Execution Start Time: 17.105 ms /End Time: 4,989.804 ms

So, indeed, both INSERT statements ran in parallel.

Happy? 🙂