Less calls…more performance (part 2)

Posted by

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

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.


  1. What you really need is to call from a typical client environment. Calling one database from another using a server-side programming language is not typical.

    As I mentioned in a comment on your previous post, client-side drivers such as JDBC have worked to reduce round trips over the years.

    Here is my test case:

    import java.sql.*;
    import oracle.jdbc.pool.OracleDataSource;

    public class RoundTrips {

    public static void main(String[] args) throws Exception{
    OracleDataSource ods = null;
    Connection conn = null;
    PreparedStatement ps = null;
    CallableStatement cs = null;
    ResultSet rset = null;
    long startTime;

    // Create DataSource and connect to the local database
    ods = new OracleDataSource();
    conn = ods.getConnection();

    try {
    // Do once to load appropriate Java classes
    ps = conn.prepareStatement(“select y from t where x = ?”);
    ps.setInt(1, 1);
    rset = ps.executeQuery();

    cs = conn.prepareCall(“begin ? := single_row_fetch(?); end;”);
    cs.registerOutParameter(1, Types.INTEGER);
    cs.setInt(2, 1);

    // Real test
    ps = conn.prepareStatement(“select y from t where x = ?”);
    startTime = System.currentTimeMillis();
    for (int i = 1; i <= 1000; i++) {
    ps.setInt(1, i);
    rset = ps.executeQuery();
    System.out.println("Select time: " + (System.currentTimeMillis() – startTime) + " ms");

    cs = conn.prepareCall("begin ? := single_row_fetch(?); end;");
    cs.registerOutParameter(1, Types.INTEGER);
    startTime = System.currentTimeMillis();
    for (int i = 1; i <= 1000; i++) {
    cs.setInt(2, i);
    System.out.println("Call time: " + (System.currentTimeMillis() – startTime) + " ms");
    } catch (Exception e) {
    if(rset!=null) rset.close();
    if(ps!=null) ps.close();
    if(cs!=null) cs.close();
    if(conn!=null) conn.close();

    Select time: 301 ms
    Call time: 331 ms

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.