RE: SQL problem -- batch vs. interactive

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Tom, I used to do testing in interactive SQL and would get the result screen
quickly when in my program it would take "forever".
I concluded that STRSQL is misleading because it does not seem to bring in
the entire result set but only the page(s) I was looking at.  I could be
wrong here, but these were my observations, at least for long running
statements.

I changed the way I test to execute a SELECT COUNT(*) type of statement,
forcing the system to 'count' the rows returned, hence going through the
entire result set.  This seems to model batch runtimes more closely.  

I was once told that REFRESH parm on STRSQL command may play a role in this
behavior, but never bothered to research it further.

I'm curious to see what your test might show, as well as what other people's
experience has been.

Elvis

-----Original Message-----
 Subject: SQL problem -- batch vs. interactive

The following SQL statement runs in maybe 25 seconds in iSQL (V5R2, of
course, current PTFs) but a batch run as embedded SQL it takes a couple
hours:

select
   FLD001 ,
   FLD002 ,
   FLD003 ,
   FLD004 ,
   sum(
       (A.FLD005-FLD006)
     * (FLD007/A.FLD005)
      )
    as TOT ,
   FLD008 ,
   PERIOD
    from (
          select
             case
                when  FLD009 <  1040401  [:h1] then '1'
                when  FLD009 >  1040601  [:h2] then '4'
                when  FLD009 >= 1040501  [:h3] then '3'
                else  '2'
             end
              as PERIOD ,
             FLD001 ,
             FLD002 ,
             FLD003 ,
             FLD004 ,
             FLD009 ,
             FLD005 ,
             FLD006 ,
             FLD007 ,
             FLD008
              from
                 TBL01 a ,
                 TBL02 b
              where
                 a.FLD001 in ('FL' , '00') and
                 a.FLD005 <> 0 and
                 A.FLD010 =  ' ' and
                 a.FLD001 =  b.FLD011 and
                 a.FLD012 =  b.FLD013 and
                 exists(
                        select
                           *
                            from
                               TBL03 c
                            where
                               a.FLD001 =  c.FLD014 and
                               a.FLD015 =  c.FLD016 and
                               c.FLD017 <> 'Q' and
                               c.FLD018 in ('CC','CL ,'CP','CS','CU')
                       )
          ) as A
    group by
       FLD001 ,
       FLD002 ,
       FLD003 ,
       FLD004 ,
       FLD008 ,
       PERIOD

The [:hn] elements in the CASE clause simply mean the values are supplied by
host variables. Regardless of whether proper indexes are available, what
elements would cause such a major difference in run-time between
environments? Run priority of interactive vs. batch is certainly possible,
but the interactive is also during busy working hours and batch is at night
during relatively quiet periods. Memory pools seem appropriate (approx 500MB
for both batch and interactive during run periods).

At the moment, it's not a question of whether the statement could be better
constructed. It's a question of curiosity about the difference.

Thanks for any discussion.

Tom Liotta