In the previous post, I mentioned that for a programming environment fetching rows from the database, then the method of
- open a ref cursor
- issue a fetch call
- close the ref cursor
might not be appropriate for those situations where the result set is known be a single row (eg primary key lookup).
A better option might be to call a procedure and get those outputs as parameters.
And I broke a cardinal rule… I effectively said “Here’s something that I know to be true…so you should just trust me“.
Dear oh dear oh dear. That’s not good. So without further ado, it’s time to put together a test case.
We’re need to come up with a way to test this across a “fast” network, the aim being that even with a best network latency we can obtain, there will still be a demonstratable benefit to using a PL/SQL procedure over the ref cursor method.
One way of doing this is, is to have two databases running on the same box. One database will be the “real” database server, ie, it will hold our data. The other database will actually be a client to this database, and we will access the data via a database link. So we never go out across the true physical network – we are simply getting the smallest latency we can by going (in my case) localhost to localhost.
So firstly on our “real” database, we create a table with a primary key. This database is called “NP12” on my machine
SQL> drop table T purge; Table dropped. SQL> create table T ( 2 x int primary key, 3 y int , 4 z char(100)); Table created. SQL> insert into T 2 select rownum, rownum, rownum 3 from dual 4 connect by level <= 1000; 1000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats('','T'); PL/SQL procedure successfully completed.
Now I’ve created a second database, so we’ll connect to that and that create some routines to access the table T on NP12
SQL> create database link np12 using 'np12'; Database link created.
These are our two procedure we will use for the test. One is our REF CURSOR procedure, which opens a cursor and passes it back to the client. The second does the whole job in the procedure but issuing the query and passing the fetched value back as a parameter.
SQL> create or replace 2 procedure ref_cursor_open(p_i int, rc in out sys_refcursor) is 3 begin 4 open rc for select y from t@np12 where x = p_i; 5 end; 6 / Procedure created. SQL> create or replace 2 procedure single_row_fetch(p_i int, p_y out int) is 3 begin 4 select y into p_y from t@np12 where x = p_i; 5 end; 6 / Procedure created.
And now its time to test them for speed using a simple looping script. I’m using the INLINE pragma to reduce the overhead of PL/SQL calls so that we have a better accuracy with respect to the cost of the extra calls when using REF CURSOR.
SQL> set timing on SQL> declare 2 rc sys_refcursor; 3 res int; 4 begin 5 for j in 1 .. 10 loop 6 for i in 1 .. 1000 loop 7 pragma inline(ref_cursor_open,'YES'); 8 ref_cursor_open(i,rc); 9 fetch rc into res; 10 close rc; 11 end loop; 12 end loop; 13 end; 14 / PL/SQL procedure successfully completed. Elapsed: 00:00:05.55 SQL> declare 2 rc sys_refcursor; 3 res int; 4 begin 5 for j in 1 .. 10 loop 6 for i in 1 .. 1000 loop 7 pragma inline(single_row_fetch,'YES'); 8 single_row_fetch(i,res); 9 end loop; 10 end loop; 11 end; 12 / PL/SQL procedure successfully completed. Elapsed: 00:00:02.80 SQL>
And there you have it. Almost twice as fast. If you just trusted what I said from the first post…then that’s great. But in general, if you read something in someone’s blog, either challenge them to prove their assertion or just whip up a script to prove it for yourself.