Hi All,
I am trying to Insert as well as Update 1000,000 records to a
table using JAVA JDBC Batch Update API's .
Where my batch size is 10,000.

Informix 10.0 is taking approx 350-400 secs for insertin of 1000,000
records.
The same is taking 20-25 secs in DB2 9.

Wanna tune this.

Following is the table structure:

CREATE TABLE ELEPCIS.SERIAL_ID (
RESOURCE_ID INTEGER NOT NULL ,
SERIAL_ID INT8 NOT NULL ,
STATUS INTEGER DEFAULT 0,
REQUEST_ID INTEGER DEFAULT 1) ;

ALTER TABLE ELEPCIS.SERIAL_ID
ADD CONSTRAINT PRIMARY KEY
(RESOURCE_ID,
SERIAL_ID) CONSTRAINT CC119033234;

ALTER TABLE ELEPCIS.SERIAL_ID
ADD CONSTRAINT FOREIGN KEY
(REQUEST_ID)
REFERENCES ELEPCIS.REQUEST
(REQUEST_ID) CONSTRAINT CC11800424368;


Thanks
Dj

djain13@gmail.com wrote:
> Hi All,
> I am trying to Insert as well as Update 1000,000 records to a
> table using JAVA JDBC Batch Update API's .
> Where my batch size is 10,000.
>
> Informix 10.0 is taking approx 350-400 secs for insertin of 1000,000
> records.
> The same is taking 20-25 secs in DB2 9.
>
> Wanna tune this.
>
> Following is the table structure:
>
> CREATE TABLE ELEPCIS.SERIAL_ID (
> RESOURCE_ID INTEGER NOT NULL ,
> SERIAL_ID INT8 NOT NULL ,
> STATUS INTEGER DEFAULT 0,
> REQUEST_ID INTEGER DEFAULT 1) ;
>
> ALTER TABLE ELEPCIS.SERIAL_ID
> ADD CONSTRAINT PRIMARY KEY
> (RESOURCE_ID,
> SERIAL_ID) CONSTRAINT CC119033234;
>
> ALTER TABLE ELEPCIS.SERIAL_ID
> ADD CONSTRAINT FOREIGN KEY
> (REQUEST_ID)
> REFERENCES ELEPCIS.REQUEST
> (REQUEST_ID) CONSTRAINT CC11800424368;
>
>
> Thanks
> Dj
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>

well, there are a great many things you don't say. for instance do you alter
the table and add the constraints after the inserts, or before? the latter
would severely impact performance.
what are the extent sizes?
do you use insert cursors (can you in java?)?
are you using statement cache?
are you repreparing the statement every time?
is your jdbc client connecting from a remote client? maybe you need some
communications layer tuning?
is there anything else going on on this instance? on this box?
are the IDS and DB2 instances and boxes comparable?

in short, this might help you:

http://www.catb.org/~esr/faqs/smart-questions.html

--
Ciao,
Marco
__________________________________________________ ____________________________
Marco Greco /UK /IBM Standard disclaimers apply!

Structured Query Scripting Language http://www.4glworks.com/sqsl.htm
4glworks http://www.4glworks.com
Informix on Linux http://www.4glworks.com/ifmxlinux.htm

In addition to Marco's comments, how exactly are you doing the DB2 9
load?... Is it truly an insert or is it a "load" operation that is "not
logged"?.... Bypassing transaction logging can surely save time.
================================================== ==========
The information contained in this message may be privileged
and confidential and protected from disclosure. If the reader
of this message is not the intended recipient, or an employee
or agent responsible for delivering this message to the
intended recipient, you are hereby notified that any reproduction,
dissemination or distribution of this communication is strictly
prohibited. If you have received this communication in error,
please notify us immediately by replying to the message and
deleting it from your computer. Thank you. Tellabs
================================================== ==========

I think Marco hit the nail on the head.

If you have the same table structure, same java application...
your only differences are on the layout and tuning of the engine, and the jdbc driver. (Unless you're using DRDA....)

The differences in the JDBC driver wouldn't cause this big of a time difference.

So that leave your tuning of the engine....
> Date: Mon, 24 Dec 2007 07:54:08 +0000> From: marco@4glworks.com> To: djain13@gmail.com; informix-list@iiug.org> Subject: Re: Performance Tuning of batch insert and batch update using JDBC API.> > djain13@gmail.com wrote:> >Hi All,> > I am trying to Insert as well as Update 1000,000 records to a> > table using JAVA JDBC Batch Update API's .> > Where my batch size is 10,000.> > > > Informix 10.0 is taking approx 350-400 secs for insertin of 1000,000> > records.> > The same is taking 20-25 secs in DB2 9.> > > > Wanna tune this.> > > > Following is the table structure:> > > > CREATE TABLE ELEPCIS.SERIAL_ID (> > RESOURCE_ID INTEGER NOT NULL ,> > SERIAL_ID INT8 NOT NULL,> > STATUS INTEGER DEFAULT 0,> > REQUEST_ID INTEGER DEFAULT 1) ;> > > > ALTER TABLE ELEPCIS.SERIAL_ID> > ADD CONSTRAINT PRIMARY KEY> > (RESOURCE_ID,> > SERIAL_ID) CONSTRAINT CC119033234;> > > > ALTER TABLE ELEPCIS.SERIAL_ID> > ADD CONSTRAINT FOREIGN KEY> > (REQUEST_ID)> > REFERENCES ELEPCIS.REQUEST> > (REQUEST_ID) CONSTRAINT CC11800424368;> > > > > > Thanks> > Dj> > _______________________________________________> > Informix-list mailing list> > Informix-list@iiug.org> > http://www.iiug.org/mailman/listinfo/informix-list> > > > well, there are a great many things you don't say. for instance do you alter> the table and add the constraints after the inserts, or before? the latter> would severely impact performance.> what are the extent sizes?> do you use insert cursors (can you in java?)?> are you using statement cache?> are you repreparing the statement every time?> is your jdbc client connecting from a remote client? maybe you need some> communications layer tuning?> is there anything else going on on this instance? on this box?> are the IDS and DB2 instances and boxes comparable?> > in short, this might help you:> > http://www.catb.org/~esr/faqs/smart-questions.html> > -- > Ciao,> Marco> __________________________________________________ ____________________________> Marco Greco /UK /IBM Standard disclaimers apply!> > Structured Query Scripting Language http://www.4glworks.com/sqsl.htm> 4glworks http://www.4glworks.com> Informix on Linux http://www.4glworks.com/ifmxlinux.htm> _______________________________________________> Informix-list mailing list> Informix-list@iiug.org> http://www.iiug.org/mailman/listinfo/informix-list
__________________________________________________ _______________
Don't get caught with egg on your face. Play Chicktionary!
http://club.live.com/chicktionary.as...mtextlink1_dec