UDA

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-17-2007
09:42 PM

12-17-2007
09:42 PM

FAST PATH INSERT SELECT

Can someone please explain the FAST PATH INSERT SELECT Teradata Function?

I have 300,000 rows of data I need to insert into a table and then run queries on. With normal INSERT this takes hours.

I know there is a function to speed this process up but DO NOT know how to use it.

MANY THANKS,

dbread007

I have 300,000 rows of data I need to insert into a table and then run queries on. With normal INSERT this takes hours.

I know there is a function to speed this process up but DO NOT know how to use it.

MANY THANKS,

dbread007

5 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-18-2007
01:57 AM

12-18-2007
01:57 AM

Re: FAST PATH INSERT SELECT

Hi,

Try using teradata utilities like FastLoad/MultiLoad for faster insert operation.

For more details on FastLoad and MultiLoad you can refer specific Teradata utility PDFs.

Regards,

Balamurugan

Try using teradata utilities like FastLoad/MultiLoad for faster insert operation.

For more details on FastLoad and MultiLoad you can refer specific Teradata utility PDFs.

Regards,

Balamurugan

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-18-2007
02:04 AM

12-18-2007
02:04 AM

Re: FAST PATH INSERT SELECT

INSERT INTO Promotion

SELECT Name, DeptNo, YrsExp

FROM employee

WHERE YrsExp > 10;

When the primary indexes (or indices) of promotion and employee tables are the same column then FAST PATH INSERT SELECT takes place and it is extremely fast. All Amps work parallelly because the source and target tables happen to be on the same path.

SELECT Name, DeptNo, YrsExp

FROM employee

WHERE YrsExp > 10;

When the primary indexes (or indices) of promotion and employee tables are the same column then FAST PATH INSERT SELECT takes place and it is extremely fast. All Amps work parallelly because the source and target tables happen to be on the same path.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-18-2007
02:06 AM

12-18-2007
02:06 AM

Re: FAST PATH INSERT SELECT

Sorry for the typo...

All Amps work parallelly because the source and target tables happen to be on the same Amp..

All Amps work parallelly because the source and target tables happen to be on the same Amp..

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-18-2007
10:16 AM

12-18-2007
10:16 AM

Re: FAST PATH INSERT SELECT

If the target table is emtpy at the begin of the transaction, then that Insert/Select doesn't need a Transient Journal, because in case of a Rollback it's just emptied again.

This is called a "Fast Path Insert/Select".

If PIs of target and source table match, it's faster, because for that AMP-local operation there's no redistribution necessary.

But this is true for any kind of operation, not only "Fast Path Insert/Select".

The OP didn't talk about the operation:

"I have 300,000 rows of data I need to insert into a table and then run queries on. With normal INSERT this takes hours."

This might be an Insert/Select into a Set table with lots of duplicate row checks

or lots of single row Inserts using a tool.

Solution for #1 would be a better PI and/or Multiset.

Solution for #2 would be FastLoad or BTEQ/JDBC using arrays.

Dieter

This is called a "Fast Path Insert/Select".

If PIs of target and source table match, it's faster, because for that AMP-local operation there's no redistribution necessary.

But this is true for any kind of operation, not only "Fast Path Insert/Select".

The OP didn't talk about the operation:

"I have 300,000 rows of data I need to insert into a table and then run queries on. With normal INSERT this takes hours."

This might be an Insert/Select into a Set table with lots of duplicate row checks

or lots of single row Inserts using a tool.

Solution for #1 would be a better PI and/or Multiset.

Solution for #2 would be FastLoad or BTEQ/JDBC using arrays.

Dieter

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-07-2011
12:27 PM

04-07-2011
12:27 PM

Re: FAST PATH INSERT SELECT

Hello,

My target table is empty. I have 8 tables to load data into this table. The PI of the target table isnt the same as that in the 8 table and neither the 8 tables have it among them. However, the datatype and size of all these PIs is the same across all the tables(8 source & target tables). Also, it would not be a simple select on each of the 8 tables. Each select would have a few other tables as well apart from the source table. Can I establish a fast path INSERT/SELECT in this scenario?

Regards,

Ayush Jain

My target table is empty. I have 8 tables to load data into this table. The PI of the target table isnt the same as that in the 8 table and neither the 8 tables have it among them. However, the datatype and size of all these PIs is the same across all the tables(8 source & target tables). Also, it would not be a simple select on each of the 8 tables. Each select would have a few other tables as well apart from the source table. Can I establish a fast path INSERT/SELECT in this scenario?

Regards,

Ayush Jain