-
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
-
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.
-
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"
-
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
-
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"
-
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
-
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
-
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?
-
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"
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|