SQL scroll cursor slow performance

[darren@xxxxxxxxx]

I'm using an SQL scroll cursor with the code shown below.  I call the
$ResetCurs1 subroutine and read through the entire cursor 4 times because I
generate the report 4 times with 4 different headings.  I had hoped that
after the first time through the cursor that any subsequent reads would be
relatively fast.  However, in debug I find that it takes 6 seconds to fetch
the first record in the cursor every time after the 'fetch before'
statement is executed.  Do you have any idea why it might take so long to
read the first record even though the data path has already been built?  I
don't see any messages after the 'fetch before' command or before the
'fetch into' that indicates anything is being rebuilt.   I could record the
output during the first run-through into a work-file, but I'm using SQL to
avoid doing such a thing.


C*****************************************************************************
     C* Declare cursor C1 to read detail records
*

C*****************************************************************************
     C     $OpenCurs1    BegSr
     C
     C/EXEC SQL
     C+ declare C1 scroll cursor for
     C+ select PLPRD, sum(PLQTY) as PLQTY,
     C+        PLCTN, sum(PLWGT) as PLWGT,
     C+        'BULTOS' as PTyp1, 'BUNDLES' as PTyp2,
     C+        sum(PHCWT)+sum(PLWGT) as GWgt,
     C+        ifnull(min(FKENGD),' ') as EDSC,
     C+        ifnull(min(FKSPND),' ') as SDSC,
     C+        ifnull(min(F5COO),' ') as F5COO,
     C+        ifnull(min(F2TOUM),' ') as F2TOUM,
     C+        ifnull(min(HARPMT),' ') as HARPMT,
     C+        ifnull(min(F5SCST),0), ifnull(min(F2FACT),1),
     C*        multiply by duty rate percentage
     C+          ifnull(case
     C+            when :FEDATE>=min(F4COOD)
     C+              then 0
     C+            when :FEDATE<min(F4COOD)
     C+              and min(FLPROF)='Y'
     C+              then min(FLPROR)
     C+            when :FEDATE<min(F4COOD)
     C+              and min(FLPROF)<>'Y'
     C+              then min(FLNORR)
     C+            end,0)
     C+
     C+   from PCHL01 join PCLL01
     C+     on PHCTN=PLCTN
     C+   left outer join DGF4
     C+     on F4PROD=PLPRD
     C+     and F4CMP=PHCMP
     C+     and F4FAC=PHFAC
     C+   left outer join HARF
     C+     on HARCOD=F4HARM
     C+   left outer join DGF5
     C+     on F5CMP=PHCMP
     C+     and F5Fac=PHFAC
     C+     and F5PROD=PLPRD
     C+   left outer join
     C+      (select F2FRUM, min(F2TOUM) as F2TOUM, min(F2FACT) as F2FACT
     C+        from DGF2 group by F2FRUM) DGF2
     C+     on F2FRUM=F5IUM
     C+   left outer join DGFK
     C+     on FKHARM=F4SUBH
     C+     and FKFAMN=F4FAMN
     C+   left outer join DGFL
     C+     on FLHARM=F4SUBH
     C+
     C+   where PHCONS=:Cons
     C+
     C+   group by PLCTN, PLPRD
     C+ order by PLCTN, PLPRD
     C/END-EXEC
     C
     C/EXEC SQL
     C+ open C1
     C/END-EXEC
     C                   EndSr
     C

C**************************************************************************
     C* Fetch cursor C1

C**************************************************************************
     C     $FetchCurs1   BegSr
     C/EXEC SQL
     C+ fetch C1 into
     C+      :PLPRD, :PLQTY, :PLCTN,
     C+      :PLWGT, :PTyp1,
     C+      :PTyp2, :GWgt, :EDSC,
     C+      :SDSC, :F5COO, :F2TOUM,
     C+      :HARPMT, :F5SCST, :F2FACT, :DutyRate
     C/END-EXEC
     C
DLS02C*                  Eval      F5SCST*=(DutyRate/100+1)
     C
     C                   EndSr

C**************************************************************************
     C* Reset cursor C1 to beginning of data

C**************************************************************************
     C     $ResetCurs1   BegSr
     C/EXEC SQL
     C+ fetch before from C1
     C/END-EXEC
     C                   EndSr

___________________________________
Darren Strong
Programmer Analyst
Group Dekko Service LLC.
Phone 260-347-3100 ext. 70270
Fax 260-599-3215