Results 1 to 8 of 8

Thread: Slow Inserts

  1. #1
    Join Date
    Apr 2004
    Posts
    190

    Unanswered: Slow Inserts

    We have an application that inserts 50 rows per calls and it takes about 1 second to insert the 50 rows. We thought we could speed up the application so it was changed to insert 200 rows per call and now it takes 10 seconds to insert the 200 rows. Any idea why its taking 10 times longer with the change ?

    DB21085I Instance "db2inst2" uses "64" bits and DB2 code release "SQL09053"
    with level identifier "06040107".
    Informational tokens are "DB2 v9.5.0.3", "special_22945", "U818976_22945", and
    Fix Pack "3".
    Product is installed at "/cs/IBM/db2/V9.5".

    Thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    How big are the rows? How many indexes on the table? What sort of tablespace and pagesize? Can you set APPEND ON for the table?

    Andy

  3. #3
    Join Date
    Apr 2004
    Posts
    190
    How big are the rows? - 1200 bytes
    How many indexes on the table? - 5
    What sort of tablespace and pagesize? - 4K db managed
    Can you set APPEND ON for the table? - yes

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    For 1200 bytes per row:
    Page size Rows per page
    ---------------------------------
    4K 3.41
    8K 6.71
    16K 13.6
    32K 27.3

    You can only get whole rows on a page, so the fractional part is wasted space. You would get better rows per page with a 32K page size. The fewer pages you need, the faster the insert will go. You would also have less wasted space.

    5 indexes may or may not be a problem. What do they look like?

    Andy

  5. #5
    Join Date
    Apr 2004
    Posts
    190
    Create unique index liq.xru1_tps_gl_intfc
    on liq.tps_gl_intfc(liq_gl_ent_id_c)
    disallow reverse scans page split symmetric
    ;
    create index liq.xru2_tps_gl_intfc
    on liq.tps_gl_intfc(ps_gl_divsn_id_c,liq_gl_ent_cur_c ,ps_gl_dept_id_c,liq_ptflo_exp_c,liq_gl_ent_sht_n, liq_gl_ent_val_d,liq_bus_d)
    disallow reverse scans page split symmetric
    ;
    create index liq.xru3_tps_gl_intfc
    on liq.tps_gl_intfc(liq_gl_ent_val_d,liq_gl_ent_sht_n ,liq_src_row_id_c)
    disallow reverse scans page split symmetric
    ;
    create index liq.xru4_tps_gl_intfc
    on liq.tps_gl_intfc(liq_deal_id_c,liq_gl_ent_val_d,li q_gl_acct_id_c)
    disallow reverse scans page split symmetric
    ;
    create index liq.xru5_tps_gl_intfc
    on liq.tps_gl_intfc(liq_intfc_grp_id_c,liq_bus_d)
    disallow reverse scans page split symmetric
    ;

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What are the data types or the columns?

    Andy

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by redwolf View Post
    Any idea why its taking 10 times longer with the change ?
    The only way to know for sure is to look at the snapshots.

    Enable monitor switches for locks, sorts, statements, and bufferpools, reset monitor data, then take a series of database snapshots with, say, 30 second intervals while running your application. You can then post 2-3 representative snapshots here for people to review.
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Another thing I thought of is that doing 50 rows, your statement is 60,000 bytes long. For 200 rows, the statement is 240,000 bytes. The amount of time to compile the statement grows also and it is not necessarily linear.

    Another place to look is logging. Your log buffer is probably too small for 200 rows, but OK for 50. If the buffer is too small, you wait while the log is flushed to disk.

    Andy

Posting Permissions

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