Hi All,
Currently we are using hibernate to perform insert, which is taking approximately 80 Secs to insert 20K records at a time. I want to reduce this as fast as possible. The table I am inserting has 15 columns. Please advise me how do I increase the performance of this insert.
Thank You in advance.
Dhani

If you can't use a LOAD or IMPORT command (LOAD is faster) then try using this syntax to insert faster:

INSERT INTO DEPARTMENT (DEPTNO, DEPTNAME, ADMRDEPT)
VALUES
(’B11’, ’PURCHASING’, ’B01’),
(’E41’, ’DATABASE ADMINISTRATION’, ’E01’)
etc.
etc.

Insert about 100 rows at once and do a commit, then next 100, etc.

Make the following changes to the db configuration (db2 get db cfg):

Increase the logbufsz to 256.
Increase dbheap by 256 (logbufsz comes out of dbheap).
Increase the num_iocleaners at least 4.
set chngpgs_thresh to 10 (no lower than 20).
set logfilesz to at least 10000.

Thank you for your suggestion. When I was searching in google, I found out that I should not use static insert stm but I should use parameterized statement to increase the performance. Also I can not change any of the DB configuration because it will impact the other queries. Is it possible to change these parameters through JDBC just for this query??.
Please let me know your suggestion.
Thank You

Why can't you use LOAD RESUME?

Data is generated in the Application, Can I use load to store the data from J2ee application to DB??. I guess not.. Please confirm..

What do you mean by generated?

Is this a batch process that spits out 20k rows? Or is it OLTP?

If it is batch, I would write it to a file, then I would load....but then again, I'm not an application grunt...ok, so I was, but why aren't you using stored procedures...I mean the data must be coming from somewhere