Why FREE cursor causes a huge and very slow dump!

[WiccaChic]

Hello 4gl gurus! We have a 4ge (code below) that runs all day, executing a stored procedure from time to time and is meant to end itself at 11pm. Does not really do a whole lot, but at 11pm every single night when it finishes up (with a FREE statement) this thing dumps like you would not believe, slowing down the box for almost 20 minutes at a time and creating huge SHMEM files in my dump directory. Programmer who authored it is taking the "high ground" and insisting its an IDS bug...can someone please take a look at the code and let me know why it may not be working? Not really looking for other ways of doing this, I would rather no why its not working as designed.
-thanks

DATABASE MYDB
MAIN
define sqlCmd varchar(255);
define hourNow datetime hour to hour;
define hourTarget datetime hour to hour;
-- Bail out at 11pm
let hourTarget = Datetime(23) Hour to Hour;
let sqlCmd = "execute procedure sp_mysp()"
prepare sqlStmt from sqlCmd
select current
into hourNow
from systables
where tabid = 1;
--Display hourNow;
WHILE (hourNow < hourTarget) -- Bail out at 9 pm
execute sqlStmt;
sleep 300; -- Run Every 5 minutes
select current
into hourNow
from systables
where tabid = 1;
--Display hourNow;
END WHILE
free sqlStmt;
END MAIN

 

[vpshriyan]

Hi,

Module logic seems to be ok to me. There is no cursor involved, but a prepared id (similar to c language pointer) has been used. In my view, free statement is not the culprit! instead I point the finger to the Stored Procedure that it calls, is the root of problem.

You may check this by commenting off the --free sqlStmt, since this a standalone module and not an application. The prepared id will be released off from the memory any how, when the module terminates by itself.

Stored Procedure sp_mysp() need to be examined, for it's complexities and need to be optimized.

Regards,
Shriyan

&quot;If a program is useful, it will have to be changed.&quot;
&quot;If a program is useless, it will have to be documented.&quot;

 

[WiccaChic]

Hi vpshriyan,
I took your advice and you were right, problem still occurs without the free. I think I am up a creek because the programmer in question absolutely will not look at this issue anymore and I cant decode his SP myself, I'm just not up to it technically.

Thanks for your help.