Results 1 to 6 of 6
  1. #1
    Join Date
    May 2006
    Posts
    3

    Unanswered: How to Efficiently Insert 20K records at a time??

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    May 2006
    Posts
    3
    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

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why can't you use LOAD RESUME?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    May 2006
    Posts
    3
    Data is generated in the Application, Can I use load to store the data from J2ee application to DB??. I guess not.. Please confirm..

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •