4gl and sqexplain help needed

Hello all, If I am posting in the wrong list please let me know. I have a question about how the 9.2FC6 engine is displaying sql statements using c4gl 7.32hc2 and esqlc 9.53hc2. In attempting to resolve a severe performance degradation after an upgrade from 7.32 to 9.2 with the above mention tools/network we ran sqexplain on the offending programme. The sqexplain shows the select statements doubled when the 4gl is using the following syntax listed below. This same syntax in c4gl 7.20ud8 shows only one select statement. Is the 4gl program really performing 2 selects, thus the program's speed degradation, or is the sqexplain just indicating a prepare-declare-open statements are being used and only one select is occurring? Thanks in advance, Doug LISTING EXAMPLE I prepare p1f5a from SqlStmt declare c1f5a cursor for p1f5a open c1f5a using pyemp.hr_pe_id , dtReportEnd, dtExtractBeg foreach c1f5a into iPeriod, sBatch, dtDate, iHrsNo, iHrs, iRt, iAmt, stmpType, sStatusCd, sSystem, iPlan, sDeferFlag, dtEarnDt, dtEarnDt2, sPB, sHB, pyt_num LISTING EXAMPLE II let sSql = "select * from ", cdhtmp_name clipped, " order by CdhNo" prepare cdh_select from sSql declare f3c1 cursor for cdh_select open f3c1 FOREACH f3c1 into sCdhNo, sCdhCd, sStatusCd, sDeferFlag, sType, sSystem, iPlan, sBeg, sEnd, sRetPB, sRetHB let Dbg[1] = " ", sCdhNo using "&&&&", " ", sCdhCd, " ", sStatusCd, " ", sDeferFlag, " ", sType, " ", sSystem, iPlan using "#", " ", sBeg, " ", sEnd, " ", sRetPB, " ", sRetHB call f0_debug(1,1) END FOREACH

Quite possibly.. The OPEN is also done in the foreach. There was a "bug" in earlier 4gl versions which meant that you could pass in parameters to the foreach's open by having it already opened which a lot of people exploited... If it were me - I would just change the foreach to a while loop with an appropriate "exit while" after a fetch, if you need to pass in those values in the open... On Wednesday 31 May 2006 17:19, Doug Fossmeyer wrote: > open c1f5a using pyemp.hr_pe_id , dtReportEnd, dtExtractBeg > > foreach c1f5a into iPeriod, sBatch, dtDate, iHrsNo, iHrs, iRt, iAmt, > stmpType, -- Mike Aubury