Insert statements are very, very SLOW!
Moderator: NorbertKrupa
Insert statements are very, very SLOW!
Why are INSERT statements so slow in Vertica? Running a script that contains thousands of INSERT statements takes forever? Is there a work around?
Thank you!
Joshua
Joshua
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Insert statements are very, very SLOW!
Hi,
Unfortunately there is not a way to speed up 1000s of DML INSERT statements in Vertica. The best throughput we've seen when using INSERT statements is only about 28 records per second! There is a lot of "stuff" that goes on behind the scene for each INSERT statement. In fact, each one of the INSERT statements is acting within its own transaction! Check the vertica.log file after you've ran your script and you'll see what I'm talking about.
If you need to load a lot of data into Vertica, the only way to go is with the bulk load COPY command. We've seen the best performance when using COPY with the DIRECT option so that data bypasses the WOS (memory) and and goes directly to the ROS (disk). The COPY command is extremely fast!
Thanks!
Unfortunately there is not a way to speed up 1000s of DML INSERT statements in Vertica. The best throughput we've seen when using INSERT statements is only about 28 records per second! There is a lot of "stuff" that goes on behind the scene for each INSERT statement. In fact, each one of the INSERT statements is acting within its own transaction! Check the vertica.log file after you've ran your script and you'll see what I'm talking about.
If you need to load a lot of data into Vertica, the only way to go is with the bulk load COPY command. We've seen the best performance when using COPY with the DIRECT option so that data bypasses the WOS (memory) and and goes directly to the ROS (disk). The COPY command is extremely fast!
Thanks!
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Insert statements are very, very SLOW!
Can you do a copy command to move data from table to another?
Any suggestions on how to make INSERT faster?
Thanks in advance.
Any suggestions on how to make INSERT faster?
Thanks in advance.
Re: Insert statements are very, very SLOW!
You can use INSERT..SELECT to move data from one table to another.
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC
Vertica Consultant, Zazz Technologies LLC
Re: Insert statements are very, very SLOW!
I tried it. But the insert is still supper slow. Are there any suggestions for what I can check that might be slowing down the insert?
Re: Insert statements are very, very SLOW!
To investigate the slow INSERT..SELECT:
- Verify that the projections for both the source and destination tables are segmented across all nodes
- Verify that you have only one set of projections defined for the destination table
- Does either table have very wide varchars?
How long does the INSERT..SELECT take?
How many nodes are in your cluster?
How many rows are you trying to INSERT?
You can PROFILE the insert..select statement and then query query_plan_profiles or execution_engine_profiles (correlated to explain output) depending on which version you're running. You can use either of these to see where the time is being spent.
--Sharon
- Verify that the projections for both the source and destination tables are segmented across all nodes
- Verify that you have only one set of projections defined for the destination table
- Does either table have very wide varchars?
How long does the INSERT..SELECT take?
How many nodes are in your cluster?
How many rows are you trying to INSERT?
You can PROFILE the insert..select statement and then query query_plan_profiles or execution_engine_profiles (correlated to explain output) depending on which version you're running. You can use either of these to see where the time is being spent.
--Sharon
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC
Vertica Consultant, Zazz Technologies LLC