| Performance issue [message #23349] |
Tue, 03 December 2002 21:18  |
Andrew G.
Messages: 7
Registered: November 2002
|
Junior Member |
|
|
Hi,
I am wondering what kind of performance difference can I expect from these two methods of processing:
A) Using a straight statement
UPDATE small_table
SET status = 'invalid'
WHERE EXISTS
(SELECT *
FROM large_table
WHERE small_table.id = large_table.id);
B) Using a cursor
DECLARE CURSOR cursor ...
FOR ....
UPDATE small_table
SET status = 'invalid'
WHERE CURRENT OF cursor
AND EXISTS
(SELECT *
FROM large_table
WHERE cursor.id = large_table.id);
END LOOP;
The small_table would have circa 5000-10000 records and the big one 1-10 million. Using method B) would enable me to do some other things in the processing a lot easier but I still think the performance would be much worse than in A), mainly for context switching and efficiency of looking up indexes. Am I right? However, I've no clue about the actual performance, if it was only, say 20% then I would definitely go with B).
Thanks
Andrew
|
|
|
|
| Re: Performance issue [message #23352 is a reply to message #23349] |
Tue, 03 December 2002 23:41  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
In general, the single statement approach will be faster and consume fewer resources than a procedural (PL/SQL, cursor loop) option.
As long as you have an index on large_table(id), this should be a fairly quick operation with either approach (although your SQL in option 2 needs the EXISTS clause in the cursor definition, not in the loop).
Without knowing what your "other things" are, I would always at least start with the single statement approach.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
