Less calls…more performance

In various programming environments, a common metaphor is to open a cursor on the database (a REF CURSOR in Oracle parlance), return that cursor handle to the calling environment, and then that cursor is used to fetch or “fill” a data object, which may map to a grid on screen, or just to an array in memory.

And that’s totally fine – its an effective means to obtain a result set from the database to the calling environment.

For example, a typical PLSQL routine might look like:

PROCEDURE my_proc(p_resultset IN OUT sys_refcursor) IS
   OPEN p_resultset FOR
     SELECT col1, col2, col3, ...
     FROM   my_table
     WHERE  status = 'Unprocessed';

That’s fine when you want a set of rows, and luckily, most programming environments have evolved to the state nowadays where they will perform that retrieval with an array fetch, rather than single row fetches.

Because as a result, we’ve become attuned to always using the REF CURSOR method, so we tend to use this method for all database access.

But what about when we are returning a single row?  For example, if the SQL in the example above was:

SELECT col1, col2, col3, ...
FROM   my_table
WHERE  primary_key_col = 123;

then perhaps fetching from a ref cursor is not the most appropriate course of action. 

Why ?  Because it (typically) will require the programmer to issue two calls to the database:

  • call the PL/SQL routine to open the cursor
  • issue a local “fetch” call, which will then visit the database again to fetch the results

[Depending on the environment, there may even be a third call to close the cursor to ensure cursors dont leak and consume endless amounts of memory]

When retrieving a single row, perhaps think about the efficiency gains of wrapping the SQL within a PLSQL program and pass parameters in and out accordingly. The example above might then become something like:

PROCEDURE my_proc(p_prim_key IN int, p_col1 OUT int, p_col2 OUT int, p_col3 OUT int) IS
     SELECT col1, col2, col3, ...
     INTO   p_col1, p_col2, p_col3
     FROM   my_table
     WHERE  primary_key_col = 123;

The whole operation is done with a single call, and you get the PLSQL cursor caching benefit for free as well.

For high volume calls, this might be a significant performance boost.  Let’s face it – nowadays there is a good chance that your database server is just insanely fast at processing data, whether it be fetching it or storing it.  The speeds and core counts just continue to boggle the mind.  But all of that will count for little if you are jumping back and forth across the network between your middle tier program and the database.  Less calls = less trips = more performance.

6 thoughts on “Less calls…more performance

  1. hello,

    interesting remark

    but the question is about correctness of open-for statement
    OPEN p_resultset FOR
    SELECT col1, col2, col3, …
    INTO p_col1, p_col2, p_col3
    FROM my_table
    WHERE primary_key_col = 123;

    is it correct to have open-for and into clause ?
    what is purpose of “OPEN p_resultset FOR” if you use into clause

    in my test case ( oracle db ) out params p_col1…. always null
    and if i remove row “OPEN p_resultset FOR” then i have values in output params


  2. Hi Connor,

    It is great that you are addressing “data access” from client programs. There should be much more of this.

    What you say does depend on the calling environment. For example, the Oracle JDBC driver reduces round trips by sending the parse, execute and first pre-fetch commands all at once. See for example http://docs.oracle.com/database/121/JJDBC/oraperf.htm#CHDHDADD

    “Starting from Oracle Database 11g Release 1, the Thin driver can fetch the first prefetch_size number of rows from the server in the very first round-trip. This saves one round-trip in SELECT statements.”

  3. Hi Conner,

    But I dont understand this behavior, My developers are using Hibernet. The program runs a select statement to fetch 5 rows. The table size is of 30GB. Lets assume all tuning and statistics are in place. The first thing I did, was write a stored procedure and just execute it. But the Hibernet looks like executing the same stored procedure 5 times to fetch the 5 rows, or in other words making 5 calls to the database. But for a test the developer replaced it with original query, directly from the Hibernate session to the database, and in one call it is done.

Leave a Reply

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 )

Google+ photo

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

Connecting to %s