RE: Performance Tuning of batch insert and batch update using JDBC API.
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.aspx?icid=chick_wlhmtextlink1_dec