All Forums Database
jsergey 15 posts Joined 06/07
17 Jun 2009
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

Adeel Chaudhry 773 posts Joined 04/08
17 Jun 2009

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

-- If you are stuck at something .... consider it an opportunity to think anew.

jsergey 15 posts Joined 06/07
17 Jun 2009

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

Jimm 298 posts Joined 09/07
17 Jun 2009

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.

Adeel Chaudhry 773 posts Joined 04/08
17 Jun 2009

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

-- If you are stuck at something .... consider it an opportunity to think anew.

jsergey 15 posts Joined 06/07
17 Jun 2009

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

jsergey 15 posts Joined 06/07
17 Jun 2009

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

robpaller 159 posts Joined 05/09
29 Jun 2009

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.

Random_Thought 87 posts Joined 06/09
01 Jul 2009

Check the distribution with the following SELECT HASHAMP(HASHBUCKET(HASHROW( ))),COUNT(*) FROM ( ) as test_table GROUP BY 1replacing the 2 parts.This should give you the distribution across the AMPs, you will then see if it is skewed.

Random_Thought 87 posts Joined 06/09
01 Jul 2009

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

jpetrides 2 posts Joined 11/12
27 Nov 2015

Hello from 6 years later!
 
Random_thought.  Thanks - your suggestion to add a primary index onto column(s) with a range of values worked.  I went from > 10 min to 5 seconds.
My query without the primary index had the same value in column 1 for all rows.

You must sign in to leave a comment.