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
BEGIN
   OPEN p_resultset FOR
     SELECT col1, col2, col3, ...
     FROM   my_table
     WHERE  status = 'Unprocessed';
END;  

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
BEGIN
   
     SELECT col1, col2, col3, ...
     INTO   p_col1, p_col2, p_col3
     FROM   my_table
     WHERE  primary_key_col = 123;
END;  

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.