You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by "Thomas J. Taylor" <th...@itqa.miami.edu> on 2007/03/14 19:01:14 UTC
RE: Large multi-record insert performance
Hi Derek,
Have you tried batch inserts?
PreparedStatement pstmt = connection.prepareStatement("INSERT INTO t VALUES
(?, ?, ?)");
for (int i=1; i<1000; i++) {
pstmt.setInt(1, i);
pstmt.setInt(2, i);
pstmt.setInt(3, i);
pstmt.addBatch();
}
int[] results = pstmt.executeBatch();
Thomas
_____
From: Sedillo, Derek (Mission Systems) [mailto:Derek.Sedillo@ngc.com]
Sent: Wednesday, March 14, 2007 11:06 AM
To: derby-user@db.apache.org
Subject: Large multi-record insert performance
Hello,
I am trying to find the most efficient way to handle multi-records inserts
into a single table in Derby. Currently we use Oracle to perform
multi-record inserts with 'one' insert statement for large amounts of data.
This bulk loading process greatly enhances performance. For example:
INSERT INTO SSJ
VALUES (:tmp_ssj_data); //where tmp_ssj_data is an array (100s or 1000s) of
C structured records
Is there any way in Java DB to perform something similar? So lets say table
t has three integer columns.
INSERT INTO t VALUES (?,?,?)
Now you have 1000 rows to insert into t that look like this:
(1,1,1), (2,2,2), (3,3,3), (4,4,4), .
How would you pass these 1000 records in as VALUES with a 'single' insert
statement?
I am thinking of two options so far:
1. Use a prepared statement and 'set' all 1000 record values. Then
execute.
But I question whether calling the set method for all 1000 records
would be buy anything in terms of performance.
2. The other option would be to iterate an array of 1000 records and create
a VALUES clause string to look like:
String records = "(1,1,1), (2,2,2), (3,3,3), (4,4,4), .(1000, 1000, 1000)";
Then append the insert statement with the records and execute.
String query = "INSERT INTO t VALUES " + records;
Execute query;
Which method would be the most efficient way to perform these 1000 inserts
in Derby? Is there a more optimized way to do this?
Thank you for any experience or ideas you may have regarding large data
inserts.
Derek Sedillo
SWAFS DBA / Software Engineer
Northrop Grumman Missions Systems
Tel: (719) 570-8256
Re: Large multi-record insert performance
Posted by "Lance J. Andersen" <La...@Sun.COM>.
You might also want to look at StringBuilder if your code is not running
in multiple threads as it is more performant than StringBuffer
Øystein Grøvlen wrote:
> Sedillo, Derek (Mission Systems) wrote:
>
>> Iterate an array of 1000 records and create a VALUES clause string to
>> look like:
>> /String records = "(1,1,1), (2,2,2), (3,3,3), (4,4,4), …(1000, 1000,
>> 1000)";/
>
> You might already know, but make sure to use a StringBuffer when you
> are doing this so you avoid creating a new String object for every
> triplet you append.
>
> --
> Øystein
Re: Large multi-record insert performance
Posted by Øystein Grøvlen <Oy...@Sun.COM>.
Sedillo, Derek (Mission Systems) wrote:
> Iterate an array of 1000 records and create a VALUES clause string to
> look like:
> /String records = "(1,1,1), (2,2,2), (3,3,3), (4,4,4), …(1000, 1000,
> 1000)";/
You might already know, but make sure to use a StringBuffer when you are
doing this so you avoid creating a new String object for every triplet
you append.
--
Øystein
Re: Import data from large Oracle table
Posted by Army <qo...@gmail.com>.
Sedillo, Derek (Mission Systems) wrote:
> There are no foreign keys or triggers on the tables. But there are
> indexes which might be slowing things down. So I might drop the indexes
> and try running import again.
Just so you're aware, there is a known issue with Derby where creation of an
index before inserting data can lead to bad statistics and therefore bad
optimizer cost estimates, thus resulting in poor performance. See DERBY-269.
Or put another way, it's best to insert the data first and *then* create the
indexes.
So even if it doesn't speed up the import operation itself, it is still a good
idea to insert data first and create indexes afterward. Doing so will help the
optimizer find better plans for subsequent queries against the relevant tables.
Army
Re: Import data from large Oracle table
Posted by Sunitha Kambhampati <ks...@gmail.com>.
Sedillo, Derek (Mission Systems) wrote:
>Do you think that 2 million records are too many to use with the import
>utility?
>
>
Import utility can handle so many records. I have tested it with
importing 6 million records into a empty table and it took 18minutes on
my test machine
(RHEL AS 4, Linux 2.6kernel, ibm142, 2cpu 2.8 intel xeon, 4GRam,scsi disks )
Details:
========
Derby ( the current trunk version 10.3), embedded mode.
Input file: :
sh-3.00$ ls -l customer.txt
-rw-r--r-- 1 cloudtst RAND 2362796422 Mar 14 16:10 customer.txt
sh-3.00$ java -version
java version "1.4.2"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2)
Classic VM (build 1.4.2, J2RE 1.4.2 IBM build cxia32142-20061124 (SR7)
(JIT enabled: jitc))
Dataset is from one of the derby system tests (OE) which is based on
TPC-C benchmark.
Data in the customer.txt is the data exported from a customer table
after loading data with scale of 200.
Table schema:.
CREATE TABLE CUSTOMER2 (
C_ID INTEGER NOT NULL,
C_D_ID SMALLINT NOT NULL,
C_W_ID SMALLINT NOT NULL,
C_FIRST VARCHAR(16) NOT NULL,
C_MIDDLE CHAR(2) NOT NULL,
C_LAST VARCHAR(16) NOT NULL,
C_STREET_1 VARCHAR(20) NOT NULL,
C_STREET_2 VARCHAR(20) NOT NULL,
C_CITY VARCHAR(20) NOT NULL,
C_STATE CHAR(2) NOT NULL,
C_ZIP CHAR(9) NOT NULL,
C_PHONE CHAR(16) NOT NULL,
C_SINCE TIMESTAMP NOT NULL,
C_CREDIT CHAR(2) NOT NULL,
C_CREDIT_LIM DECIMAL(12,2) NOT NULL,
C_DISCOUNT DECIMAL(4,4) NOT NULL,
C_BALANCE DECIMAL(12,2) NOT NULL,
C_YTD_PAYMENT DECIMAL(12,2) NOT NULL,
C_PAYMENT_CNT INTEGER NOT NULL,
C_DELIVERY_CNT INTEGER NOT NULL,
C_DATA VARCHAR(500) NOT NULL
);
HTH,
Sunitha.
Re: Import data from large Oracle table
Posted by Mike Matrigali <mi...@sbcglobal.net>.
I think import into an empty table with no indexes, using embedded
derby is the way to go. 2 million doesn't sound like a lot but of
course that depends on the size of records (2 million 2 gig records
would be a lot). Any estimate of row size? What kind of cpu and
what kind of disk is being used?
If possible I would run a test with a much smaller dataset, say
10,000 records to get an idea of the speed required. Make sure there
is not some format error in the file.
See more comments below:
Sedillo, Derek (Mission Systems) wrote:
> Suresh,
>
> There are no foreign keys or triggers on the tables. But there are
> indexes which might be slowing things down. So I might drop the indexes
> and try running import again.
>
> Do you think that 2 million records are too many to use with the import
> utility?
>
> Thanks,
>
> Derek
>
> -----Original Message-----
> From: Suresh Thalamati [mailto:suresh.thalamati@gmail.com]
> Sent: Wednesday, March 14, 2007 4:00 PM
> To: Derby Discussion
> Subject: Re: Import data from large Oracle table
>
> Sedillo, Derek (Mission Systems) wrote:
>
>>Hello,
>>
>>I have several large Oracle tables (2+ million records) which I need
>>to import data from to Derby. Here is what I have tried so far:
>>
>>1. I have dumped the data to comma separated flat files.
>>2. Used the import table utility like this:
>> /CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE
>>(null,'SSJ_CNT','SSJ_CNT.csv',null, null,null,0);/
>
>
>
> Import will run slow, if the table has triggers, foreing key
> references or if the table already has some data. These force the data
> to be logged , which makes import run slow.
>
> If you have foreign keys/triggers you may want drop them and add them
> back after the data is imported. If you do that import might run
> faster.
>
>
>
>
>>3. After '4 hours' of running it appears to have frozen up.
What does frozen up mean? No CPU? No I/O? Did you monitor messages
in derby.log?
There is
>
>
>>both a db.lck and dbex.lck file present which I will delete soon.
What JVM are you using? With modern JVM's you should NEVER have to
delete these files by hand and can eliminate Derby's ability to prevent
db corruption by doing so.
>
>
> These files are not related to import. You should not be deleting
> them. They are used to prevent multiple jvm instances booting
> the same database concurrenly.
>
>
>
>>
>>Do you think that 2 million records is just too much for this utility
>
> to
>
>>handle? Is there a better way to transfer data from another database
>>besides the IMPORT_TABLE utility?
>>
>
>
> I have not tried with 2 million rows , IMPORT_TABLE is the fastest way
> to tranfer data from files into a derby database.
>
>
>
> hope that helps
> -suresh
>
>
>
RE: Import data from large Oracle table
Posted by "Sedillo, Derek (Mission Systems)" <De...@ngc.com>.
Suresh,
There are no foreign keys or triggers on the tables. But there are
indexes which might be slowing things down. So I might drop the indexes
and try running import again.
Do you think that 2 million records are too many to use with the import
utility?
Thanks,
Derek
-----Original Message-----
From: Suresh Thalamati [mailto:suresh.thalamati@gmail.com]
Sent: Wednesday, March 14, 2007 4:00 PM
To: Derby Discussion
Subject: Re: Import data from large Oracle table
Sedillo, Derek (Mission Systems) wrote:
> Hello,
>
> I have several large Oracle tables (2+ million records) which I need
> to import data from to Derby. Here is what I have tried so far:
>
> 1. I have dumped the data to comma separated flat files.
> 2. Used the import table utility like this:
> /CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE
> (null,'SSJ_CNT','SSJ_CNT.csv',null, null,null,0);/
Import will run slow, if the table has triggers, foreing key
references or if the table already has some data. These force the data
to be logged , which makes import run slow.
If you have foreign keys/triggers you may want drop them and add them
back after the data is imported. If you do that import might run
faster.
> 3. After '4 hours' of running it appears to have frozen up. There is
> both a db.lck and dbex.lck file present which I will delete soon.
These files are not related to import. You should not be deleting
them. They are used to prevent multiple jvm instances booting
the same database concurrenly.
>
> Do you think that 2 million records is just too much for this utility
to
> handle? Is there a better way to transfer data from another database
> besides the IMPORT_TABLE utility?
>
I have not tried with 2 million rows , IMPORT_TABLE is the fastest way
to tranfer data from files into a derby database.
hope that helps
-suresh
Re: Import data from large Oracle table
Posted by Suresh Thalamati <su...@gmail.com>.
Sedillo, Derek (Mission Systems) wrote:
> Hello,
>
> I have several large Oracle tables (2+ million records) which I need to
> import data from to Derby. Here is what I have tried so far:
>
> 1. I have dumped the data to comma separated flat files.
> 2. Used the import table utility like this:
> /CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE
> (null,'SSJ_CNT','SSJ_CNT.csv',null, null,null,0);/
Import will run slow, if the table has triggers, foreing key
references or if the table already has some data. These force the
data to be logged , which makes import run slow.
If you have foreign keys/triggers you may want drop them and add them
back after the data is imported. If you do that import might run faster.
> 3. After '4 hours' of running it appears to have frozen up. There is
> both a db.lck and dbex.lck file present which I will delete soon.
These files are not related to import. You should not be deleting
them. They are used to prevent multiple jvm instances booting
the same database concurrenly.
>
> Do you think that 2 million records is just too much for this utility to
> handle? Is there a better way to transfer data from another database
> besides the IMPORT_TABLE utility?
>
I have not tried with 2 million rows , IMPORT_TABLE is the fastest way
to tranfer data from files into a derby database.
hope that helps
-suresh
Re: Import data from large Oracle table
Posted by Michael Segel <ms...@mycingular.blackberry.net>.
Well...
You could open a connection to your oracle database, then open a connection to your derby database.
Select from oracle and then insert into derby...
(You could build on this to add commits and table locking...)
You could write your own loader to use a single thread to read from the flat file and have multiple writers to the database. (Simple mutex solution.)
Sent via BlackBerry.
-Mike Segel
Principal
MSCC
312 952 8175
-----Original Message-----
From: "Sedillo, Derek \(Mission Systems\)" <De...@ngc.com>
Date: Wed, 14 Mar 2007 15:19:00
To:"Derby Discussion" <de...@db.apache.org>
Subject: Import data from large Oracle table
Hello,
I have several large Oracle tables (2+ million records) which I need to import data from to Derby. Here is what I have tried so far:
1. I have dumped the data to comma separated flat files.
2. Used the import table utility like this:
CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null,'SSJ_CNT','SSJ_CNT.csv',null, null,null,0);
3. After '4 hours' of running it appears to have frozen up. There is both a db.lck and dbex.lck file present which I will delete soon.
Do you think that 2 million records is just too much for this utility to handle? Is there a better way to transfer data from another database besides the IMPORT_TABLE utility?
Thank you for any ideas or experiences you have had with importing data from another DB.
Derek Sedillo
SWAFS DBA / Software Engineer
Northrop Grumman Missions Systems
Tel: (719) 570-8256
Import data from large Oracle table
Posted by "Sedillo, Derek (Mission Systems)" <De...@ngc.com>.
Hello,
I have several large Oracle tables (2+ million records) which I need to
import data from to Derby. Here is what I have tried so far:
1. I have dumped the data to comma separated flat files.
2. Used the import table utility like this:
CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE
(null,'SSJ_CNT','SSJ_CNT.csv',null, null,null,0);
3. After '4 hours' of running it appears to have frozen up. There is
both a db.lck and dbex.lck file present which I will delete soon.
Do you think that 2 million records is just too much for this utility to
handle? Is there a better way to transfer data from another database
besides the IMPORT_TABLE utility?
Thank you for any ideas or experiences you have had with importing data
from another DB.
Derek Sedillo
SWAFS DBA / Software Engineer
Northrop Grumman Missions Systems
Tel: (719) 570-8256
Re: Large multi-record insert performance
Posted by Mike Matrigali <mi...@sbcglobal.net>.
Do you have some idea what kind of insert performance you need in
your application? It probably would be easiest for me to understand
in rows per second with some estimate in row size. Weather data
sounds like it might be very big per row.
So far the suggestions will tend to be single threaded so may not use
up the 2nd processor on your machine, depending on the other work your
app server needs to do this may be just fine. Derby will automatically
support
multiple threads inserting into the same table, but I usually suggest
applications first see if a single thread, with large groups of inserts
per commit, on an embedded derby instance meet their need. Once you
get into multiple threads you have to start to worry about lock
waits/deadlocks between the 2/N threads depending out the data.
Inserts will be very logging intensive, so putting the log on a separate
disk from your data is the first step to avoid being I/O bound. If you
find your data disk I/O bound then some sort of raid/striping may help
there.
I didn't realize the import and insert questions were coming from the
same app. I assume the import usage is a one-time thing and then from
your most recent comments you need to stream data into the db as it
comes in.
Sedillo, Derek (Mission Systems) wrote:
> Actually this will be a part of the application logic. We have real
> time weather data which we constantly receive and insert into the DB.
>
> - Derek
>
> ------------------------------------------------------------------------
> *From:* Mamta Satoor [mailto:msatoor@gmail.com]
> *Sent:* Wednesday, March 14, 2007 5:02 PM
> *To:* Derby Discussion
> *Subject:* Re: Large multi-record insert performance
>
> If this bulk insert is not normal part of application logic and only
> done once in a while then I wonder if import using
> *SYSCS_UTIL.SYSCS_IMPORT _TABLE *would be a faster way to load data.
>
> Mamta
>
>
> On 3/14/07, *Lance J. Andersen* <Lance.Andersen@sun.com
> <ma...@sun.com>> wrote:
>
>
>
> Mike Matrigali wrote:
> >
> >
> > Lance J. Andersen wrote:
> >>
> >>
> >> Even if the backend does not provide optimization for batch
> >> processing, i would hope that there would be still some efficiency
> >> especially in a networked environment vs building the strings,
> >> invoking execute() 1000 times in the amount of data on the wire...
> >>
> >>
> > I could not tell from the question whether this was network or
> not. I
> > agree in network then limiting execution probably is best. In
> embedded
> > I am not sure - I would not be surprised if doing 1000 in batch is
> > slower than just doing the executes.
> >
> > In either case I really would stay away from string manipulation
> as much
> > as possible and also stay away from things that create very long SQL
> > statements like 1000 term values clauses.
> i agree completely. Let the driver do the heavy lifting :-)
>
>
RE: Large multi-record insert performance
Posted by "Sedillo, Derek (Mission Systems)" <De...@ngc.com>.
Actually this will be a part of the application logic. We have real
time weather data which we constantly receive and insert into the DB.
- Derek
________________________________
From: Mamta Satoor [mailto:msatoor@gmail.com]
Sent: Wednesday, March 14, 2007 5:02 PM
To: Derby Discussion
Subject: Re: Large multi-record insert performance
If this bulk insert is not normal part of application logic and only
done once in a while then I wonder if import using
SYSCS_UTIL.SYSCS_IMPORT _TABLE would be a faster way to load data.
Mamta
On 3/14/07, Lance J. Andersen <La...@sun.com> wrote:
Mike Matrigali wrote:
>
>
> Lance J. Andersen wrote:
>>
>>
>> Even if the backend does not provide optimization for batch
>> processing, i would hope that there would be still some
efficiency
>> especially in a networked environment vs building the
strings,
>> invoking execute() 1000 times in the amount of data on the
wire...
>>
>>
> I could not tell from the question whether this was network or
not. I
> agree in network then limiting execution probably is best. In
embedded
> I am not sure - I would not be surprised if doing 1000 in
batch is
> slower than just doing the executes.
>
> In either case I really would stay away from string
manipulation as much
> as possible and also stay away from things that create very
long SQL
> statements like 1000 term values clauses.
i agree completely. Let the driver do the heavy lifting :-)
Re: Large multi-record insert performance
Posted by Mamta Satoor <ms...@gmail.com>.
If this bulk insert is not normal part of application logic and only done
once in a while then I wonder if import using *SYSCS_UTIL.SYSCS_IMPORT_TABLE
*would be a faster way to load data.
Mamta
On 3/14/07, Lance J. Andersen <La...@sun.com> wrote:
>
>
>
> Mike Matrigali wrote:
> >
> >
> > Lance J. Andersen wrote:
> >>
> >>
> >> Even if the backend does not provide optimization for batch
> >> processing, i would hope that there would be still some efficiency
> >> especially in a networked environment vs building the strings,
> >> invoking execute() 1000 times in the amount of data on the wire...
> >>
> >>
> > I could not tell from the question whether this was network or not. I
> > agree in network then limiting execution probably is best. In embedded
> > I am not sure - I would not be surprised if doing 1000 in batch is
> > slower than just doing the executes.
> >
> > In either case I really would stay away from string manipulation as much
> > as possible and also stay away from things that create very long SQL
> > statements like 1000 term values clauses.
> i agree completely. Let the driver do the heavy lifting :-)
>
Re: Large multi-record insert performance
Posted by "Lance J. Andersen" <La...@Sun.COM>.
Mike Matrigali wrote:
>
>
> Lance J. Andersen wrote:
>>
>>
>> Even if the backend does not provide optimization for batch
>> processing, i would hope that there would be still some efficiency
>> especially in a networked environment vs building the strings,
>> invoking execute() 1000 times in the amount of data on the wire...
>>
>>
> I could not tell from the question whether this was network or not. I
> agree in network then limiting execution probably is best. In embedded
> I am not sure - I would not be surprised if doing 1000 in batch is
> slower than just doing the executes.
>
> In either case I really would stay away from string manipulation as much
> as possible and also stay away from things that create very long SQL
> statements like 1000 term values clauses.
i agree completely. Let the driver do the heavy lifting :-)
RE: Large multi-record insert performance
Posted by "Sedillo, Derek (Mission Systems)" <De...@ngc.com>.
Mike,
Derby is running in embedded mode on our Solaris dual processor app
server. The size of the files are ~700 MB per table.
I am going to drop all indexes and keys then try a limited subset (~1000
records).
- Derek
-----Original Message-----
From: Mike Matrigali [mailto:mikem_app@sbcglobal.net]
Sent: Wednesday, March 14, 2007 4:41 PM
To: Derby Discussion
Subject: Re: Large multi-record insert performance
Lance J. Andersen wrote:
>
>
> Even if the backend does not provide optimization for batch
> processing, i would hope that there would be still some efficiency
> especially in a networked environment vs building the strings,
> invoking execute() 1000 times in the amount of data on the wire...
>
>
I could not tell from the question whether this was network or not. I
agree in network then limiting execution probably is best. In embedded
I am not sure - I would not be surprised if doing 1000 in batch is
slower than just doing the executes.
In either case I really would stay away from string manipulation as much
as possible and also stay away from things that create very long SQL
statements like 1000 term values clauses.
Re: Large multi-record insert performance
Posted by Mike Matrigali <mi...@sbcglobal.net>.
Lance J. Andersen wrote:
>
>
> Even if the backend does not provide optimization for batch processing,
> i would hope that there would be still some efficiency especially in a
> networked environment vs building the strings, invoking execute() 1000
> times in the amount of data on the wire...
>
>
I could not tell from the question whether this was network or not. I
agree in network then limiting execution probably is best. In embedded
I am not sure - I would not be surprised if doing 1000 in batch is
slower than just doing the executes.
In either case I really would stay away from string manipulation as much
as possible and also stay away from things that create very long SQL
statements like 1000 term values clauses.
Re: Large multi-record insert performance
Posted by "Lance J. Andersen" <La...@Sun.COM>.
Even if the backend does not provide optimization for batch processing,
i would hope that there would be still some efficiency especially in a
networked environment vs building the strings, invoking execute() 1000
times in the amount of data on the wire...
Mike Matrigali wrote:
> Did you try the most straight forward approach, ie just do 1000
> inserts, using Derby in embedded mode? ie.:
>
> > PreparedStatement pstmt = connection.prepareStatement("INSERT INTO t
> > VALUES (?, ?, ?)");
> > for (int i=1; i<1000; i++) {
> > pstmt.setInt(1, i);
> > pstmt.setInt(2, i);
> > pstmt.setInt(3, i);
> > pstmt.execute();
> > }
> commit;
>
> I haven't done the comparison, but I don't think derby is going to do
> much optimization with the batch. I would definitely start there. The
> usual problem is the commit, so turning autocommit off and doing as
> many inserts as possible per commit is the best place to start.
>
> If you are not using embedded derby then network overhead may be more of
> a problem.
>
>
> Sedillo, Derek (Mission Systems) wrote:
>> Thomas,
>>
>> Thank you, I have not tried the batch feature yet. But you have given
>> me some great stuff to work with.
>>
>> I will have to try this and compare the timings to the single INSERT
>> string approach below. I may also have to adjust the commit number
>> up/down to achieve optimal performance.
>>
>> Iterate an array of 1000 records and create a VALUES clause string to
>> look like:
>> /String records = "(1,1,1), (2,2,2), (3,3,3), (4,4,4), …(1000, 1000,
>> 1000)";/
>> Then append the insert statement with the records and execute.
>> /String query = "INSERT INTO t VALUES " + records;/
>> /Execute query;/
>>
>> - Derek
>> ------------------------------------------------------------------------
>> *From:* Thomas J. Taylor [mailto:thomas.taylor@itqa.miami.edu]
>> *Sent:* Wednesday, March 14, 2007 12:01 PM
>> *To:* 'Derby Discussion'
>> *Subject:* RE: Large multi-record insert performance
>>
>> Hi Derek,
>>
>> Have you tried batch inserts?
>>
>> PreparedStatement pstmt = connection.prepareStatement("INSERT INTO t
>> VALUES (?, ?, ?)");
>> for (int i=1; i<1000; i++) {
>> pstmt.setInt(1, i);
>> pstmt.setInt(2, i);
>> pstmt.setInt(3, i);
>> pstmt.addBatch();
>> }
>> int[] results = pstmt.executeBatch();
>>
>> Thomas
>> ------------------------------------------------------------------------
>> *From:* Sedillo, Derek (Mission Systems) [mailto:Derek.Sedillo@ngc.com]
>> *Sent:* Wednesday, March 14, 2007 11:06 AM
>> *To:* derby-user@db.apache.org
>> *Subject:* Large multi-record insert performance
>>
>> Hello,
>>
>> I am trying to find the most efficient way to handle multi-records
>> inserts into a single table in Derby. Currently we use Oracle to
>> perform multi-record inserts with 'one' insert statement for large
>> amounts of data. This bulk loading process greatly enhances
>> performance. For example:
>>
>> /INSERT INTO SSJ
>> VALUES (:tmp_ssj_data); / //where* tmp_ssj_data* is an array (100s or
>> 1000s) of C structured records
>>
>> Is there any way in Java DB to perform something similar? So lets say
>> table t has three integer columns.
>> /INSERT INTO t VALUES (?,?,?)/
>> Now you have 1000 rows to insert into t that look like this:
>> (1,1,1), (2,2,2), (3,3,3), (4,4,4), …
>> How would you pass these 1000 records in as VALUES with a 'single'
>> insert statement?
>> I am thinking of two options so far:
>> 1. Use a prepared statement and 'set' all 1000 record values. Then
>> execute. But I question whether calling the set method for all 1000
>> records would be buy anything in terms of performance.
>>
>> 2. The other option would be to iterate an array of 1000 records and
>> create a VALUES clause string to look like:
>> /String records = "(1,1,1), (2,2,2), (3,3,3), (4,4,4), …(1000, 1000,
>> 1000)";/
>> Then append the insert statement with the records and execute.
>> /String query = "INSERT INTO t VALUES " + records;/
>> /Execute query;/
>>
>> Which method would be the most efficient way to perform these 1000
>> inserts in Derby? Is there a more optimized way to do this?
>>
>> Thank you for any experience or ideas you may have regarding large
>> data inserts.
>>
>> Derek Sedillo
>> SWAFS DBA / Software Engineer
>> *Northrop Grumman Missions Systems*
>> Tel: (719) 570-8256
>>
>>
>
RE: Large multi-record insert performance
Posted by "Sedillo, Derek (Mission Systems)" <De...@ngc.com>.
Dyre,
I apologize for the confussion. I wasn't quite sure what Daniel meant
with regards to:
> All in one transaction using executeUpdate():
> 100 tags added in 274ms
> 100 tags removed in 70ms
> 1000 tags added in 299ms
> 1000 tags removed in 250ms
> 10000 tags added in 1605ms
> 10000 tags removed in 1500ms
> 100000 tags added in 14441ms
> 100000 tags removed in 19721ms
In looking again at your proposed solution, this in fact may be what he
was doing.
> PreparedStatement ps =
> conn.prepareStatement("insert into T values (?, ?, ?)");
> conn.setAutoCommit(false);
> for (int i = 0; i < 1000; ++i) {
ps.setX(1, <whatever>);
ps.setX(2, <whatever>);
ps.setX(3, <whatever>);
ps.executeUpdate();
> }
> conn.commit();
With autoCommit off you are executing 1000 updates but only performing
'One' commit. If Daniel is equating transactions with commits then your
approach would be correct.
Thank you,
Derek
Re: Large multi-record insert performance
Posted by Dy...@Sun.COM.
"Sedillo, Derek (Mission Systems)" <De...@ngc.com> writes:
> Dyre,
>
> The goal is to find the most efficient/optimized way to insert large
> amounts of data into Derby. For example in working as an Oracle DBA I
> have discovered that I can bulk load data from ProC using an array of C
> Structures in one insert statement like this:
>
> INSERT INTO SSJ
> VALUES (:tmp_ssj_data); // Where tmp_ssj_data is an array (100s or
> 1000s) of structured records
>
> This approach greatly enhances performance for large data inserts which
> we perform regularly. My original question is how can I do something
> similar with Derby.
Not to my knowledge, no. I believe you can only bulk load from a file
(which I think others have already described).
> While I realize there are 'basic' constructs for performing a task, they
> are not 'normally' optimal. For example performing 1000 separate
> transactions is less efficient than one.
Not sure I understand what you mean here. You control how many inserts
you want to do between each commit...
> All in one transaction using executeUpdate():
> 100 tags added in 274ms
> 100 tags removed in 70ms
> 1000 tags added in 299ms
> 1000 tags removed in 250ms
> 10000 tags added in 1605ms
> 10000 tags removed in 1500ms
> 100000 tags added in 14441ms
> 100000 tags removed in 19721ms
100.000 inserts in 15 sec. That's too slow?
Then I don't have any answers I'm afraid...
--
dt
Re: Large multi-record insert performance
Posted by Daniel Noll <da...@nuix.com>.
Mike wrote:
> As you can see in the results Derby currently does not optimize the
> batch processing in embedded so if you are embedded I would use code
> similar to
> what is included below. The biggest performance win is going to be
> to insert as many rows as possible between each commit. Each commit
> will do a synchronous I/O and wait for it to hit disk, so on modern
> processors you will quickly become I/O wait bound unless you make
> commits big enough. "Big enough" depends on the data, for instance
> below it looks like the optimal number may be bigger than 100000,
> but the difference between 10k and 100k is not much (6250 rows/sec for
> 10k, and 6924 rows/sec).
There is a benefit to doing it all in a single commit anyway, namely that if
the process crashes, someone kills it, or the machine powers out, the
database will be in a state where it's as if nothing occurred.
I need to rerun these tests with a networked database but setting that up
isn't as trivial as a unit test. :-)
Also the benefits of doing it for networked mode probably vary greatly
depending on the network itself.
Daniel Noll
Nuix Pty Ltd
Suite 79, 89 Jones St, Ultimo NSW 2007, Australia Ph: +61 2 9280 0699
Web: http://nuix.com/ Fax: +61 2 9212 6902
This message is intended only for the named recipient. If you are not
the intended recipient you are notified that disclosing, copying,
distributing or taking any action in reliance on the contents of this
message or attachment is strictly prohibited.
Re: Large multi-record insert performance
Posted by Mike Matrigali <mi...@sbcglobal.net>.
Sedillo, Derek (Mission Systems) wrote:
> Dyre,
>
> The goal is to find the most efficient/optimized way to insert large
> amounts of data into Derby. For example in working as an Oracle DBA I
> have discovered that I can bulk load data from ProC using an array of C
> Structures in one insert statement like this:
>
> INSERT INTO SSJ
> VALUES (:tmp_ssj_data); // Where tmp_ssj_data is an array (100s or
> 1000s) of structured records
>
> This approach greatly enhances performance for large data inserts which
> we perform regularly. My original question is how can I do something
> similar with Derby.
>
> While I realize there are 'basic' constructs for performing a task, they
> are not 'normally' optimal. For example performing 1000 separate
> transactions is less efficient than one. See Daniel's numbers below.
I assume that Daniel's non-batch numbers below are generated using
code similar to that at then end of this message, ie. set autocommit to
false, loop doing inserts, commit after some number of inserts. Derby
is different than other db's when using it in embedded mode, some
optimizations necessary in client/server applications are not necessary
in Derby.
As you can see in the results Derby currently does not optimize the
batch processing in embedded so if you are embedded I would use code
similar to
what is included below. The biggest performance win is going to be
to insert as many rows as possible between each commit. Each commit
will do a synchronous I/O and wait for it to hit disk, so on modern
processors you will quickly become I/O wait bound unless you make
commits big enough. "Big enough" depends on the data, for instance
below it looks like the optimal number may be bigger than 100000,
but the difference between 10k and 100k is not much (6250 rows/sec for
10k, and 6924 rows/sec).
> Along these lines I did some testing for our own application.
>
> Cached PreparedStatement but one transaction per insert:
> 100 tags added in 399ms
> 100 tags removed in 160ms
> 1000 tags added in 1163ms
> 1000 tags removed in 873ms
> 10000 tags added in 6094ms
> 10000 tags removed in 6840ms
> 100000 tags added in 58563ms
> 100000 tags removed in 67342ms
>
> All in one transaction using executeUpdate():
> 100 tags added in 274ms
> 100 tags removed in 70ms
> 1000 tags added in 299ms
> 1000 tags removed in 250ms
> 10000 tags added in 1605ms
> 10000 tags removed in 1500ms
> 100000 tags added in 14441ms
> 100000 tags removed in 19721ms
>
> All in one transaction using addBatch()/executeBatch():
> 100 tags added in 290ms
> 100 tags removed in 76ms
> 1000 tags added in 316ms
> 1000 tags removed in 258ms
> 10000 tags added in 1621ms
> 10000 tags removed in 1927ms
> 100000 tags added in 14971ms
> 100000 tags removed in 19320ms
>
> - Derek
>
> -----Original Message-----
> From: Dyre.Tjeldvoll@Sun.COM [mailto:Dyre.Tjeldvoll@Sun.COM]
> Sent: Friday, March 16, 2007 9:00 AM
> To: Derby Discussion
> Subject: Re: Large multi-record insert performance
>
> I haven't followed this thread closely, but is there a reason why you
> cannot do:
>
> PreparedStatement ps =
> conn.prepareStatement("insert into T values (?, ?, ?)");
>
> conn.setAutoCommit(false);
> for (int i = 0; i < 1000; ++i) {
> ps.setX(1, <whatever>);
> ps.setX(2, <whatever>);
> ps.setX(3, <whatever>);
> ps.executeUpdate();
> }
> conn.commit();
this is the code I would start with for iterative inserts in embedded
systems, with the loop as big as makes sense for the application.
>
> ?
>
> --
> dt
>
>
RE: Large multi-record insert performance
Posted by "Sedillo, Derek (Mission Systems)" <De...@ngc.com>.
Dyre,
The goal is to find the most efficient/optimized way to insert large
amounts of data into Derby. For example in working as an Oracle DBA I
have discovered that I can bulk load data from ProC using an array of C
Structures in one insert statement like this:
INSERT INTO SSJ
VALUES (:tmp_ssj_data); // Where tmp_ssj_data is an array (100s or
1000s) of structured records
This approach greatly enhances performance for large data inserts which
we perform regularly. My original question is how can I do something
similar with Derby.
While I realize there are 'basic' constructs for performing a task, they
are not 'normally' optimal. For example performing 1000 separate
transactions is less efficient than one. See Daniel's numbers below.
Along these lines I did some testing for our own application.
Cached PreparedStatement but one transaction per insert:
100 tags added in 399ms
100 tags removed in 160ms
1000 tags added in 1163ms
1000 tags removed in 873ms
10000 tags added in 6094ms
10000 tags removed in 6840ms
100000 tags added in 58563ms
100000 tags removed in 67342ms
All in one transaction using executeUpdate():
100 tags added in 274ms
100 tags removed in 70ms
1000 tags added in 299ms
1000 tags removed in 250ms
10000 tags added in 1605ms
10000 tags removed in 1500ms
100000 tags added in 14441ms
100000 tags removed in 19721ms
All in one transaction using addBatch()/executeBatch():
100 tags added in 290ms
100 tags removed in 76ms
1000 tags added in 316ms
1000 tags removed in 258ms
10000 tags added in 1621ms
10000 tags removed in 1927ms
100000 tags added in 14971ms
100000 tags removed in 19320ms
- Derek
-----Original Message-----
From: Dyre.Tjeldvoll@Sun.COM [mailto:Dyre.Tjeldvoll@Sun.COM]
Sent: Friday, March 16, 2007 9:00 AM
To: Derby Discussion
Subject: Re: Large multi-record insert performance
I haven't followed this thread closely, but is there a reason why you
cannot do:
PreparedStatement ps =
conn.prepareStatement("insert into T values (?, ?, ?)");
conn.setAutoCommit(false);
for (int i = 0; i < 1000; ++i) {
ps.setX(1, <whatever>);
ps.setX(2, <whatever>);
ps.setX(3, <whatever>);
ps.executeUpdate();
}
conn.commit();
?
--
dt
Re: Large multi-record insert performance
Posted by Dy...@Sun.COM.
"Sedillo, Derek (Mission Systems)" <De...@ngc.com> writes:
> Hello Daniel,
>
> Thank you for sharing your finding. It is interesting to see that using
> executeBatch was actually slower than executeUpdate.
>
> Of these three approaches I find it hard to picture multiple inserts in
> a single transaction using executeUpdate. Would you mind describing how
> to place multiple inserts into one transaction 'without' using batch
> processing? I am still a bit new to this paradigm after coming from the
> Oracle/C++ world.
I haven't followed this thread closely, but is there a reason why you
cannot do:
PreparedStatement ps =
conn.prepareStatement("insert into T values (?, ?, ?)");
conn.setAutoCommit(false);
for (int i = 0; i < 1000; ++i) {
ps.setX(1, <whatever>);
ps.setX(2, <whatever>);
ps.setX(3, <whatever>);
ps.executeUpdate();
}
conn.commit();
?
--
dt
Re: Large multi-record insert performance
Posted by Daniel Noll <da...@nuix.com>.
Derek said:
> Thank you for sharing your finding. It is interesting to see that using
> executeBatch was actually slower than executeUpdate.
I'm not confident enough to say whether this is really the case as the
margin of error is greater than the difference in speed. I would have to
run the same test a few thousand times and take an average, before telling
whether batching is really slower or faster.
Daniel Noll
Nuix Pty Ltd
Suite 79, 89 Jones St, Ultimo NSW 2007, Australia Ph: +61 2 9280 0699
Web: http://nuix.com/ Fax: +61 2 9212 6902
This message is intended only for the named recipient. If you are not
the intended recipient you are notified that disclosing, copying,
distributing or taking any action in reliance on the contents of this
message or attachment is strictly prohibited.
RE: Large multi-record insert performance
Posted by "Sedillo, Derek (Mission Systems)" <De...@ngc.com>.
Hello Daniel,
Thank you for sharing your finding. It is interesting to see that using
executeBatch was actually slower than executeUpdate.
Of these three approaches I find it hard to picture multiple inserts in
a single transaction using executeUpdate. Would you mind describing how
to place multiple inserts into one transaction 'without' using batch
processing? I am still a bit new to this paradigm after coming from the
Oracle/C++ world.
Thanks again,
Derek
-----Original Message-----
From: Daniel Noll [mailto:daniel@nuix.com]
Sent: Thursday, March 15, 2007 5:27 PM
To: Derby Discussion
Subject: Re: Large multi-record insert performance
> using prepared statement + batch will be your fastest approach even
> tough you pass 1000 records per query derby has to still parse that
> query whereas with prepared statements they are parsed once. and if
> you are using batch all your data will be passed at once so derby will
> make better use of your disk drive( instead of flushing index after
> each insert it will do it once for all batch this was the case in
> mysql i maybe wrong for derby)
Along these lines I did some testing for our own application.
Cached PreparedStatement but one transaction per insert:
100 tags added in 399ms
100 tags removed in 160ms
1000 tags added in 1163ms
1000 tags removed in 873ms
10000 tags added in 6094ms
10000 tags removed in 6840ms
100000 tags added in 58563ms
100000 tags removed in 67342ms
All in one transaction using executeUpdate():
100 tags added in 274ms
100 tags removed in 70ms
1000 tags added in 299ms
1000 tags removed in 250ms
10000 tags added in 1605ms
10000 tags removed in 1500ms
100000 tags added in 14441ms
100000 tags removed in 19721ms
All in one transaction using addBatch()/executeBatch():
100 tags added in 290ms
100 tags removed in 76ms
1000 tags added in 316ms
1000 tags removed in 258ms
10000 tags added in 1621ms
10000 tags removed in 1927ms
100000 tags added in 14971ms
100000 tags removed in 19320ms
So it certainly seems like batching in itself has no real benefit but
reducing the number of transactions has dramatic benefits.
Of course the numbers themselves are meaningless without redoing the
same test on your own application.
Daniel Noll
Nuix Pty Ltd
Suite 79, 89 Jones St, Ultimo NSW 2007, Australia Ph: +61 2 9280 0699
Web: http://nuix.com/ Fax: +61 2 9212 6902
This message is intended only for the named recipient. If you are not
the intended recipient you are notified that disclosing, copying,
distributing or taking any action in reliance on the contents of this
message or attachment is strictly prohibited.
Re: Large multi-record insert performance
Posted by Daniel Noll <da...@nuix.com>.
Kristian said:
> How often did you issue a commit in this test?
> (and was auto-commit enabled or disabled)
As my original message stated, it did each insert in a different
transaction.
i.e. one commit per insert.
The latter tests just did the commit once after all the inserts were done.
We actually contain this all automatically, we have a utility method which
we call and pass a callback to, which handles the transaction for us so that
we don't need to do commit/rollback everywhere. (The method also handles
retries if something bad happens.)
Daniel Noll
Nuix Pty Ltd
Suite 79, 89 Jones St, Ultimo NSW 2007, Australia Ph: +61 2 9280 0699
Web: http://nuix.com/ Fax: +61 2 9212 6902
This message is intended only for the named recipient. If you are not
the intended recipient you are notified that disclosing, copying,
distributing or taking any action in reliance on the contents of this
message or attachment is strictly prohibited.
Re: Large multi-record insert performance
Posted by Kristian Waagan <Kr...@Sun.COM>.
Daniel Noll wrote:
>
>> using prepared statement + batch will be your fastest approach even
>> tough you pass 1000 records per query derby has to still parse that
>> query whereas with prepared statements they are parsed once. and if
>> you are using batch all your data will be passed at once so derby
>> will make better use of your disk drive( instead of flushing index
>> after each insert it will do it once for all batch this was the case
>> in mysql i maybe wrong for derby)
>
> Along these lines I did some testing for our own application.
>
> Cached PreparedStatement but one transaction per insert:
> 100 tags added in 399ms
> 100 tags removed in 160ms
> 1000 tags added in 1163ms
> 1000 tags removed in 873ms
> 10000 tags added in 6094ms
> 10000 tags removed in 6840ms
> 100000 tags added in 58563ms
> 100000 tags removed in 67342ms
Hi Daniel,
How often did you issue a commit in this test?
(and was auto-commit enabled or disabled)
--
Kristian
>
> All in one transaction using executeUpdate():
> 100 tags added in 274ms
> 100 tags removed in 70ms
> 1000 tags added in 299ms
> 1000 tags removed in 250ms
> 10000 tags added in 1605ms
> 10000 tags removed in 1500ms
> 100000 tags added in 14441ms
> 100000 tags removed in 19721ms
>
> All in one transaction using addBatch()/executeBatch():
> 100 tags added in 290ms
> 100 tags removed in 76ms
> 1000 tags added in 316ms
> 1000 tags removed in 258ms
> 10000 tags added in 1621ms
> 10000 tags removed in 1927ms
> 100000 tags added in 14971ms
> 100000 tags removed in 19320ms
>
> So it certainly seems like batching in itself has no real benefit but
> reducing the number of transactions has dramatic benefits.
>
> Of course the numbers themselves are meaningless without redoing the
> same test on your own application.
>
> Daniel Noll
>
> Nuix Pty Ltd
> Suite 79, 89 Jones St, Ultimo NSW 2007, Australia Ph: +61 2 9280 0699
> Web: http://nuix.com/ Fax: +61 2 9212 6902
>
> This message is intended only for the named recipient. If you are not
> the intended recipient you are notified that disclosing, copying,
> distributing or taking any action in reliance on the contents of this
> message or attachment is strictly prohibited.
>
Re: Large multi-record insert performance
Posted by Daniel Noll <da...@nuix.com>.
> using prepared statement + batch will be your fastest approach even
> tough you pass 1000 records per query derby has to still parse that
> query whereas with prepared statements they are parsed once. and if
> you are using batch all your data will be passed at once so derby
> will make better use of your disk drive( instead of flushing index
> after each insert it will do it once for all batch this was the case
> in mysql i maybe wrong for derby)
Along these lines I did some testing for our own application.
Cached PreparedStatement but one transaction per insert:
100 tags added in 399ms
100 tags removed in 160ms
1000 tags added in 1163ms
1000 tags removed in 873ms
10000 tags added in 6094ms
10000 tags removed in 6840ms
100000 tags added in 58563ms
100000 tags removed in 67342ms
All in one transaction using executeUpdate():
100 tags added in 274ms
100 tags removed in 70ms
1000 tags added in 299ms
1000 tags removed in 250ms
10000 tags added in 1605ms
10000 tags removed in 1500ms
100000 tags added in 14441ms
100000 tags removed in 19721ms
All in one transaction using addBatch()/executeBatch():
100 tags added in 290ms
100 tags removed in 76ms
1000 tags added in 316ms
1000 tags removed in 258ms
10000 tags added in 1621ms
10000 tags removed in 1927ms
100000 tags added in 14971ms
100000 tags removed in 19320ms
So it certainly seems like batching in itself has no real benefit but
reducing the number of transactions has dramatic benefits.
Of course the numbers themselves are meaningless without redoing the same
test on your own application.
Daniel Noll
Nuix Pty Ltd
Suite 79, 89 Jones St, Ultimo NSW 2007, Australia Ph: +61 2 9280 0699
Web: http://nuix.com/ Fax: +61 2 9212 6902
This message is intended only for the named recipient. If you are not
the intended recipient you are notified that disclosing, copying,
distributing or taking any action in reliance on the contents of this
message or attachment is strictly prohibited.
Re: Large multi-record insert performance
Posted by Nurullah Akkaya <nu...@yahoo.com>.
using prepared statement + batch will be your fastest approach even
tough you pass 1000 records per query derby has to still parse that
query whereas with prepared statements they are parsed once. and if
you are using batch all your data will be passed at once so derby
will make better use of your disk drive( instead of flushing index
after each insert it will do it once for all batch this was the case
in mysql i maybe wrong for derby) using prepared statements and batch
of 100 000 in embedded mode on a amd 1800 1 gb ram ide hard drive i
can achive up to 500 ( 15byte) inserts per second all day long.( i
even have composite index on the table that includes all the columns
so they are pretty much written twice.)
On Mar 15, 2007, at 12:24 AM, Mike Matrigali wrote:
> Did you try the most straight forward approach, ie just do 1000
> inserts, using Derby in embedded mode? ie.:
>
> > PreparedStatement pstmt = connection.prepareStatement("INSERT INTO t
> > VALUES (?, ?, ?)");
> > for (int i=1; i<1000; i++) {
> > pstmt.setInt(1, i);
> > pstmt.setInt(2, i);
> > pstmt.setInt(3, i);
> > pstmt.execute();
> > }
> commit;
>
> I haven't done the comparison, but I don't think derby is going to do
> much optimization with the batch. I would definitely start there.
> The
> usual problem is the commit, so turning autocommit off and doing as
> many inserts as possible per commit is the best place to start.
>
> If you are not using embedded derby then network overhead may be
> more of
> a problem.
>
>
> Sedillo, Derek (Mission Systems) wrote:
>> Thomas,
>> Thank you, I have not tried the batch feature yet. But you have
>> given me some great stuff to work with.
>> I will have to try this and compare the timings to the single
>> INSERT string approach below. I may also have to adjust the
>> commit number up/down to achieve optimal performance.
>> Iterate an array of 1000 records and create a VALUES clause
>> string to look like:
>> /String records = "(1,1,1), (2,2,2), (3,3,3), (4,4,4), …(1000,
>> 1000, 1000)";/
>> Then append the insert statement with the records and execute.
>> /String query = "INSERT INTO t VALUES " + records;/
>> /Execute query;/
>> - Derek
>> ---------------------------------------------------------------------
>> ---
>> *From:* Thomas J. Taylor [mailto:thomas.taylor@itqa.miami.edu]
>> *Sent:* Wednesday, March 14, 2007 12:01 PM
>> *To:* 'Derby Discussion'
>> *Subject:* RE: Large multi-record insert performance
>> Hi Derek,
>> Have you tried batch inserts?
>> PreparedStatement pstmt = connection.prepareStatement("INSERT
>> INTO t VALUES (?, ?, ?)");
>> for (int i=1; i<1000; i++) {
>> pstmt.setInt(1, i);
>> pstmt.setInt(2, i);
>> pstmt.setInt(3, i);
>> pstmt.addBatch();
>> }
>> int[] results = pstmt.executeBatch();
>> Thomas
>> ---------------------------------------------------------------------
>> ---
>> *From:* Sedillo, Derek (Mission Systems)
>> [mailto:Derek.Sedillo@ngc.com]
>> *Sent:* Wednesday, March 14, 2007 11:06 AM
>> *To:* derby-user@db.apache.org
>> *Subject:* Large multi-record insert performance
>> Hello,
>> I am trying to find the most efficient way to handle multi-records
>> inserts into a single table in Derby. Currently we use Oracle to
>> perform multi-record inserts with 'one' insert statement for large
>> amounts of data. This bulk loading process greatly enhances
>> performance. For example:
>> /INSERT INTO SSJ
>> VALUES (:tmp_ssj_data); / //where* tmp_ssj_data* is an array (100s
>> or 1000s) of C structured records
>> Is there any way in Java DB to perform something similar? So lets
>> say table t has three integer columns.
>> /INSERT INTO t VALUES (?,?,?)/
>> Now you have 1000 rows to insert into t that look like this:
>> (1,1,1), (2,2,2), (3,3,3), (4,4,4), …
>> How would you pass these 1000 records in as VALUES with a 'single'
>> insert statement?
>> I am thinking of two options so far:
>> 1. Use a prepared statement and 'set' all 1000 record values.
>> Then execute. But I question whether calling the set method
>> for all 1000 records would be buy anything in terms of performance.
>> 2. The other option would be to iterate an array of 1000 records
>> and create a VALUES clause string to look like:
>> /String records = "(1,1,1), (2,2,2), (3,3,3), (4,4,4), …(1000,
>> 1000, 1000)";/
>> Then append the insert statement with the records and execute.
>> /String query = "INSERT INTO t VALUES " + records;/
>> /Execute query;/
>> Which method would be the most efficient way to perform these 1000
>> inserts in Derby? Is there a more optimized way to do this?
>> Thank you for any experience or ideas you may have regarding large
>> data inserts.
>> Derek Sedillo
>> SWAFS DBA / Software Engineer
>> *Northrop Grumman Missions Systems*
>> Tel: (719) 570-8256
>
Re: Large multi-record insert performance
Posted by Mike Matrigali <mi...@sbcglobal.net>.
Did you try the most straight forward approach, ie just do 1000 inserts,
using Derby in embedded mode? ie.:
> PreparedStatement pstmt = connection.prepareStatement("INSERT INTO t
> VALUES (?, ?, ?)");
> for (int i=1; i<1000; i++) {
> pstmt.setInt(1, i);
> pstmt.setInt(2, i);
> pstmt.setInt(3, i);
> pstmt.execute();
> }
commit;
I haven't done the comparison, but I don't think derby is going to do
much optimization with the batch. I would definitely start there. The
usual problem is the commit, so turning autocommit off and doing as
many inserts as possible per commit is the best place to start.
If you are not using embedded derby then network overhead may be more of
a problem.
Sedillo, Derek (Mission Systems) wrote:
> Thomas,
>
> Thank you, I have not tried the batch feature yet. But you have given
> me some great stuff to work with.
>
> I will have to try this and compare the timings to the single INSERT
> string approach below. I may also have to adjust the commit number
> up/down to achieve optimal performance.
>
> Iterate an array of 1000 records and create a VALUES clause string to
> look like:
> /String records = "(1,1,1), (2,2,2), (3,3,3), (4,4,4), …(1000, 1000,
> 1000)";/
> Then append the insert statement with the records and execute.
> /String query = "INSERT INTO t VALUES " + records;/
> /Execute query;/
>
> - Derek
> ------------------------------------------------------------------------
> *From:* Thomas J. Taylor [mailto:thomas.taylor@itqa.miami.edu]
> *Sent:* Wednesday, March 14, 2007 12:01 PM
> *To:* 'Derby Discussion'
> *Subject:* RE: Large multi-record insert performance
>
> Hi Derek,
>
> Have you tried batch inserts?
>
> PreparedStatement pstmt = connection.prepareStatement("INSERT INTO t
> VALUES (?, ?, ?)");
> for (int i=1; i<1000; i++) {
> pstmt.setInt(1, i);
> pstmt.setInt(2, i);
> pstmt.setInt(3, i);
> pstmt.addBatch();
> }
> int[] results = pstmt.executeBatch();
>
> Thomas
> ------------------------------------------------------------------------
> *From:* Sedillo, Derek (Mission Systems) [mailto:Derek.Sedillo@ngc.com]
> *Sent:* Wednesday, March 14, 2007 11:06 AM
> *To:* derby-user@db.apache.org
> *Subject:* Large multi-record insert performance
>
> Hello,
>
> I am trying to find the most efficient way to handle multi-records
> inserts into a single table in Derby. Currently we use Oracle to
> perform multi-record inserts with 'one' insert statement for large
> amounts of data. This bulk loading process greatly enhances
> performance. For example:
>
> /INSERT INTO SSJ
> VALUES (:tmp_ssj_data); / //where* tmp_ssj_data* is an array (100s or
> 1000s) of C structured records
>
> Is there any way in Java DB to perform something similar? So lets say
> table t has three integer columns.
> /INSERT INTO t VALUES (?,?,?)/
> Now you have 1000 rows to insert into t that look like this:
> (1,1,1), (2,2,2), (3,3,3), (4,4,4), …
> How would you pass these 1000 records in as VALUES with a 'single'
> insert statement?
> I am thinking of two options so far:
> 1. Use a prepared statement and 'set' all 1000 record values. Then
> execute.
> But I question whether calling the set method for all 1000 records
> would be buy anything in terms of performance.
>
> 2. The other option would be to iterate an array of 1000 records and
> create a VALUES clause string to look like:
> /String records = "(1,1,1), (2,2,2), (3,3,3), (4,4,4), …(1000, 1000,
> 1000)";/
> Then append the insert statement with the records and execute.
> /String query = "INSERT INTO t VALUES " + records;/
> /Execute query;/
>
> Which method would be the most efficient way to perform these 1000
> inserts in Derby? Is there a more optimized way to do this?
>
> Thank you for any experience or ideas you may have regarding large data
> inserts.
>
> Derek Sedillo
> SWAFS DBA / Software Engineer
> *Northrop Grumman Missions Systems*
> Tel: (719) 570-8256
>
>
RE: Large multi-record insert performance
Posted by "Sedillo, Derek (Mission Systems)" <De...@ngc.com>.
Thomas,
Thank you, I have not tried the batch feature yet. But you have given
me some great stuff to work with.
I will have to try this and compare the timings to the single INSERT
string approach below. I may also have to adjust the commit number
up/down to achieve optimal performance.
Iterate an array of 1000 records and create a VALUES clause string to
look like:
String records = "(1,1,1), (2,2,2), (3,3,3), (4,4,4), ...(1000, 1000,
1000)";
Then append the insert statement with the records and execute.
String query = "INSERT INTO t VALUES " + records;
Execute query;
- Derek
________________________________
From: Thomas J. Taylor [mailto:thomas.taylor@itqa.miami.edu]
Sent: Wednesday, March 14, 2007 12:01 PM
To: 'Derby Discussion'
Subject: RE: Large multi-record insert performance
Hi Derek,
Have you tried batch inserts?
PreparedStatement pstmt = connection.prepareStatement("INSERT INTO t
VALUES (?, ?, ?)");
for (int i=1; i<1000; i++) {
pstmt.setInt(1, i);
pstmt.setInt(2, i);
pstmt.setInt(3, i);
pstmt.addBatch();
}
int[] results = pstmt.executeBatch();
Thomas
________________________________
From: Sedillo, Derek (Mission Systems) [mailto:Derek.Sedillo@ngc.com]
Sent: Wednesday, March 14, 2007 11:06 AM
To: derby-user@db.apache.org
Subject: Large multi-record insert performance
Hello,
I am trying to find the most efficient way to handle multi-records
inserts into a single table in Derby. Currently we use Oracle to
perform multi-record inserts with 'one' insert statement for large
amounts of data. This bulk loading process greatly enhances
performance. For example:
INSERT INTO SSJ
VALUES (:tmp_ssj_data); //where tmp_ssj_data is an array (100s or
1000s) of C structured records
Is there any way in Java DB to perform something similar? So lets say
table t has three integer columns.
INSERT INTO t VALUES (?,?,?)
Now you have 1000 rows to insert into t that look like this:
(1,1,1), (2,2,2), (3,3,3), (4,4,4), ...
How would you pass these 1000 records in as VALUES with a 'single'
insert statement?
I am thinking of two options so far:
1. Use a prepared statement and 'set' all 1000 record values. Then
execute.
But I question whether calling the set method for all 1000 records
would be buy anything in terms of performance.
2. The other option would be to iterate an array of 1000 records and
create a VALUES clause string to look like:
String records = "(1,1,1), (2,2,2), (3,3,3), (4,4,4), ...(1000, 1000,
1000)";
Then append the insert statement with the records and execute.
String query = "INSERT INTO t VALUES " + records;
Execute query;
Which method would be the most efficient way to perform these 1000
inserts in Derby? Is there a more optimized way to do this?
Thank you for any experience or ideas you may have regarding large data
inserts.
Derek Sedillo
SWAFS DBA / Software Engineer
Northrop Grumman Missions Systems
Tel: (719) 570-8256