Optimization of Insert....Select statement


Optimization of Insert....Select statement

Hi All,

We are inserting data in an empty table through 2 Insert...Select statements in our Bteq scripts One of which is Consuming a lot of CPU Time (2 hours )though it is inserting 3.6 Million rows.

We reached our DBA's for any possibility of Query Optimization.

DBA advised that 'second insert is inserting into a populated table. If this was supposed to be a multi-statement insert/select, which would make sense and run much faster, then the syntax needs to be very specific to that need'.

And advised us to change our Statements from:

Insert Into Select...... ; 

Insert Into Select......;


Insert Into Select...... ; Insert Into Select......;

The reason given by them is 'Once you remove the spaces optimizer thinks it’s one connected insert statement, not 2 separate inserts.' 

Now I am not sure how is this going to help because as soon as Parser encounters ';' It will send that part to Parser and a Explain will be created.

Can anyone please explain if I am correct or wrong.

Your help is much appreciated.

Thanks and Regards,

Apoorv Jain


Re: Optimization of Insert....Select statement


try running explain, you DBA is correct.

From Manual:

BTEQ does not submit any of the statements to Teradata Database until it

encounters a semicolon as the last nonblank character of a line.


Re: Optimization of Insert....Select statement

Hi Glass,

Thanks for your reply.

I generated the explain for the queries and it generated 2 separate explains for 

Insert Into Select...... ; Insert Into Select......;

Please Advise.


Re: Optimization of Insert....Select statement


Can you give some more details?

-- What is the difference between the 2 inserts?

-- 2 inserts are selecting data from different base tables?

-- Usually which insert will have more volume of data?

-- Have you checked for collect stats in PI & JOIN columns?

-- As suggested by DBA is there any improvement in your performance?

Re: Optimization of Insert....Select statement


Please find below the answers to your questions:

1. The difference is in number of source tables, also we have a Union operation being performed in the Select statement of second Insert.

2. Yes 2nd Insert statement is selecting data from more number of tables.

3. the 2nd Insert statement.

4. Yes, the stats are collected before executing the Insert statements.

5. No, I haven't executed the query till now.


Re: Optimization of Insert....Select statement


Even if you do not provide a lot of details you must take under consideration three basic things.

1) If the target table is SET or not , if it is the result is normal - try to change it to multiset-

2)Before running the insert statement , calculate with hasbucket.....  as a select statement the skew of the result set.

3)Try to check and compare the two phase statements ,

i) which calculate the Cpu on calculating the result set

ii)the step where  you try to insert this result set on the table.

-do this either from viewpoint , or from dbqlog-step-.

Please may you send us at least , CPU , IO , SPOOL , CPU - IO SKEW 

thank you.