Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    1

    Unhappy Unanswered: Reorg during update?

    Hi

    We have a Java application that inserts a certain amount of data into a set of tables.

    After the insert the data is processed one row a a time and inserts are made into the main tables...

    Our problem is that on a mySQL DB this takes about 1 1/2 hours and
    on our DB2 about 14 hours! It seems that the problem is that the indexes
    on the first set of tables are not updated efter the insert therefore the
    updates takes too long to perform.

    We guess that this could be fixed by running a reorg or update statistics but we cant figure out how to do it between the two jobs!

    The JDBC-driver does'nt accept the reorg commands so we cant do it through
    the application.

    Does anone have any ideas???

    Sincerely
    Stefan Sidholm
    Sweden

  2. #2
    Join Date
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64
    Hi!

    Inserting and updating data can be extremely demanding... Especially, if you have lot of indexes and/or triggers involved. But also JDBC-trafic can be the bottleneck.

    Indexes ARE updated, but they can be really mixed up by lot of new update. And having REORG between two jobs is no option. What to do?

    1) Have material in right order. Sort input material (if you can), so that insert are done only in same order as tables cluster-index is. This will minimize i/o operations.
    2) Can you use TEMP-tables? Do first insert into TEMP-table and then do further inserting by "INSERT INTO xxxx SELECT ... FROM TEMP" -SQL-sentence. This way indexes are not hurting initial insert and secondary insert are done INSIDE the DB2 (no data traveling trough JDBC-interface).

    There are more tricks, but first you must find out, which element is causing slowness. For instance, take indexes away and try then to do insert. Is it faster? And keep it in mind, sequential i/o is x100 faster than random i/o.

    Cheers, Bill

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    What tablespace are you using ? Using DMS will improve performance ... If you wish to use SMS only, then enable multipage file allocation using the db2empfa command ...

    There is a recent article on Developer Domain on insert performance ... You can search in www7b.boulder.ibm.com/dmdd

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by sidkolia
    Hi

    We have a Java application that inserts a certain amount of data into a set of tables.

    After the insert the data is processed one row a a time and inserts are made into the main tables...

    Our problem is that on a mySQL DB this takes about 1 1/2 hours and
    on our DB2 about 14 hours! It seems that the problem is that the indexes
    on the first set of tables are not updated efter the insert therefore the
    updates takes too long to perform.

    We guess that this could be fixed by running a reorg or update statistics but we cant figure out how to do it between the two jobs!

    The JDBC-driver does'nt accept the reorg commands so we cant do it through
    the application.

    Does anone have any ideas???

    Sincerely
    Stefan Sidholm
    Sweden
    JDBC - set autocommit off, then do commits ever N records.

    Better still - use the batch capabilities:

    PreparedStatement pstmt =
    con.prepareStatement("INSERT INTO XXX values (?, ?)");
    for (int i=0; i<ITER; i++) {
    pstmt.setString(1, "STRING" + i);
    pstmt.setString(2, "another string");
    pstmt.addBatch();
    }
    int[] res = pstmt.executeBatch();
    con.commit();

    Keep the batch sizes reasonable, 1000 rows for small ones, less if the rows are big... but I've found a big performance gain, especially if the app is running remotely.
    --
    Jonathan Petruk
    DB2 Database Consultant

Posting Permissions

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