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? 🙂

[Former Member]

lars.breddemann, your detailed and helpful responses always make me happy. 😉

I actually rarely check out the prepared statement tab - I didn't in this case - my fault. So thanks for illustrating its usefulness - as well as the start/end times. Great suggestion.

Also, in the timeline, I did look at that on my system, and it seemed to me the second [Statement] was executed as a child of the first (which I think is indeed the case?). But the first [Insert] is also a child of the first [Statement], thus technically a sibling of the second [Insert]... indicating that they are not dependent on each other in any fashion - and thus, ran parallel. That's my take, but I'll give it some more thought...

[lbreddemann]

To be frank, this was the first time for me too, where the prepared plan actually delivered the additional insight in a more suitable way than the executed plan.

Where this is probably more interesting is when e.g.no parallel DML is performed, because the scheduler couldn't find free tasks at that point in time. Then the prepared plan can show, that the plan generation did not block this feature.