Question Re: Parallel Procedure Calls in Technology Q&A

Parallel Procedure Calls

matma24 — Fri, 21 Aug 2020 21:27:06 GMT

Hello,

I'm having a procedure that takes really long time to run (over 45min).

Very simply speaking procedure reads some data from table, do calculation and insert the data into different one.

I'd like to run introduce an IN parameter that will divide it to X calls that gonna run in parallel, example : now im running 100k rows at once but I'd like to divide it to 4calls and run 25k on each call.

I'm able to run that and it works a lot faster when I'm not doing any inserts.

According to https://help.sap.com/viewer/de2486ee947e43e684d39702027f8a94/2.0.02/en-US/8db200a4f585490c81c4930689ec1a5c.html table variable are supported and DML are allowed in read-write procedures, which can be called within a parallel block so I've created one in the procedure but it does give an error "having DML statement on table variable in PARALLEL EXECUTION block" when I'm trying to do the insert "INSERT INTO :tempTable VALUES(1,2,3);".

Unfortunately DynamicSQL is also not available for parallel run, does anyone have an idea how can I do it?

Would be very grateful for any help or insights where I could look for a solution.

I'm running on SAP HANA 2.0 SPS05.

Very simple example that returns an error "not permitted statement: Calling a procedure TEST_PROC; having DML statement on table variable in PARALLEL EXECUTION block: line 50 col 2 (at pos 254) is not allowed inside parallel execution block: line 3 col 3 (at pos 37)":

CREATE PROCEDURE "TEST_PROC"(
	OUT someReturn TABLE(
		idRet INT, 
		someText VARCHAR(100)
	)
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
	DECLARE j INT;
	DECLARE tempReturnTab TABLE(
		idRet INT, 
		someText VARCHAR(100)
	);
	FOR j in 1..20 DO
		INSERT INTO :tempReturnTab VALUES (1, 'test');


	END FOR;
	someReturn = SELECT * FROM :tempReturnTab;
END;

DO BEGIN
	begin parallel execution
		CALL "TEST_PROC"(
			someReturn => ?
		);
		CALL "TEST_PROC"(
			someReturn => ?
		);
		CALL "TEST_PROC"(
			someReturn => ?
		);
	end;
END;

Re: Parallel Procedure Calls

lbreddemann — Sat, 22 Aug 2020 07:23:54 GMT

The first questions to ask here are of course:

how do you know which part of the procedure takes up most of the time? By removing the "writing the result into tables" step you took out a huge sub-part of your program, not just a simple processing step.
why do you believe that running more but smaller packets of the data processing in parallel will result in a better total runtime?

In my experience, runtimes like 45mins on HANA are very often related to either

locking
or
unloading/re-loading of data
or
massive materialisation of intermediate resultsets

or all of those combined.

Without thoroughly analysing where the time is spent (depending on the amount of data involved, things like automatic delta merges, compression optimisation of the target table etc. comes to mind) optimising this will be just guesswork.

To answer one aspect of your question more concretely, writing to the same data structure is not supported by parallel procedure calls. But you could have all of the procedures write into their own (maybe temporary) target table and transfer the results once all parallel calls have returned.

Another thing to consider is that there is the MAP_REDUCE functionality that is specifically made for "split-up-then-join-the-results" types of workloads.

Re: Parallel Procedure Calls

matma24 — Sat, 22 Aug 2020 08:26:35 GMT

Hello, thank you very much for your reply, I'm running a fuzzy search in range between 20-60k times on the table with over 200m records. There are only 20-60k Inserts, the part that is taking so long time is all of those Fuzzy Searches thats why I'd like to run them in batches (for example run procedure 20 times 1k record each).

I did also tried to create "create local temporary table #MyTempTable" too which I'd like to insert records and then assign the result of it to the OUT table but that is still not working cause of DML errors.

Re: Parallel Procedure Calls

lbreddemann — Sat, 22 Aug 2020 08:58:56 GMT

So, you're saying that you only get ~60.000/(45*60) = 22.22 fuzzy searches per second done on this table?

Why is that? Are there full-text indexes present on the searched columns?

And is the table partitioned?

For any possibly practical advises on how to speed this up, I would need to see the procedure and the long running statements.

Re: Parallel Procedure Calls

matma24 — Sat, 22 Aug 2020 09:20:15 GMT

I'm doing searching on three columns, two of them do have FUZZY INDEXES ON but one not (and I'm not able to change that). After search is done I do enrich the data by reading some additional stuff and putting in into result table. The problem is - even after I delete those Insert statements the performance is pretty much the same (when I'm running it all at once), but when I delete those statements and do "begin parallel execution" then everything is working way better - the problem is only that I do not know how to be able to do it and save the result to any temp table/ table because every time I'm trying to do that compilator says I's not possible to run DML during parallel execution, which is weird because documentation says

"Only the following statements are allowed in read-write procedures, which can be called within a parallel block: DML" and "implicit parallelization has been applied to table variable"

Re: Parallel Procedure Calls

lbreddemann — Sat, 22 Aug 2020 09:41:08 GMT

That's all very good, but performance analysis is about specifics. That's why without knowing the code and the data (and probably details about the system) there's no "analysis" possible here.

Maybe this is a case where the forum cannot help much and you need somebody me working on your system. But from what we know up to here, there's nothing to go for.

Re: Parallel Procedure Calls

matma24 — Sat, 22 Aug 2020 09:53:57 GMT

What I'd like to ask is, you said: "But you could have all of the procedures write into their own (maybe temporary) target table and transfer the results once all parallel calls have returned." Could you please show how it can be done? The second Im trying to create any table(even local temp table) in the procedure or insert data to it I'm getting the information that parallel calls are not available for procedures with DML statements.

Edit:

I did used MAP_REDUCE as proposed which let me go from over 40min to less than 2mins.