Execute in parallel inside Stored Procedure

Database
Fan

Execute in parallel inside Stored Procedure

Hello everyone!

I have searched for this and didn't come across a solution yet.

 

I am trying to create an SP that will execute some of its code as parallel queries and other parts as sequential queries.

Let's say I have the following queries:

 

 

CREATE TABLE first_table AS (SELECT id, field1 FROM some_table);

CREATE TABLE second_table AS (SELECT id, field2 FROM other_table);

CREATE TABLE third_table AS (SELECT a.id, a.field1, b.field2 FROM some_table a inner join other_table b on a.id = b.id);

CREATE TABLE fourth_table AS (SELECT * from third_table);

 

I would like my SP to execute the first and second statement in parallel (like pressing F9 in Teradata SQL Assistant) and when those are done, to continue executing in a serial manner. I hope I have explained the issue clearly.

 

I'm really hoping there is a way to do this.

 

Thanks!

 

Regards,

Nahuel

6 REPLIES
Junior Contributor

Re: Execute in parallel inside Stored Procedure

When you show me how you run two CREATEs in SQL Assistant using F9 suceesfully, I will show you to do the same in an SP :-)

Highlighted
Senior Apprentice

Re: Execute in parallel inside Stored Procedure

Hi Nahuel,

As per @dnoeth 's reply this cannot be done using an SP. The reason why is as follows:

 

'Create table' is a DDL statement.

SQL rules say that a DDL statement must be the only statement or the last statement in a Transaction. Teradata rules say that a DDL statement must be the only statement in a Request.

An SP runs on a single session and Teradata only allows one active request per session.

 

If you try using F9 for two 'create table' statements in SQLA it fails with: "[3932] Only an ET or null statement is legal after a DDL Statement."

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Fan

Re: Execute in parallel inside Stored Procedure

Thank you both for your answers.

I was not aware that this wasn't possible. I now realise I have never tried to execute CREATE statements in parallel, only INSERTs.

 

I have rewritten just a small part of what I want to execute. So, I am goint to present you with a modified problem =)

 

This would be my new code

 

CREATE TABLE first_table (id INT, field1 VARCHAR(8));

CREATE TABLE second_table (id INT, field2 VARCHAR(8));


INSERT INTO first_table SELECT id, field1 FROM some_table;

INSERT INTO second_table SELECT id, field1 FROM other_table;

I would make the SP to execute the CREATE statements serially, and then (assuming this time I'm not incredibly wrong as before) to execute the INSERT statements in parallel. Would it be possible to make the SP do this?

 

Thank you again!

 

Nahuel

Senior Apprentice

Re: Execute in parallel inside Stored Procedure

Yep, no problem.

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Fan

Re: Execute in parallel inside Stored Procedure

Great!

Now...the question would be, how to make the SP do this? I have no idea how to tell the SP to execute certain code in serial and some other portions of code in parallel.

Would you be so kind of teaching me that syntax? Or pointing me to where could I find this information? I wasn't able to find it.

Junior Contributor

Re: Execute in parallel inside Stored Procedure

BEGIN REQUEST; -- single request from here ...
   INSERT INTO first_table SELECT id, field1 FROM some_table;
   INSERT INTO second_table SELECT id, field1 FROM other_table;
END REQUEST; -- ... to here

This is a Multi Statement Request within a Stored Procedure.

Of course both Inserts are a single transaction now, if one fails both are rolled back.

 

I just checked the manuals, it's hard to find, no details :-(