Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    1

    Unanswered: Sybase Insert Low Performance

    Hello!

    I am using large database Sybase ASE 12.5 on 2-machine cluster, with average load 50%. My application is connecting to it from different host on 100Mbps network. Sorry, can`t provide anymore informaton on setup, i just don`t know.

    I write a program in Java using JConnect 5.2
    which inserts 20000+ rows in table.

    I have tried different ways to do it,
    - autocommit off,
    - prepared statements,
    - DYNAMIC_PREPARE = on
    - batches (all the rows in one execute, or calling execute, say, every 1000 rows).

    hovewer, I get very low performance,
    for example, most of the time I get 500 rows inserted in more than 3 seconds.

    Then I increase number of rows in batch mode for which executeBatch is called, i get non-linear increase of executeBatch() call.

    My questions is:
    - is this situation normal?
    - that is the typical performance of Sybase?
    - that is the most efficient way to send batches? rows per executeBatch?
    - how to import to table FAST, not using insert statement?

    Thanks for help.

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    The fastest way to bulk insert is with the BCP utility. Note that to achive maximum performance you should drop all the indexes on the table before loading.
    Thanks,

    Matt

  3. #3
    Join Date
    Mar 2003
    Location
    Vienna
    Posts
    24

    Arrow

    Don't forget to set the db_option to allow bulk insert of data while using bcp. Another insert performance gain could be achived when binding the transaction log to a named cache.

    Eg.

    sp_cacheconfig NAME_OF_CACHE, "SIZE", logonly
    use master
    /* before binding the cache to the logs */
    sp_dboption NAME_OF_YOUR_DB, single, true
    use NAME_OF_YOUR_DB
    checkpoint
    sp_bindcache "NAME_OF_CACHE", NAME_OF_YOUR_DB, syslogs
    use master
    sp_dboption NAME_OF_YOUR_DB, single, false
    use NAME_OF_YOUR_DB
    checkpoint

    Memory setting should be adjusted before playing with cache. You can also cahche the entiure table, then it will be in memory => it will be fast
    Last edited by clarus; 09-09-03 at 12:25.
    kdb is the solution of all problems.
    Just try it, www.kx.com

  4. #4
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    There might also be last page contentions on the table. Try using multi-threaded BCPs with partitions on the table.

    Thanks,
    Vishi.

  5. #5
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    If it is just INSERT's, drop all indexes on this table for the time of INSERT.

Posting Permissions

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