Inserting 100049 rows into a derby table

[Trevor Simpson <ta...@bigpond.net.au>]

Can anyone help ?

I have a derby database with one table. The table includes the primary key plus 7 other columns. There is an index on 4 of those 7 columns.

I am inserting 100,049 rows into the table and no-matter what I try, I need more than 32MB to insert those records (at one time).

I have tried turning autocommit on and off, I have tried using Batch processing and normal insert processing but whatever I do, I need more than 32MB to insert the 100,049 rows into the table at one time . When using java -Xmx48m to insert the 100,049 rows, free space is around 12 to 14 MB during the insert process.

I am trying to understand why the derby database management system needs approx 36MB to insert the 100,049 rows. The database is around 67.8MB in size, when all rows have been inserted. What does derby keep in memory which requires approx 36MB during the entire insert process?

Any help will be most appreciated.

Regards
Trevor Simpson

Re: Derby Procedure performance

[Daniel John Debrunner <dj...@apache.org>]

Dave Been wrote:
> 
> I hear this question was recently discussed (last week?) but was 
> embedded in another subject, maybe someone can point me to the subject 
> so i can find it in the archives.
> I have not found it so far.
> 
> Using the *Embedded *driver (not network server), I have an application 
> with 3 PreparedStatements which do a *select*/*update *qualified on 1 
> row via indexed column, and an *insert* over a single table.
> 
> Would there be any performance gain putting the 3 PreparedStatements 
> into a Java Procedure so all are executed locally within the database?
> The parameters are 3 input ints, one String/varchar 1 - 256 bytes in 
> length, one OUT int value.
> 
> If there is any hope i can try it and time it, but if there is no hope 
> it would save me the effort.

http://mail-archives.apache.org/mod_mbox/db-derby-user/200701.mbox/%3c45B4FD44.2030409@apache.org%3e

Dan.

Derby Procedure performance

[Dave Been <be...@us.ibm.com>]

I hear this question was recently discussed (last week?) but was embedded 
in another subject, maybe someone can point me to the subject so i can 
find it in the archives.
I have not found it so far.

Using the Embedded driver (not network server), I have an application with 
3 PreparedStatements which do a select/update qualified on 1 row via 
indexed column, and an insert over a single table.

Would there be any performance gain putting the 3 PreparedStatements into 
a Java Procedure so all are executed locally within the database? 
The parameters are 3 input ints, one String/varchar 1 - 256 bytes in 
length, one OUT int value.

If there is any hope i can try it and time it, but if there is no hope it 
would save me the effort.

thanks much,
Dave Been

Re: Inserting 100049 rows into a derby table

[Mike Matrigali <mi...@sbcglobal.net>]

The following information would help to answer your question:
1) complete ddl of the table in question (ie. the create table statement and any
   indexes on that table).
2) a code segment of how you are inserting the data, streams can cause problems if not used correctly with large data types.
3) Some estimate of the size of rows/size of data being inserted.  You may
    have provided this info, but wasn't sure if the 100k rows are the entire
    db or if this is an additional 100k rows into an existing 67mb db.
4) are you embedded or client/server?

Without that info my best guess is the issue is the derby data cache which 
defaults to 1000 pages.  These pages can be 32k in size.  Answer to 1 will
tell me what page size derby choose for your table, unless you used derby
properties to change the default.  Derby by default will always try to fill up
those 1000 pages in memory, so it will try to cache all the data you are
inserting up to those 1000 pages.  This cache is configurable, so if you 
want less data cached it is safe to set this down all the way to 40 pages.
This may or may not be your issue, but is easy to test out.  

Trevor Simpson <ta...@bigpond.net.au> wrote:       Can anyone help ?
  
 I have a derby database with one table. The table  includes the primary key plus 7 other columns. There is an index on 4 of those 7  columns.
  
 I am inserting 100,049 rows into the table and  no-matter what I try, I need more than 32MB to insert those records (at one  time).
  
 I have tried turning autocommit on and off, I have  tried using Batch processing and normal insert processing but whatever I do, I  need more than 32MB to insert the 100,049 rows into the table at one time  . When using java -Xmx48m to insert the 100,049 rows, free  space is around 12 to 14 MB during the insert process.
  
 I am trying to understand why the derby database  management system needs approx 36MB to insert the 100,049 rows. The  database is around 67.8MB in size, when all rows have been inserted.  What does derby keep in memory which requires approx 36MB during  the entire insert process?
  
 Any help will be most appreciated.
  
 Regards
 Trevor Simpson