|
-
Oh I see what you're getting at. You want to race your SQL statement against my PL/SQL function called from SQL. The cost of context switching between SQL and PL/SQL is no big secret, in fact I would say it is one of the major failings of the integration of SQL and PL/SQL. Obviously if I wanted to go fast I would not call the above function from SQL if that is what you are getting at.
Nevertheless lets test. Feel free to post any evidence you may have at any time.
I figure this logic could be used in a range of places in our application, so I will test:
1. PL/SQL direct assignment. My expression (without function call) vs. your straight SELECT FROM dual, 100,000x in PL/SQL.
2. PL/SQL calls PL/SQL function. My expression (as function call) vs. your SELECT FROM dual in a function, 100,000x in PL/SQL.
3. SQL calls PL/SQL function. My expression (as function call) vs. your SELECT FROM dual in a function, 100,000 rows in SQL.
4. Straight SQL. My SQL expression (in-line view expanded) vs your SQL expression. 100.000 rows in SQL.
I'll also use a table (called table_name, columns min_dte DATE, max_dte DATE, 1,000,000 rows).
Code:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 v_start_date DATE := SYSDATE;
3 v_end_date DATE := v_start_date + 15;
4 v_business_days NUMBER;
5 v_iterations PLS_INTEGER := 10 ** 5;
6 v_start_time PLS_INTEGER := 0;
7
8 TYPE tt_business_days IS TABLE OF NUMBER;
9 t_business_days tt_business_days := tt_business_days ();
10 BEGIN
11 DBMS_OUTPUT.PUT_LINE ('[1. PL/SQL direct assignment');
12 v_start_time := DBMS_UTILITY.get_time;
13
14 FOR i IN 1 .. v_iterations
15 LOOP
16 SELECT dayz
17 - NVL (FLOOR (ROUND ((dayz + add_to_dayz) / 7, 2)) * 2,
18 0) week_days
19 INTO v_business_days
20 FROM (SELECT TRUNC (v_end_date) - TRUNC (v_start_date) -
21 DECODE (SUBSTR (TO_CHAR (v_start_date, 'DAY'), 1, 3), 'SUN', 1,'SAT', 2, 0) -
22 DECODE (SUBSTR (TO_CHAR (v_end_date, 'DAY'), 1, 3), 'SUN', 2, 'SAT', 1, 0) + 1 dayz,
23 DECODE (SUBSTR (TO_CHAR (v_start_date, 'DAY'), 1, 3), 'MON', 1,
24 'TUE', 2, 'WED', 3, 'THU', 4, 'FRI', 5, 0) add_to_dayz
25 FROM DUAL);
26 END LOOP;
27 DBMS_OUTPUT.put_line ('[you (hsecs)] ' ||
28 (DBMS_UTILITY.get_time - v_start_time));
29
30 v_start_time := DBMS_UTILITY.get_time;
31 FOR i IN 1 .. v_iterations
32 LOOP
33 v_business_days := v_end_date - v_start_date - ((
34 TRUNC (NEXT_DAY (v_end_date, 'SAT') - NEXT_DAY (v_start_date - 1, 'SAT')) / 7) + (
35 TRUNC (NEXT_DAY (v_end_date, 'SUN') - NEXT_DAY (v_start_date - 1, 'SUN')) / 7)) + 1;
36 END LOOP;
37 DBMS_OUTPUT.put_line ('[me (hsecs)] ' ||
38 (DBMS_UTILITY.get_time - v_start_time));
39
40 DBMS_OUTPUT.PUT_LINE (CHR (10) || '[2. PL/SQL calls PL/SQL function]');
41 v_start_time := DBMS_UTILITY.get_time;
42
43 FOR i IN 1 .. v_iterations
44 LOOP
45 v_business_days := working_days (v_start_date, v_end_date);
46 END LOOP;
47
48 DBMS_OUTPUT.put_line ('[you (hsecs)] ' ||
49 (DBMS_UTILITY.get_time - v_start_time));
50
51 v_start_time := DBMS_UTILITY.get_time;
52
53 FOR i IN 1 .. v_iterations
54 LOOP
55 v_business_days := business_days_between (v_start_date, v_end_date);
56 END LOOP;
57
58 DBMS_OUTPUT.put_line ('[me (hsecs)] ' ||
59 (DBMS_UTILITY.get_time - v_start_time));
60
61 DBMS_OUTPUT.PUT_LINE (CHR (10) || '[3. SQL calls PL/SQL function]');
62 t_business_days.DELETE;
63 v_start_time := DBMS_UTILITY.get_time;
64
65 SELECT working_days (min_dte, max_dte)
66 BULK COLLECT INTO t_business_days
67 FROM table_name
68 WHERE ROWNUM <= 100000;
69
70 DBMS_OUTPUT.put_line ('[you (hsecs)] ' ||
71 (DBMS_UTILITY.get_time - v_start_time));
72
73 t_business_days.DELETE;
74 v_start_time := DBMS_UTILITY.get_time;
75
76 SELECT business_days_between (min_dte, max_dte)
77 BULK COLLECT INTO t_business_days
78 FROM table_name
79 WHERE ROWNUM <= 100000;
80
81 DBMS_OUTPUT.put_line ('[me (hsecs)] ' ||
82 (DBMS_UTILITY.get_time - v_start_time));
83
84 DBMS_OUTPUT.PUT_LINE (CHR (10) || '[4. Straight SQL]');
85 t_business_days.DELETE;
86 v_start_time := DBMS_UTILITY.get_time;
87
88 SELECT TRUNC (max_dte) - TRUNC (min_dte) - DECODE (SUBSTR (TO_CHAR (
89 min_dte, 'DAY'), 1, 3), 'SUN', 1, 'SAT', 2, 0) - DECODE (
90 SUBSTR (TO_CHAR (max_dte, 'DAY'), 1, 3), 'SUN', 2, 'SAT', 1, 0) + 1 -
91 NVL (FLOOR (ROUND ((TRUNC (max_dte) - TRUNC (min_dte) - DECODE (
92 SUBSTR (TO_CHAR (min_dte, 'DAY'), 1, 3), 'SUN', 1, 'SAT', 2, 0) -
93 DECODE (SUBSTR (TO_CHAR (max_dte, 'DAY'), 1, 3), 'SUN', 2, 'SAT', 1, 0) +
94 1 + DECODE (SUBSTR (TO_CHAR (min_dte, 'DAY'), 1, 3),
95 'MON', 1, 'TUE', 2, 'WED', 3, 'THU', 4, 'FRI', 5, 0)) / 7, 2)) * 2, 0) week_days
96 BULK COLLECT INTO t_business_days
97 FROM table_name
98 WHERE ROWNUM <= 100000;
99
100 DBMS_OUTPUT.put_line ('[you (hsecs)] ' || (DBMS_UTILITY.get_time - v_start_time));
101
102 t_business_days.DELETE;
103 v_start_time := DBMS_UTILITY.get_time;
104
105 SELECT max_dte - min_dte - ((
106 TRUNC (NEXT_DAY (max_dte, 'SAT') -
107 NEXT_DAY (min_dte - 1, 'SAT')) / 7) + (
108 TRUNC (NEXT_DAY (max_dte, 'SUN') -
109 NEXT_DAY (min_dte - 1, 'SUN')) / 7)) + 1 business_days
110 BULK COLLECT INTO t_business_days
111 FROM table_name
112 WHERE ROWNUM <= 100000;
113
114 DBMS_OUTPUT.put_line ('[me (hsecs)] ' ||
115 (DBMS_UTILITY.get_time - v_start_time));
116 END;
117
118 /
[1. PL/SQL direct assignment]
[you (hsecs)] 2904
[me (hsecs)] 525
[2. PL/SQL calls PL/SQL function]
[you (hsecs)] 3043
[me (hsecs)] 652
[3. SQL calls PL/SQL function]
[you (hsecs)] 3572
[me (hsecs)] 871
[4. Straight SQL]
[you (hsecs)] 699
[me (hsecs)] 200
PL/SQL procedure successfully completed.
SQL>
Looks like your 699 only beat one of my scores - that of calling PL/SQL function in PL/SQL. The cost of context switches from SQL to PL/SQL is IMHO a major limitation of the integration of PL/SQL with SQL. That's a big shame because it would be nice to encapsulate business logic in one function and use it throughout the application. Still, if I need to go really fast in SQL I have a version for that too.
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
|