Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    7

    Unanswered: insert performance

    A few basic ( stupid! ) questions:
    1. does having an index on a table help speed inserts into that table in any way? 2. does having PDQ set for a session doing inserts help speed the inserts in any way?
    3. how come my insert batch jobs slow down when I insert records into a table that users are doing read only lookups over...I feel like it makes sense that it does slow down, I would just like someone to spell it out for me in a technical sense.

  2. #2
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    183
    I hope I have some answers for you:

    1. Having an index will slow the insert instead of making it faster. The index need to be maintained also. Records are not stored ordered or something like that, so no benefit in that.

    2. PDQ might help somewhat, if the insert is a select...insert statement. Just doing a lot of inserts after each other will not be faster with PDQ. Every insert is dealt with seperately and sequentially.

    3. Reading and writing from the some table, is raising the locing on one object. Also of course reads on the bitmap pages etc. is done at the same time., This are just some explanations there might be more.

    There are some extra things you can try, speeding up the insert:
    A. Create your table fragmented round robin, this will speed up the I/O
    B. Use an insert cursor (PUT) instead of single inserts. I've seen batchjobs run 10x faster using cursors.

    Hope this helps,

    Rob Prop

  3. #3
    Join Date
    May 2004
    Posts
    7

    thanks for not calling me a doofus

    thanks for not calling me a doofus

Posting Permissions

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