| PRocedure/Function [message #41246] |
Thu, 12 December 2002 00:42  |
Arjun
Messages: 17
Registered: April 2001
|
Junior Member |
|
|
|
If the procedure and function will have same content or same functionality then which will execute fast(Function or Procedure).
|
|
|
|
| Re: PRocedure/Function [message #41253 is a reply to message #41246] |
Thu, 12 December 2002 06:03  |
B
Messages: 327
Registered: August 1999
|
Senior Member |
|
|
seems U don't understand the differences between function and procedure ... is not the same !
procedure exec code
function return value
|
|
|
|
| Re: PRocedure/Function [message #41291 is a reply to message #41246] |
Sat, 14 December 2002 06:43 |
 |
Barbara Boehmer
Messages: 9094
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
That's a very interesting question. When, in doubt, test and see. Below, I have created a procedure and a function that both accomplish the same thing, then used a method by Tom Kyte, with some slight modifications, to compare their speed. I tried to select something that was lengthy enough and loop through it enough times for a fair test. In this case, it looks like the function is the winner with 18,792 seconds compared to the procedure with 19,727 seconds. However, that may or may not be true for other similar procedures and functions. It may depend on what the procedure or function is doing. I would love to hear of any other comparisons or opinions of others.
SQL> -- the procedure:
SQL> CREATE OR REPLACE PROCEDURE test_proc
2 (p_out OUT NUMBER)
3 AS
4 BEGIN
5 SELECT COUNT (*)
6 INTO p_out
7 FROM all_objects;
8 END test_proc;
9 /
Procedure created.
SQL>
SQL>
SQL> -- the function:
SQL> CREATE OR REPLACE FUNCTION test_func
2 RETURN NUMBER
3 AS
4 v_out NUMBER;
5 BEGIN
6 SELECT COUNT (*)
7 INTO v_out
8 FROM all_objects;
9 RETURN v_out;
10 END test_func;
11 /
Function created.
SQL>
SQL>
SQL>
SQL> -- the comparison:
SQL> CREATE TABLE run_stats
2 (runid VARCHAR2 (15),
3 name VARCHAR2 (80),
4 value INT)
5 /
Table created.
SQL> CREATE OR REPLACE VIEW stats
2 AS
3 SELECT 'STAT...' || a.name name, b.value
4 FROM v$statname a, v$mystat b
5 WHERE a.statistic# = b.statistic#
6 UNION ALL
7 SELECT 'LATCH.' || name, gets
8 FROM v$latch
9 /
View created.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 l_start NUMBER;
3 -- add any other variables you need here for the test...
4 v_out NUMBER;
5 BEGIN
6 DELETE FROM run_stats;
7 COMMIT;
8 -- start by getting a snapshot of the v$ tables
9 INSERT INTO run_stats
10 SELECT 'before', stats.*
11 FROM stats;
12
13 -- and start timing...
14 l_start := DBMS_UTILITY.GET_TIME;
15
16 -- for things that take a very small amount of time, I like to
17 -- loop over it time and time again, to measure something "big"
18 -- if what you are testing takes a long time, loop less or maybe
19 -- not at all
20 FOR i in 1 .. 10
21 LOOP
22 -- your code here for approach #1
23 test_proc (v_out);
24 DBMS_OUTPUT.PUT_LINE (v_out);
25 END LOOP;
26
27 DBMS_OUTPUT.PUT_LINE ((DBMS_UTILITY.GET_TIME - l_start) || ' hsecs');
28
29 -- get another snapshot and start timing again...
30 INSERT INTO run_stats
31 SELECT 'after 1', stats.*
32 FROM stats;
33
34 l_start := DBMS_UTILITY.GET_TIME;
35
36 FOR i in 1 .. 10
37 LOOP
38 -- your code here for approach #2
39 v_out := test_func;
40 DBMS_OUTPUT.PUT_LINE (v_out);
41 END LOOP;
42
43 DBMS_OUTPUT.PUT_LINE ((DBMS_UTILITY.GET_TIME - l_start) || ' hsecs');
44 INSERT INTO run_stats
45 SELECT 'after 2', stats.*
46 FROM stats;
47 END;
48 /
23185
23185
23185
23185
23185
23185
23185
23185
23185
23185
19727 hsecs
23185
23185
23185
23185
23185
23185
23185
23185
23185
23185
18792 hsecs
PL/SQL procedure successfully completed.
SQL> COLUMN name FORMAT A40 WORD_WRAPPED
SQL> COLUMN winner FORMAT A6
SQL> SELECT a.name,
2 b.value - a.value AS proc,
3 c.value - b.value AS func,
4 ((c.value-b.value) - (b.value - a.value)) AS diff,
5 DECODE (SIGN ((c.value-b.value) - (b.value - a.value)),
6 0, 'TIE',
7 1, 'PROC',
8 -1, 'FUNC') AS winner
9 FROM run_stats a, run_stats b, run_stats c
10 WHERE a.name = b.name
11 AND b.name = c.name
12 AND a.runid = 'before'
13 AND b.runid = 'after 1'
14 AND c.runid = 'after 2'
15 AND (c.value - a.value) > 0
16 AND (c.value - b.value) <> (b.value - a.value)
17 ORDER BY ABS ((c.value - b.value) - (b.value - a.value))
18 /
NAME PROC FUNC DIFF WINNER
---------------------------------------- ---------- ---------- ---------- ------
LATCH.ktm global data 1 0 -1 FUNC
LATCH.redo allocation 101 102 1 PROC
STAT...cursor authentications 1 2 1 PROC
STAT...deferred (CURRENT) block cleanout 5 6 1 PROC
applications
LATCH.sort extent pool 3 2 -1 FUNC
STAT...dirty buffers inspected 1 0 -1 FUNC
STAT...enqueue releases 24 23 -1 FUNC
STAT...free buffer inspected 1 0 -1 FUNC
STAT...execute count 41 40 -1 FUNC
NAME PROC FUNC DIFF WINNER
---------------------------------------- ---------- ---------- ---------- ------
STAT...parse count (total) 32 31 -1 FUNC
STAT...parse count (hard) 2 1 -1 FUNC
STAT...opened cursors cumulative 32 31 -1 FUNC
LATCH.session timer 162 160 -2 FUNC
STAT...calls to get snapshot scn: kcmgss 56 54 -2 FUNC
STAT...prefetched blocks 2 0 -2 FUNC
STAT...table fetch by rowid 377142 377140 -2 FUNC
STAT...redo entries 36 34 -2 FUNC
STAT...enqueue requests 25 23 -2 FUNC
STAT...no work - consistent read gets 304219 304217 -2 FUNC
STAT...messages sent 4 2 -2 FUNC
NAME PROC FUNC DIFF WINNER
---------------------------------------- ---------- ---------- ---------- ------
LATCH.transaction allocation 13 11 -2 FUNC
LATCH.virtual circuit queues 11 9 -2 FUNC
STAT...db block changes 67 64 -3 FUNC
STAT...buffer is not pinned count 422149 422145 -4 FUNC
LATCH.multiblock read objects 6 0 -6 FUNC
STAT...session logical reads 635247 635241 -6 FUNC
STAT...db block gets 102 96 -6 FUNC
STAT...recursive calls 416 409 -7 FUNC
LATCH.active checkpoint queue latch 70 61 -9 FUNC
LATCH.session allocation 618 604 -14 FUNC
LATCH.redo writing 267 250 -17 FUNC
NAME PROC FUNC DIFF WINNER
---------------------------------------- ---------- ---------- ---------- ------
LATCH.session idle bit 1221 1202 -19 FUNC
LATCH.undo global data 44 25 -19 FUNC
LATCH.enqueue hash chains 374 346 -28 FUNC
LATCH.messages 565 530 -35 FUNC
LATCH.enqueues 1027 964 -63 FUNC
LATCH.checkpoint queue latch 402 308 -94 FUNC
LATCH.shared pool 1178 1062 -116 FUNC
STAT...redo size 25312 25472 160 PROC
LATCH.library cache 5070 4834 -236 FUNC
LATCH.row cache objects 153066 152727 -339 FUNC
STAT...hot buffers moved to head of LRU 432 0 -432 FUNC
NAME PROC FUNC DIFF WINNER
---------------------------------------- ---------- ---------- ---------- ------
STAT...physical reads 500 9 -491 FUNC
STAT...free buffer requested 505 12 -493 FUNC
LATCH.cache buffers lru chain 722 76 -646 FUNC
LATCH.cache buffers chains 1101024 1100184 -840 FUNC
STAT...session uga memory 8472 4220 -4252 FUNC
47 rows selected.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
