Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2010
    Posts
    8

    Question Unanswered: Sybase best way to bulk insert/update from proc

    Frenz,
    We have a proc which dynamically creates inserts or updates and runs each sql in a cursor for each records. This process is taking hours. Logic wise it is very simple. No complex logic at all. As i mentioned, in the cursor we just create these insert update sqls and use dynamic sql ( exec ) to run that.
    Formation of sql is lengthy and we can not avoid cursors.
    What would be best way to do that from your experience? Please advice. Thanks.

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Show me with a sample why you think you can't avoid the cursor

  3. #3
    Join Date
    Sep 2010
    Posts
    8

    Question Sybase best way to bulk insert/update from proc

    Thanks for taking a look at my question. It is a lot of code and would be overwhelming to put in here.
    But considering that can not be removed, is there any way to run the bulk sqls or load data if i construct
    sqls dynamically in the code. And it is not just the inserts, but there are updates as well.
    And to your point, let's say we do not have any cursor, but still all these dynamically created queries needs to run, right?
    Like 10,000 inserts or updates

  4. #4
    Join Date
    Jun 2010
    Posts
    51
    How many rows on average are being inserted/updated by dynamic sqls?

    Do you have index on the tables being updated?
    Please always reply to the post if it was helpful. Others may find it helpful.

  5. #5
    Join Date
    Sep 2010
    Posts
    8

    Sybase best way to bulk insert/update from proc

    it depends, but i would assume may be around 3K to 5K records could be inserted/updated daily. Yes, it has an index on a id column which is unique.

  6. #6
    Join Date
    Jun 2010
    Posts
    51
    Use your sp to output the dynamic sql (don't execute the dynamic sql) and then run it separately with set showplan on for updates. This will tell you if indexes are being used or not.
    For inserts, do you have multiple index on the table you are inserting? Indexes usually slow down insert operation.
    Please always reply to the post if it was helpful. Others may find it helpful.

Tags for this Thread

Posting Permissions

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