Oracle Tips and Tricks — David Fitzjarrell

February 20, 2018

Here We Go, Again

Filed under: General — dfitzjarrell @ 20:38

"There are no wrong roads to anywhere."
-- Norton Juster, The Phantom Tollbooth

Every once in a while someone decides that Oracle functionality isn’t fast enough and makes a valiant attempt to correct these perceived deficiencies. Most of the time such efforts are anything but successful. From ‘home-grown’ referential integrity (which doesn’t work) to trying to speed up existing functionality the efforts are amazing and dismal at the same time. But that doesn’t keep people from trying.

Listed below is a package that was posted on the web some years ago; the goal, as mentioned above, was to make such date math, well, easier, and the speed difference, in a single run, isn’t very noticeable. Let’s take a run at this with and execute both the native Oracle functionality and the package function performing the same task for 100,000 executions and see how much time is consumed. The package code, as it was posted years ago, is shown along with some comments on the test. We begin:


SQL> 
SQL> /*
SQL> Once upon a time someone wrote this package to perform
SQL> date calculations:
SQL> 
SQL> CREATE OR REPLACE PACKAGE dates_pkg
SQL> AS
SQL> 	 FUNCTION julian_date
SQL> 	     ( date_to_convert DATE )
SQL> 	     RETURN NUMBER;
SQL> 
SQL> 	 FUNCTION minutes_since_midnight
SQL> 	     ( timevalue DATE )
SQL> 	     RETURN NUMBER;
SQL> 
SQL> 	 FUNCTION minutes_elapsed
SQL> 	     ( lowdate DATE
SQL> 	     , highdate DATE )
SQL> 	     RETURN NUMBER;
SQL> 
SQL> END dates_pkg;
SQL> /
SQL> 
SQL> CREATE OR REPLACE PACKAGE BODY dates_pkg
SQL> AS
SQL> 	 FUNCTION julian_date
SQL> 	     ( date_to_convert DATE)
SQL> 	     RETURN NUMBER
SQL> 	 IS
SQL> 	     varch_value VARCHAR (10);
SQL> 	     num_value NUMBER (20);
SQL> 	 BEGIN
SQL> 	     SELECT TO_CHAR
SQL> 		    ( TO_DATE(TO_CHAR(date_to_convert,'MM/DD/YYYY'),'MM/DD/YYYY')
SQL> 		    , 'J')
SQL> 	     INTO   varch_value
SQL> 	     FROM   dual;
SQL> 
SQL> 	     SELECT TO_NUMBER (varch_value)
SQL> 	     INTO   num_value
SQL> 	     FROM   dual;
SQL> 
SQL> 	     RETURN (num_value);
SQL> 	 END julian_date;
SQL> 
SQL> 
SQL> 	 FUNCTION minutes_since_midnight (
SQL> 	     timevalue DATE)
SQL> 	     RETURN NUMBER
SQL> 	 IS
SQL> 	     secs_elapsed NUMBER (20);
SQL> 	     mins_elapsed NUMBER (20);
SQL> 	 BEGIN
SQL> 	     SELECT TO_NUMBER
SQL> 		    ( TO_CHAR(TO_DATE(TO_CHAR(timevalue,'HH:MI AM'),'HH:MI AM')
SQL> 		    , 'SSSSS') )
SQL> 	     INTO   secs_elapsed
SQL> 	     FROM   dual;
SQL> 
SQL> 	     SELECT (secs_elapsed / 60)
SQL> 	     INTO   mins_elapsed
SQL> 	     FROM   dual;
SQL> 
SQL> 	     RETURN (mins_elapsed);
SQL> 	 END minutes_since_midnight;
SQL> 
SQL> 
SQL> 	 FUNCTION minutes_elapsed
SQL> 	     ( lowdate DATE
SQL> 	     , highdate DATE )
SQL> 	     RETURN NUMBER
SQL> 	 IS
SQL> 	     final_number NUMBER (20);
SQL> 	     low_julian NUMBER (20);
SQL> 	     high_julian NUMBER (20);
SQL> 	     num_days NUMBER (20);
SQL> 	     num_minutes NUMBER (20);
SQL> 	     temp_mins NUMBER (20);
SQL> 	     min_low NUMBER (20);
SQL> 	     min_high NUMBER (20);
SQL> 	 BEGIN
SQL> 	     SELECT julian_date (lowdate)
SQL> 	     INTO   low_julian
SQL> 	     FROM   dual;
SQL> 
SQL> 	     SELECT julian_date (highdate)
SQL> 	     INTO   high_julian
SQL> 	     FROM   dual;
SQL> 
SQL> 	     SELECT (high_julian - low_julian)
SQL> 	     INTO   num_days
SQL> 	     FROM   dual;
SQL> 
SQL> 	     SELECT (num_days * 1440)
SQL> 	     INTO   num_minutes
SQL> 	     FROM   dual;
SQL> 
SQL> 	     SELECT minutes_since_midnight (lowdate)
SQL> 	     INTO   min_low
SQL> 	     FROM   dual;
SQL> 
SQL> 	     SELECT minutes_since_midnight (highdate)
SQL> 	     INTO   min_high
SQL> 	     FROM   dual;
SQL> 
SQL> 	     SELECT (min_high - min_low)
SQL> 	     INTO   temp_mins
SQL> 	     FROM   dual;
SQL> 
SQL> 	     SELECT (num_minutes + temp_mins)
SQL> 	     INTO   final_number
SQL> 	     FROM   dual;
SQL> 
SQL> 	     RETURN (final_number);
SQL> 
SQL> 	 END minutes_elapsed;
SQL> END dates_pkg;
SQL> /
SQL> 
SQL> Either this person didn't know Oracle could do this
SQL> without help or this person possibly thought the tasks
SQL> were too difficult to write.  Regardless of that this
SQL> package was created.
SQL> 
SQL> Unfortunately this package takes more time to run the
SQL> calculations than it takes to use the built-in functionality
SQL> Oracle provides.
SQL> 
SQL> Let's prove that.
SQL> 
SQL> First let's use the package to execute one of the functions
SQL> 100000 times and report how long the entire loop takes to run.
SQL> */
SQL> 
SQL> declare
  2  	     v_retval	     number:=0;
  3  begin
  4  	     for i in 1..100000 loop
  5  		     select dates_pkg.minutes_elapsed(sysdate - 10, sysdate)
  6  		     into v_retval
  7  		     from dual;
  8  	     end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:41.36

Over 41 seconds to perform 100,000 date subtractions using the package code. That’s a lot of work to do for some basic calculations. Let’s let Oracle work its magic with some simple subtraction and multiplication and see how long 100,000 iterations takes:


SQL> 
SQL> /*
SQL> Now let's get Oracle to do the same calculation with native functionality
SQL> and report how long the entire loop takes to run.	Again this is a
SQL> 100000-iteration loop.
SQL> */
SQL> 
SQL> declare
  2  	     v_retval	     number:=0;
  3  begin
  4  	     for i in 1..100000 loop
  5  		     select  (sysdate - (sysdate - 10))*1440
  6  		     into v_retval
  7  		     from dual;
  8  	     end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.27
SQL> 

Oracle did the same amount of work in less than a second and a half. It required a great deal of effort to write this package and it did take some serious thought to get the functions written to produce the correct results, but the overall execution time is far greater than it should be. To be fair the package functions do make the calculation mechanics easier to understand, and it can be argued that 100,000 repetitions is a bit excessive. So, let’s do single runs of each and report on the execution time:


SQL>
SQL> /*
SQL> Okay, so 100,000 repetitions is a bit much.  How long do single executions take?
SQL> Let's find out.  First, the package function:
SQL> */
SQL>
SQL> select dates_pkg.minutes_elapsed(sysdate - 20, sysdate)
  2  from dual;

DATES_PKG.MINUTES_ELAPSED(SYSDATE-20,SYSDATE)
---------------------------------------------
                                        28800

Elapsed: 00:00:00.01
SQL>
SQL> /*
SQL> Next let Oracle do its thing.
SQL> */
SQL>
SQL> select     (sysdate - (sysdate - 20))*1440
  2  from dual;

(SYSDATE-(SYSDATE-20))*1440
---------------------------
                      28800

Elapsed: 00:00:00.01
SQL>

A single execution doesn’t appear to take any longer than the built-in functionality, but that’s an issue with the precision of the timing values, not an indication that the package functions are as fast, or faster, than basic subtraction and multiplication. Dividing the 100,000 run elapsed times by 100,000 more accurate timings are reported. First, the per-execution time for the function calls:


SQL> select 41.36/100000 from dual;

41.36/100000
------------
    .0004136

SQL>

Next, the per-execution time for the native calculations:


SQL> select 1.27/100000 from dual;

1.27/100000
-----------
   .0000127

SQL>

Although there is a considerable difference in the per-execution times it’s not one that would be noticeable without timing data, so it’s not unusual for a programmer to conclude his or her code is just as fast as the native functionality. Usually, though, such functions are called many times by other package procedures and functions and these repeated calls cause the elapsed time to add up to a possibly significant amount. Just because it looks fast in testing doesn’t mean it’s going to be fast under actual conditions. Such results also point to a flawed testing methodology; if the testing doesn’t reflect how the code is actually going to be used then the results of those tests really can’t be relied upon to accurately reflect the performance.

Change is good, but some changes just don’t provide the expected improvements. Accurate and representative testing needs to be done, even if that testing appears to be out of the ordinary. Some problems won’t manifest themselves until extreme testing provides results ‘normal’ testing can’t reveal. True, there are no wrong roads to anywhere, but some roads may lead to unexpected places.

You just might need another map to get back home.

Advertisements

Blog at WordPress.com.