DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Insertion speed with Indexed tables

  1. #1
    Join Date
    Jul 2004
    Posts
    7

    Insertion speed with Indexed tables

    I've been asked to look at a piece of code for an application that has been taking longer to run recently. The code was developed by outside contractors, so I don't really have anyone to ask why things were written the way they were. I have a couple questions though, and you guys always have some great answers, so I thought I'd try and post them here.

    Basically, this is what is happening:
    - Receive a flat file of data weekly
    - Truncate some tables, drop indexes/sequences
    - Recreate sequences
    - Load data from file into tables (~3,500,000 rows processed, 1 hr)
    - Recreate some indexes (ASC type)
    - Load data from existing table into table loaded and indexed above
    (~50,000 rows processed, 25hr)
    - Recreate remaining indexes

    Previously, the whole process ran in about 15hr - first load processed 2,077,000 rows in 30 min and second load processed 49,500 rows in 14.5hr. This was acceptable since it ran overnight and finished by morning.

    Okay, now to my question. I believe the indexes are created between the two loads because a search of the records is done before insertion so that duplicate/similar entries are not entered. Could creating these indexes actually be hurting performance since updates to the indexes also have to be made? Also, two indexes are created between the loads which correspond to select statement criteria used in the second load. There is another select statement used, but the index corresponding the the criteria in this statement is not created until the very end. Any thoughts on why it might have been done that way?

    I realize that not seeing the code leaves a lot of speculation, but I guess I'm just hoping for some general ideas and past experience teachings. BTW, I'm working with Oracle 8.1.7.2.1 Thanks for any info!

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    I refuse to believe that updating the indexes reduces 3'500'000 rows per hour to 2'000 rows per hour! Yes, the inserts will take longer - but my guess is that the selection logic (e.g. eliminating duplicates) is killing you.

    I'd hack out the "select" statements that are being used and time them to confirm or refute my guess. Then tune them. e.g. Are the tables ANALYZED after the load? Does creating the missing index help? What does explain plan say? etc etc etc

    When that's clear, you can determine which indexes don't need to be built before the second load.

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    It sounds as if the second load is done row-by-row ... is that right? Each row is individually processed?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by slimdave
    It sounds as if the second load is done row-by-row ... is that right? Each row is individually processed?
    Even so . . . TWO SECONDS PER ROW!

    There aren't any triggers created, are there?

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by DaPi
    Even so . . . TWO SECONDS PER ROW!

    There aren't any triggers created, are there?
    Hey, I could easily code something up that took two seconds per row
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by slimdave
    Hey, I could easily code something up that took two seconds per row
    'scuse us while we pop off into "Obfuscation Unlimited" for a dumb code dual. Queries at dawn. Coffee for two, breakfast for one.

  7. #7
    Join Date
    Jul 2004
    Posts
    7
    Yes, the second data 'load' (probably wasn't the best term to use) is a row-by-row insertion. And no, it is not just a "see if there's a row already in the table like the current"....it does involve a bit of logic. I agree that the logic is probably the reason for the majority of the slowdown. I was just trying to determine how big of an impact the indexes might have. There is a test environment that I hope I can take advantage of at some point, but no development area...so I have to coordinate with the workflow staff for testing.

    No triggers are created and there are not any triggers present already. Nothing has been analyzed in this database in the past, but we are thinking about doing so in the future.

    On a side note, you guys are hilarious!..lol

  8. #8
    Join Date
    Oct 2002
    Posts
    807
    Originally posted by babe9c
    I was just trying to determine how big of an impact the indexes might have.
    Trace it. See for yourself.

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    My suggestion to "hack out the selects" is only half of the analysis: Why not measure the insertion time?
    Insert a 1000 rows with a PL/SQL loop or one of Tamils's techniques: http://www.dbasupport.com/forums/sho...threadid=44411
    and time it with and without the indexes.

  10. #10
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Originally posted by babe9c
    Yes, the second data 'load' (probably wasn't the best term to use) is a row-by-row insertion. And no, it is not just a "see if there's a row already in the table like the current"....it does involve a bit of logic. I agree that the logic is probably the reason for the majority of the slowdown.
    If the second load involves logic where you do a select count(*) to see if a row exists then that could explain why this takes so long. select count(*) by their very nature are costly.

Posting Permissions

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


Click Here to Expand Forum to Full Width