Performance Tuning of batch insert and batch update using JDBC API.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

djain13@gmail.com - 24 Dec 2007 07:14 GMT

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

Reply to this Message

Marco Greco - 24 Dec 2007 07:54 GMT

> Hi All,
> I am trying to Insert as well as Update 1000,000 records to a
[quoted text clipped - 32 lines]
> 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

Signature

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

Reply to this Message

Sebastian, Norma J. - 26 Dec 2007 21:35 GMT

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
============================================================

Reply to this Message