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

Thread: performance with INSERT/EXCEPTION/UPDATE pattern

  1. #1
    Join Date
    Aug 2003
    Posts
    2

    performance with INSERT/EXCEPTION/UPDATE pattern

    I often use the logic below to load data from a query into a table. First I try to insert the row, and if it violates the pk constraint of the table I do an update instead.

    It seems that performance can be really slow when I'm dealing with a large number of updates. Maybe a factor of 10 longer than the case where it's mostly inserts. Is there a better way to perform this type of operation?

    ...
    FOR some_cursor_rec IN some_cursor LOOP
    INSERT INTO some_table(...
    EXCEPTION WHEN OTHERS THEN
    BEGIN
    UPDATE some_table...
    END;
    COMMIT;
    END LOOP;
    ...

    Thanks

  2. #2
    Join Date
    Aug 2003
    Posts
    2
    Trying the update first, then doing the insert if SQL%ROWCOUNT is 0, seems to be a bit faster for heavy update cases, but still significantly slower than the same logic for heavy insert cases. I can't figure out why this is.

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    If Oracle Ver 9.2, use Merge ( its the fastest ).

    Abhay.
    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"

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Another approach would be to code it as a bulk operation, performing an insert, catching exceptions, and updating based on those exceptions.

    There's an example that you could use as a starting point here
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by slimdave
    Another approach would be to code it as a bulk operation, performing an insert, catching exceptions, and updating based on those exceptions.
    If bulk operations can be done wass the need of "catching exceptions" in some other/temp table?

    can be done in jus 2 statements Insert(Not exists clause) & Update(Exists clause).


    Abhay.
    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"

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Originally posted by abhaysk
    can be done in jus 2 statements Insert(Not exists clause) & Update(Exists clause)
    But the other way around, of course.

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

  7. #7
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Another point, assuming it can't be done in bulk.

    As you mentioned, it helps to have an idea as to whether you will have more updates or inserts. And I don't see what there is to figure out there - it's kinda obvious.

    Anyway, the point is, if more updates:

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

    If more inserts:

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

    Using the EXISTS or NOT EXISTS is more efficient than waiting for the exception.

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

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by chrisrlong
    Another point, assuming it can't be done in bulk.

    As you mentioned, it helps to have an idea as to whether you will have more updates or inserts. And I don't see what there is to figure out there - it's kinda obvious.

    Anyway, the point is, if more updates:

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

    If more inserts:

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

    Using the EXISTS or NOT EXISTS is more efficient than waiting for the exception.

    - Chris
    I swear I've only had one beer today, but I'm totaly lost here. I'm not sure what are you talking about here.

    You are talking about update/insert of a record by record inside cursor, right? If so, why checking for existance at all? I mean, ROWCOUNT will tell if it exists or not, no?

    You are not talking about the same concept that slimdave has suggested, are you? Or have I totaly and 100% miss some important point here?

    I realy am confused.....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by chrisrlong
    But the other way around, of course.
    - Chris
    Chris :
    I didnt get what you wanted to tell, but lemme explain what I thought

    SQL%ROWCOUNT is not at all needed...(yes if using cursors, but not needed in bulk loads)

    jus see below eg

    Code:
    Insert into X select %Attribute_List% From Y 
    Where Not Exists (Select 1 From X Where PK_Of_X=Cols_Of_Y ( Or Sub Query ) )
    ;
    
    Update X Set (COLx1,COLx2,...) = (Select %Attribute_List% From Y Where Cols_Of_Y ( Or Sub Query )=PK_Of_X)
    Where Exists
    (Select 1 from Y Where Cols_Of_Y ( Or Sub Query )=PK_Of_X)
    ;
    Abhay.
    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"

  10. #10
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Originally posted by abhaysk
    Chris :
    I didnt get what you wanted to tell, but lemme explain what I thought

    SQL%ROWCOUNT is not at all needed...(yes if using cursors, but not needed in bulk loads)

    jus see below eg

    Code:
    Insert into X select %Attribute_List% From Y 
    Where Not Exists (Select 1 From X Where PK_Of_X=Cols_Of_Y ( Or Sub Query ) )
    ;
    
    Update X Set (COLx1,COLx2,...) = (Select %Attribute_List% From Y Where Cols_Of_Y ( Or Sub Query )=PK_Of_X)
    Where Exists
    (Select 1 from Y Where Cols_Of_Y ( Or Sub Query )=PK_Of_X)
    ;
    Abhay.
    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.

    Originally posted by jmodic
    I swear I've only had one beer today, but I'm totaly lost here. I'm not sure what are you talking about here.

    You are talking about update/insert of a record by record inside cursor, right? If so, why checking for existance at all? I mean, ROWCOUNT will tell if it exists or not, no?

    You are not talking about the same concept that slimdave has suggested, are you? Or have I totaly and 100% miss some important point here?

    I realy am confused.....
    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)
    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
    Code:
    UPDATE -- TEST06
    JUNK	J
    SET 
    	(PK,	OWNER,	OBJECT_NAME,	SUBOBJECT_NAME,	OBJECT_ID,
    	DATA_OBJECT_ID,	OBJECT_TYPE,	CREATED,	LAST_DDL_TIME,
    	TIMESTAMP,	STATUS,	TEMPORARY,	GENERATED,	SECONDARY
    	)	=
    	(	
    	SELECT
    		PK,	OWNER,	OBJECT_NAME,	SUBOBJECT_NAME,	OBJECT_ID,
    		DATA_OBJECT_ID,	OBJECT_TYPE,	CREATED,	LAST_DDL_TIME,
    		TIMESTAMP,	STATUS,	TEMPORARY,	GENERATED,	SECONDARY
    	FROM
    		JUNK
    	WHERE
    		PK	=	J.PK
    	)
    WHERE
    	PK	=	100000
    
    UPDATE -- TEST06
    JUNK J
    SET 
    	(PK,	OWNER,	OBJECT_NAME,	SUBOBJECT_NAME,	OBJECT_ID,
    	DATA_OBJECT_ID,	OBJECT_TYPE,	CREATED,	LAST_DDL_TIME,
    	TIMESTAMP,	STATUS,	TEMPORARY,	GENERATED,	SECONDARY
    	)	=
    	(	
    	SELECT
    		PK,	OWNER,	OBJECT_NAME,	SUBOBJECT_NAME,	OBJECT_ID,
    		DATA_OBJECT_ID,	OBJECT_TYPE,	CREATED,	LAST_DDL_TIME,
    		TIMESTAMP,	STATUS,	TEMPORARY,	GENERATED,	SECONDARY
    	FROM
    		JUNK
    	WHERE
    		PK	=	J.PK
    	)
    WHERE
    	PK	=	100000
    AND NOT EXISTS(SELECT 1 FROM JUNK WHERE PK	=	J.PK)
    In this case, both statements run exactly the same.
    So, if more updates, simply:

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

    - Chris
    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