How to speed up inserting data into volatile table ?

Database
Enthusiast

How to speed up inserting data into volatile table ?

Hello!

Is it any way to speed up inserting data into volatile table?
I've tried to INSERT ~50.000 rows(selected from another table) and it was ~40 minutes.

DDL is simple:
CREATE SET VOLATILE TABLE v_table, NO FALLBACK, CHECKSUM = DEFAULT,NO LOG
(...5 columns...)
ON COMMIT PRESERVE ROWS;

Any ideas ?

Thanks.

Regards,
Sergey
10 REPLIES
Teradata Employee

Re: How to speed up inserting data into volatile table ?

Hello,

You can try by removing SET from the CREATE command and ensuring uniqueness of records while inserting into it or while retrieving data from the volatile table.

And you may also check the current load on the system, if it is too busy doing something, you can't get better performance!

HTH!

Regards,

Adeel
Enthusiast

Re: How to speed up inserting data into volatile table ?

Thanks for your answer!

Testing workstation is not too fast themselves but it not in use at this time by anybody excepting me.
I've tried to remove SET and add "NO BEFORE JOURNAL" and "NO AFTER JOURNAL" but looks like it doesn't matter.

The problem is that SELECT's executing time is ~3 seconds, all other time(~40 minutes) is INSERTS.

Thanks.

Regards,
Sergey
Enthusiast

Re: How to speed up inserting data into volatile table ?

You do not seem to have a primary index on your volatile table spec. The Create table will use the first column - does this have a wide range of values?
Volatile tables use the same rules as permanent tables for data placement - your data is probably going to one or a few AMPS.
Teradata Employee

Re: How to speed up inserting data into volatile table ?

We once faced such scenario in one of the projects, we had to insert around 40 rows in VT table having single column .... with INSERT statement in SQL Assistant it was taking about 2-3 minutes. After much benchmarking we created a Stored-Procedure and issued the same INSERT and voila it was taking less than 2 seconds!

I hope you can do the same and find it useful! :)

Regards,

Adeel
Enthusiast

Re: How to speed up inserting data into volatile table ?

I've tried with/without primary_keys/indexes - result is the same.
Enthusiast

Re: How to speed up inserting data into volatile table ?

Thanks, I'll try stored procedures....maybe its really will help :)
Enthusiast

Re: How to speed up inserting data into volatile table ?

I'm going to side with jimm on this and suggest your lack of a primary index in your table definition combined with the values in the first column of the table distributing poorly is the culprit. The primary index is going to determine how your data is distributed across the system, and if you have a primary index defined with a high percentage of repeating values you are skewing your data and workload to a single amp on the system. Thus the discrepancy between the SELECT and INSERT INTO..SELECT statements.

Enthusiast

Re: How to speed up inserting data into volatile table ?

Check the distribution with the following

SELECT HASHAMP(HASHBUCKET(HASHROW( ))),COUNT(*)
FROM ( ) as test_table
GROUP BY 1


replacing the 2 parts.

This should give you the distribution across the AMPs, you will then see if it is skewed.

Enthusiast

Re: How to speed up inserting data into volatile table ?

Correction below. I tried to bold the parts to replace, but they were missing after I posted! BOLD does not work!

SELECT HASHAMP(HASHBUCKET(HASHROW( FIRST COLUMN GOES HERE ))),COUNT(*)

FROM ( YOUR SELECT STATEMENT GOES HERE ) as test_table

GROUP BY 1