| Performance question [message #20326] |
Mon, 13 May 2002 12:26  |
Ratan
Messages: 2
Registered: May 2002
|
Junior Member |
|
|
Hi listers,
I have a performance related question.
I have a PL/SQL package within which I have some procedures.
Now, I have various cursors in the different procedures in the package.
My question is :
If I have to make use of few functions to return some data for the procedure
to process, then
is it correct to call the functions within the "SELECT..." statement of the
cursors or
is it correct to initialise number of variables for each function call
within the cursor loop processing ?
Which process is most efficient with respect to memory management as well as
speed of execution.
Below is an example of the two scenarios :
Scenario 1:
----------------
CURSOR a is
SELECT
a,b,c, function1(a), function1(b)
FROM
XYZ
WHERE
....
.....
FOR cur_rec in a
loop
IF cur_rec.a = 'X'
THEN
...
...
END LOOP;
Scenario 2
---------------:
CURSOR a is
SELECT a,b,c
FROM
XYZ
WHERE
....
.... ;
v_a table_name.column_name%TYPE
FOR cur_rec in a
LOOP
v_a := function1(cursor_variable.a);
IF v_a = 'X'
THEN
....
...
...
END LOOP;
In Scenario 1, I am not using any variables within the cursor FOR loop to
process the values returned by the query.
In Scenario 2, I am using variables.
Now there could be as many variables for as many function calls in scenario
2 but in scenario 1, the function calls will be within the select statement.
Which scenario is efficient and why ?
Thanks and regards
Ratan
|
|
|
|
| Re: Performance question [message #20347 is a reply to message #20326] |
Wed, 15 May 2002 12:04  |
Su
Messages: 154
Registered: April 2002
|
Senior Member |
|
|
I think the first scenario is better than other one. In second scenario; since you are keeping the function in a FOR loop and using variables, every time the function is called for expression evaluation and some amount of memory is needed to store it (in a variable), it would slow down the process in long run or for large amount of data and you may not achieve the output in desired amount of time. I guess the first one is better, since the cursor is read only once when it is declared, and once after all the required data is SELECTEd (by CURSOR), the fetch takes care of the rest of the process and does not call the function again and again. So first scenario is better, I say. Hope you got my point.
Hey guys, any better suggesion? I am also curious to know that ;)
Good luck :)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
