Results 1 to 2 of 2
  1. #1
    Join Date
    May 2002
    Posts
    15

    Unanswered: insert performance

    Hi,

    I am trying to acheive high volume insert performance. I know that the number of indexes will have a significant impact on Insert performance. Can any one tell me the other factors that need to be considered which impact the Insert performance. I am new to this area so any help would be greatly appreciated.

    Thanks

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: insert performance

    Here are a few points I can think of:

    (1) If your workload is mostly INSERT then you can ALTER TABLE for APPEND ON

    (2) Use DMS Tablespaces. If you are using SMS, enable multipage allocation using db2empfa

    (3) Increase the log buffer size . By default it is 32 k .. you can use 256,512 or 1024

    (4) Avoid transmitting data over the network. Try to have the source data on the server

    (5) Use parameter markers in your INSERT Statement and also multirow inserts

    (6) If using JDBC2, use batch inserts ... Your performance will improve dramatically

    (7) Do not commit for every row ... or investigate putting up the mincommit database cfg parameter

    (8) Use COMPOUND SQL if possible

    (9) Check parameters like IO ServersmNum IO Cleaners, Change page threshold, Bufferpool size etc

    (10) Set the striped containers and parallel io registry variables. the former has to be set before creating the tablespace

    (11) REORG the tablespace to update the free space information for the table pages. (i'm not 100% sure about this)

    Remember that Trigger, RI, Check Constraints, Generated Columns etc will have an impact on your INSERT performance.

    HTH ...

    Cheers

    Sathyaram



    Originally posted by huskers
    Hi,

    I am trying to acheive high volume insert performance. I know that the number of indexes will have a significant impact on Insert performance. Can any one tell me the other factors that need to be considered which impact the Insert performance. I am new to this area so any help would be greatly appreciated.

    Thanks

Posting Permissions

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