performance with INSERT/EXCEPTION/UPDATE pattern - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: performance with INSERT/EXCEPTION/UPDATE pattern

  1. #11
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by chrisrlong
    Which, as I said, is simply in the wrong order You don't want to insert the records *before* the update, or you will end up updating those very same records (as well as the others), right? You always do the update before the insert. My next post was only applicable if bulk statements could not be done, as I specified at the beginning of said post.
    Ok now I get you, yes ofcourse Update then Insert should be performed, else unneccesary updates.



    Originally posted by chrisrlong

    Okay, so in my haste, I went a *little* too far, but not by much.
    Code:
    INSERT -- TEST02
    INTO JUNK
    SELECT * from JUNK WHERE PK = 1
    
    INSERT -- TEST02
    INTO JUNK
    SELECT * from JUNK WHERE PK = 1
    AND NOT EXISTS(SELECT 1 FROM JUNK WHERE PK = 1)
    Compare the buffer gets on the two statements. The second one is much more efficient. Therefore, If more inserts:

    INSERT WHERE NOT EXISTS
    IF SQL%ROWCOUNT = 0
    THEN
    UPDATE...

    However, the same does not appear to apply to updates

    In this case, both statements run exactly the same.
    So, if more updates, simply:

    UPDATE ...
    IF SQL%ROWCOUNT = 0
    THEN
    INSERT...

    - Chris
    Ultimate!!
    Now, it wud be interesting to see Jurij's reply
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  2. #12
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Now after additional explanation it makes much more sence to me. (Maybe the fact that I haven't yet had any beer today has something to do with that?)

    However,

    Originally posted by chrisrlong
    Okay, so in my haste, I went a *little* too far, but not by much.
    Code:
    DROP TABLE JUNK
    
    CREATE TABLE JUNK AS SELECT ROWNUM PK, T.* FROM ALL_OBJECTS T
    
    CREATE UNIQUE INDEX JUNK_PK ON JUNK(PK)
    
    ANALYZE TABLE JUNK COMPUTE STATISTICS
    
    INSERT -- TEST02
    INTO JUNK
    SELECT * from JUNK WHERE PK = 1
    
    INSERT -- TEST02
    INTO JUNK
    SELECT * from JUNK WHERE PK = 1
    AND NOT EXISTS(SELECT 1 FROM JUNK WHERE PK = 1)
    How many beers have you had, Chris? No, seriously, you were again a bit hasty with the above example, dont you think? The first select will always raise ORA-00001 "unique constraint violated" (I wonder how could you get logical reads from that select) and the second will never ever insert any rows, don't you think

    Anyway, I agree with your conclusions.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #13
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Originally posted by jmodic
    The first select will always raise ORA-00001 "unique constraint violated" (I wonder how could you get logical reads from that select) and the second will never ever insert any rows, don't you think
    That's the point, actually. When you compare 2 successful inserts, adding an exists adds 1 LR. When comparing 2 that fail (and you can find both in the statement cache), adding the exists saves 13 LRs. This means that you would have to have 13 times as many inserts as updates for my method to lose, and this is with a small, simple table with a single index.

    I wish I could find the code where I actually implemented this not too long ago - I know it save a good chunk of time but I can't find the bloody thing.

    Oh well :(

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  4. #14
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by chrisrlong
    That's the point, actually
    No, I think you are missing my point, Chris! I don't object to your conclusions, I'm just saying that both selects that you've provided to demonstraint the effect on inserts are (excuse the expression) - braindead!

    INSERT 1:
    Code:
    INSERT -- TEST02
    INTO JUNK
    SELECT * from JUNK WHERE PK = 1
    With this, you are telling oracle to select a record from a table (that is unique index-ed) and without modifying that record to insert it in that same table again! You are violating the unique index so you'll get ORA-00001 - so what are you trying to show here? What is it good to compare the LRs for a query that will always return error message to any other query? To prove how many logical reads you need to get an ORA-00001?

    INSERT 2:
    Code:
    INSERT -- TEST02
    INTO JUNK
    SELECT * from JUNK WHERE PK = 1
    AND NOT EXISTS(SELECT 1 FROM JUNK WHERE PK = 1)
    It's more than obvious that the query will never ever return any rows, so what's the point here? You could (logic-vise) as well written the above as:

    Code:
    INSERT -- TEST02
    INTO JUNK
    SELECT * from JUNK WHERE PK = 1
    AND     EXISTS(SELECT 1 FROM JUNK WHERE PK = 1)
    AND NOT EXISTS(SELECT 1 FROM JUNK WHERE PK = 1)
    or even more simpler:
    Code:
    INSERT -- TEST02
    INTO JUNK
    SELECT * from JUNK WHERE PK = 1
    AND 1=2
    Unfortunately the optimizer is not clever enough (at least not on 8i that I'm connected to just now) to find out from your original query itself that its WHEN clause can never result in TRUE, so indeed it makes some reads from the database. But it is clever enough to resolve this from my query (with "1=2" part in WHERE clause, which is logicaly totaly equivalent to yours) and doesn't even try to perform any database reads!

    So I guess I'v just found the fastest insert statement possible - it doesn't nead a single logical read. However I don't know how one would use that statement in any comparison test as it is useles.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #15
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    I'm sorry. I must be explaining myself poorly. Bear with me.

    I *know* that the statements I used failed. Again, that was the point. Assume we have a set of records in TableA that we want to INSERT or UPDATE in TableB in 8i. If we know that we usually have more records inserted than updated, we would essentially try an insert on each record, *and if that failed*, perform an update.

    Now, this means that a certain percentage of inserts will succeed and a certain percentage will fail. I simply attempted to show that the standard INSERT/SELECT had, if I remember, 10 LRs for a successful INSERT and 15 for a failed INSERT. I then attempted to show that my version of INSERT/SELECT/WHERE NOT EXISTS had, if I remember, 11 LRs for a succesful INSERT and 2 for a failed INSERT.

    To show that, I had to craft an INSERT statement of each type that failed.
    I knew they failed.
    I meant for them to fail

    Again, that is because a certain number of the INSERTs *were going to fail*. I also had ones that didn't fail, so I could get stats on them as well. I also did the same thing with explicitely listed columns and various other permutations, etc. A more accurate set of statements would have involved 2 tables and some records that existed in both the Source and Destination versus ones that existed in the Source only and use bind variables in the statements, etc., but I was too lazy for that. And I didn't want to waste a lot of space with a lot of code when the premise could be shown simply with the statements I chose.

    The premise being that waiting for a Unique Index to catch a duplicate record costs a lot more than adding a NOT EXISTS clause. The NOT EXISTS is an 'early out' scenario compared to the backend test of unique indexes.

    So, my version costs slightly more for those records that didn't exist in TableB, but is LOTS cheaper for those records that ARE already in TableB, as some will be.

    Did I make sense that time?

    - Chris
    Last edited by chrisrlong; 08-07-2003 at 10:01 PM.
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

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