db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: faster inserts in big tables
Date Thu, 11 Dec 2008 19:34:32 GMT
Rick Hillegas <Richard.Hillegas@Sun.COM> 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

Mime
View raw message