|
|
|
Junior Member
 
Group: Forum Members
Last Login: 6/18/2009 5:09:57 AM
Posts: 13,
Visits: 14
|
|
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
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/18/2009 5:48:26 AM
Posts: 273,
Visits: 1,214
|
|
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
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 6/18/2009 5:09:57 AM
Posts: 13,
Visits: 14
|
|
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
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/5/2009 3:21:51 PM
Posts: 101,
Visits: 241
|
|
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.
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/18/2009 5:48:26 AM
Posts: 273,
Visits: 1,214
|
|
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
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 6/18/2009 5:09:57 AM
Posts: 13,
Visits: 14
|
|
I've tried with/without primary_keys/indexes - result is the same.
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 6/18/2009 5:09:57 AM
Posts: 13,
Visits: 14
|
|
|
Thanks, I'll try stored procedures....maybe its really will help :)
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 7/29/2009 9:23:21 AM
Posts: 27,
Visits: 34
|
|
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.
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 9/10/2009 6:25:29 AM
Posts: 16,
Visits: 74
|
|
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.
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 9/10/2009 6:25:29 AM
Posts: 16,
Visits: 74
|
|
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
|
|