Insert statements are very, very SLOW!

[Josh]

Why are INSERT statements so slow in Vertica? Running a script that contains thousands of INSERT statements takes forever? Is there a work around?

[JimKnicely]

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!

[abraylyan]

Can you do a copy command to move data from table to another?

Any suggestions on how to make INSERT faster?

Thanks in advance.

[scutter]

You can use INSERT..SELECT to move data from one table to another.

[abraylyan]

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?

[scutter]

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