speed up inserting data into volatile table from a txt file?

General
Highlighted
Enthusiast

speed up inserting data into volatile table from a txt file?

Hi ,

 

I am trying to load data from a text file into a volatile table.  I have been a SQL server user and recently moved to a role that needs to use Teradata. While I got the data loaded into the volatile table , it is extremely slow. For a file with 8068 rows it took around 16 mins.( I used import mode in SQL Assistant).  Bulk inserts in SQL server just took less than a min to load the same file into its temp table. There should be a way to expedite the load in Teradata. Someone please point me in the right direction.

 

Thanks 


Accepted Solutions
Ambassador

Re: speed up inserting data into volatile table from a txt file?

Hi,

 

I suspect the slow loading is because you are not using 'bulk load' logic. By default SQLA will load one row at a time.

 

Look at the SQLA help topics ("Help / Help Topics") and then look in "The Query Window \ Importing Data \ Types of Import Operations".

 

For what you want to do you'll need to use "Type 3: Batch" which also means you need to use a NET connection (not ODBC).

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
5 REPLIES 5
Ambassador

Re: speed up inserting data into volatile table from a txt file?

Hi,

 

I suspect the slow loading is because you are not using 'bulk load' logic. By default SQLA will load one row at a time.

 

Look at the SQLA help topics ("Help / Help Topics") and then look in "The Query Window \ Importing Data \ Types of Import Operations".

 

For what you want to do you'll need to use "Type 3: Batch" which also means you need to use a NET connection (not ODBC).

 

HTH

Dave

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

Re: speed up inserting data into volatile table from a txt file?

@DaveWellman  Thanks a lot !! THAT IS IT!!

This can be the only way that I can achieve this as the other import methods will not be applicable to volatile tables.

I am trying to find an example for batch import with SQL assistant couldn't find anythign so far.  Have you done this before , Can u point me to an example ?

 

Thanks

 

Ambassador

Re: speed up inserting data into volatile table from a txt file?

Hi,

I'm glad that idea works for you.

Sorry but I don't have an example. I've done it a couple of times, but I tend to use BTEQ for loading small volumes of data from my pc (also means I can run it as a batch job).

Cheers,

Dave

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

Re: speed up inserting data into volatile table from a txt file?

With this method, can I speed up the processing of registered users database data? For example, users are registered on the nz-casinos.com site and I will need to export the data to a table for reports. If it is possible, it will be very cool.

Ambassador

Re: speed up inserting data into volatile table from a txt file?

It doesn't really matter if the user is registered on an external site or not. (Obviously) To do this they need a userid on the Teradata system and that is all.

Does this help?

Cheers,

Dave

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