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

For more information on table functions, see the white paper at 
http://developers.sun.com/javadb/reference/whitepapers/index.jsp

Hope this helps,
-Rick


>
> publicayers wrote:
>   
>> I have 10's of thousands of rows to add to a table, possibly 100's of 
>> thousands, and I'm wondering if there's anything else I can do to speed 
>> this up. The table could end up having a couple of million rows.
>>
>> This is what I've done so far:
>> * Using a PreparedStatement that gets reused with each insert.
>> * Set locking level to TABLE for that table.
>> * Turned off autocommit.
>> * Set the connection to READ_COMMIT.
>>
>> In addition to that, I'm also setting these system parameters, though 
>> not
>> necessarily to improve insert performance:
>> * derby.system.durability=test
>> * derby.storage.pageSize=32768
>>
>> The table has one odd feature: The last column is a VARCHAR(32672) FOR 
>> BIT DATA. I've tried setting the length to something smaller, but it 
>> didn't really seem to matter.
>>
>> The primary key is an auto generated int with another 2-column index on 
>> two BIGINT columns. Something I found interesting is that the inserts 
>> seem to go 2x faster if I have the 2-column index in place than if I 
>> have just the primary-key index.
>>
>> I'm running
>>     Derby 10.2.2
>>     JRE 1.6.0_07
>>     Windows XP SP2
>>
>> Is there anything else I can do to speed up row inserts?
>>
>> Thanks,
>> Brian
>>
>>
>>     
>
>