1

I need to speed up inserts into an Ingress VectorWise database, and the documentation shows this:

http://docs.actian.com/ingres-vectorwise/2.5/sql-language-guide/5315-insert

The INSERT statement inserts rows into a table.

This statement has the following format:

[REPEATED]]INSERT INTO [schema.]table_name
    [(column {, column})]
    [OVERRIDING SYSTEM VALUE | OVERRIDING USER VALUE]
    [VALUES (expr{, expr}) {,(expr{ ,expr})} |
    [WITH common_table_expression] subselect];

and says:

REPEATED: Saves the execution plan of the insert, which can make subsequent executions faster.

I can not for the life of me get a query using "REPEATED" to execute successfully, and I can't find any examples online using straight up SQL for it. Does anyone have any suggestions on how to get this to work syntactically?

Charles
  • 50,943
  • 13
  • 104
  • 142
John Humphreys
  • 37,047
  • 37
  • 155
  • 255
  • Are you doing a whole bunch of inserts all at once? – D'Arcy Rittich Aug 08 '12 at 20:57
  • Yup. It's happening from an ETL tool though, so they're going to happen one at a time and I can't do much about it. I can do batching, but then I don't get control over the SQL that's used and it doesn't seem to improve speed too much anyway. – John Humphreys Aug 08 '12 at 20:58
  • OK - I was going to suggest using a bunch of UNIONed subqueries to do a single batch insert, but sounds like that is out. – D'Arcy Rittich Aug 08 '12 at 20:59
  • I might be able to work that in with some effort, it's a good idea. It'd take some extra steps in the tool though so I'd prefer to just do this REPEATED thing if it actually works. I don't know why there's no examples around :( Thanks for the suggestion! – John Humphreys Aug 08 '12 at 21:01

2 Answers2

1

I'm not sure if Vectorwise supports it, Ingres does, but you might want to look at using CACHE_DYNAMIC. See http://docs.actian.com/ingres/10s/upgrade-guide/2038-cached-dynamic-cursor-query-plans for more info.

grantc
  • 1,703
  • 12
  • 13
1

@w00te : Ignore REPEATED. I would rather do something more "radical" if the multi-insertion performance is essential. - I would create a temporary HEAP table (they are the fastest storage type when it comes to insertion) and once I am done inserting bunch (can be MILLIONS) of rows, then I just either MODIFY the table to VECTORWISE or SELECT from it into a vectorwise table. If you just select, then perhaps the table does not have to be temporary, it can be a normal table where you temporarily store data. Depends on the use-case.

While inserting into the abovementioned HEAP table, use batch processing if possible (Ingres JDBC >= 4.0.1), it can significantly boost the speed of inserting millions of rows (I know it for a fact - I did few tests).

EDIT: Apparently, this will not work with VectorWise. The best approach is to insert straight to a VectorWise table, preferably non-indexed. Use JDBC prepared statement, and batch execution, and you will be fine. Grant's proposal to use CACHE_DYNAMIC should also be considered.

DejanLekic
  • 18,787
  • 4
  • 46
  • 77