Another interesting SQL result

["Dan Bale" <dbale@xxxxxxxxxxxxx>]

FWIW, don't know if this will surprise SQL experts at all or not, but...

Referring back to the SQL with three joins I was developing, you may
remember that my Where clause looked like:

   Where     T01.CONOCV = 01
         and T01.ICDTCV between 1040701 and 1040731
         and T03.SCCDSS = '2'

Since the hard-coded date wasn't suitable for the production environment, I
changed this to:

   Where     T01.CONOCV = 01
         and T01.ICDTCV between :BeginDate and :EndDate
         and T03.SCCDSS = '2'

T01.ICDTCV is defined as 7,0 packed
BeginDate and EndDate are defined in the RPGLE program D-specs as 7,0 and
are set at *INZSR based on *entry parameters.

I ran the two versions back-to-back for about 20 iterations and, clearly,
the version that used variables :BeginDate and :EndDate completed in half
the time as the one using the hardcoded dates.  (I collected timestamps at
*INZSR and at end-of-program, then calculated the duration.)  Some
statisticals (all durations in seconds):

  Using:   Variable  Hard-coded

  Average:   1.436      2.843
  Maximum:   1.652      3.141
  Minimum:   1.262      2.647
Std. Dev.:   0.121      0.153

As always, YMMV.  But very interesting any way you look at it.

db