Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2003
    Posts
    8

    Post Unanswered: Why db2 is taking so long to perform batch insert?

    Hi,
    i was performing some batch insertion into db2 and it took around ten minutes to complete 38752 insertions. When i perform the same operation on MSSQL, it did it in less than 10 seconds.

    Is there any settings that I have to make to achieve the same performance?

    Thanks in advance.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Please post the DDL of the table and indexes. Also show the SQL used for the insert, and describe what method you are using to submit the SQL, icluding isolation level, auto-commit on/off, etc.

  3. #3
    Join Date
    Nov 2002
    Location
    vienna
    Posts
    9
    The BCP Utility usually uses minimal logging
    you can get near the same with the db2 sql statement
    LOAD FROM <file> OF <format> INSERT INTO <table> NONRECOVERABLE;

  4. #4
    Join Date
    Sep 2003
    Posts
    8
    Hi,

    in both DB2 and MSSQL, there were no indexes specified and primary keys used and my C# .NET program calls the same Stored Procedures such as:

    call SucxentStorageManager.SP_INSERT_LEAF_NODE ......;
    ...
    ...
    dbCommand.ExecuteNonQuery();


    after setting Autocommit to turned off. now the insertion time is reduced to 4 mins.

    the default table space was used (4K page size). i wonder if there's any optimisation to the db2 settings such that it parred with MSSQL.

  5. #5
    Join Date
    Nov 2002
    Location
    vienna
    Posts
    9
    with ALTER TABLE ... APPEND ON insert can be made faster

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    See if the recommendations in this thread are useful at all

    http://dbforums.com/arch/8/2002/6/393899

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

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    "there were no indexes specified and primary keys used"

    Does this mean that a primary key was used, or was not used? If a primary key is defined, then DB2 automatically creates a unique index for the key.

    If a primary key was used, and new rows are being inserted in the middle of the existing range of keys, you may need to increase the percent free of the index. You will also need to perform periodic reorgs of the table/index, followed by runstats (at least one time).

    I would use the Performance Wizard on the Control Center to modify the database configuration parameters. For most database servers, allocate at least 50% of system memory to DB2. You can fine tune the parameters after that if needed.

    As stated in the link provided by Sathyaram, check everything having to do with logging (although these settings may be initially optimized by the Performance Wizard).

  8. #8
    Join Date
    Sep 2003
    Posts
    8
    Hi,
    thanks for all the help.

    i have tried all possible methods from the help list but the performance is still far apart from mssql. there were 38752 stored procedures calls to perform insertion at one go. no indexes and no primary keys for both cases. mssql completes in 1 min. db2 completes in 10 mins.

    autocommit turn off.
    dms tablespace created for the table.
    logfilsz 2500
    mincommit 12
    logbufsz 256
    dbheap 300
    activate not logged initially for the table.


    what went wrong then...? thanks in advance.

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by justdavid77
    Hi,
    thanks for all the help.

    i have tried all possible methods from the help list but the performance is still far apart from mssql. there were 38752 stored procedures calls to perform insertion at one go. no indexes and no primary keys for both cases. mssql completes in 1 min. db2 completes in 10 mins.

    autocommit turn off.
    dms tablespace created for the table.
    logfilsz 2500
    mincommit 12
    logbufsz 256
    dbheap 300
    activate not logged initially for the table.


    what went wrong then...? thanks in advance.
    Might that be because of stored proc calls overhead? 38 thousand calls... What SPs did you use in either case: Java? SQL?
    ---
    "It does not work" is not a valid problem statement.

  10. #10
    Join Date
    Sep 2003
    Posts
    8
    sql stored procedures. why does this work so well for mssql?

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by justdavid77
    sql stored procedures. why does this work so well for mssql?
    As far as I understand, in MS SQL Server a SQL stored procedure is just this - a SQL statement; it is compiled once on the first use and executed from the statement cache thereafter. This in particular means that it runs within the main thread of the database server.

    On the other hand, in DB2 a SQL SP is converted into C source that uses CLI function calls and then compiled into an object code fragment. If the SP is created as FENCED then the object code would run as a separate process, basically communicating with the server as any other local application. This may add some overhead, which would be especially noticeable in case of a small procedure containing just one or two SQL statements.

    For valid comparison you should probably use an "extended stored procedure" in MS SQL and a NOT FENCED SQL stored proc in DB2.

    Hope this helps.

    Nick
    ---
    "It does not work" is not a valid problem statement.

  12. #12
    Join Date
    Sep 2003
    Posts
    8

    Smile

    i have checked that all the stored procedures are NOT FENCED. Thanks for the advise anyway.

Posting Permissions

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