Rick Hillegas <[email protected]> writes: > Hi Brian, > > In a previous response Peter Ondruška noted that you could use bulk > import to speed up your inserts if your source data lived in a > properly formatted file. > > Even if your source data does not live in a file, you can still get > bulk import speed (and the benefits of your generated key) by using > table functions--provided that you upgrade to 10.4. If you wrap your > source data in a table function, then you can bulk insert your data as > follows: > > insert into MY_TABLE( NON_GEN_COL1, ... NON_GEN_COL_N ) > select * from table( MY_TABLE_FUNCTION() ) s
The internal SQL syntax allows you to add an optimizer override here (--DERBY-PROPERTIES insertMode=bulkInsert) which I think speeds it up even more, though I'm not sure exactly what it buys you. This override is used in the import code and in the SYSCS_UTIL.SYSCS_BULK_INSERT system procedure, but you're not allowed to use it in your own SQL queries, it seems. The (undocumented) SYSCS_BULK_INSERT procedure does exactly what Rick's example does, except that it also adds the optimizer override. The procedure only works on old-style table functions, though. Does anyone know what performance gains one can expect by using SYSCS_BULK_INSERT instead of INSERT INTO ... SELECT FROM? Would it be worth the effort to create a similar mechanism for the new-style table functions? -- Knut Anders
