Hi All,

I've been at this all day and cannot find a work around.

I have a largish sort of table (about 500,000 records) that I want to
transfer to another database. Some of the fields need translation
during the transfer, so I wrote the simple requirement with a select
procedure. Similar to...

CREATE PROCEDURE CHANGEVAL(
CONTROLVAL VARCHAR(4),
INVAL INTEGER )
RETURNS( OUTVAL ) AS
BEGIN
IF CONTROLVAL = 'ABCD' THEN
OUTVAL = GEN_ID(AGEN,1);
ELSE
OUTVAL = INVAL;
SUSPEND;
END

I have a few of these simple procedures for various required
translations. Some translate INTEGER and some NUMERIC(18,0). I then
setup the transfer program with a select statement like...

SELECT
(SELECT OUTVAL FROM
CHANGEVAL(TABLEA.VAL)) AS NEWVAL,
OTHERFIELD,
ETC
FROM TABLEA


I'm trying to keep this simple for demonstration purposes, in the
actual transfer there are many more fields and upto 10 translations
per table using a procedure similar to that described above. (ie. The
procedures are all simple, no selects from other tables etc.)


The problem is that when the select is executed the memory useage of
the (locally installed) server just keeps expanding as I progress
through it. Memory useage hits about 350Mb by the time this transfer
finishes, and things are starting to run a little slow.

If I run the same select without using the embedded translation
procedures the server uses about 20Mb at prepare time and never grows.

I've tried moving the select entirely inside a stored procedure and
doing the translations inside the procedure, but external to the
select statement. eg.

DECLARE VARIABLE Tmp INTEGER;
BEGIN
FOR SELECT VAL <etc>
FROM TABLEA
INTO :TMP
DO BEGIN
SELECT OUTVAL FROM CHANGEVAL(:TMP)
INTO NEWVAL;
<etc>

But this makes no difference to the memory useage.


The memory is not lost (other than to the operating system, since IB
seems reluctant to give anything back). Once the transaction is
commited, the memory stops growing - in fact I can do the same
transfer a second time without impacting the memory useage, so IB
still has control over the memory and is reusing it once the
transaction is over.

I tried doing a commitretaining at stages through the transfer to see
if I could keep the memory from growing but this did not help. Only a
full commit seems to work and it is difficult to work that into the
middle of my transfer process.


I was initially working with Borlands IB601, but just installed
Firebird 0.9.4 and the problem remains. The PC is running Windows
2000 Pro with SP1.


Does anyone have any idea what is going on here?

I guess I could transfer to an intermediate table, translate each
record insitue and then transfer that result. Since I have about 120
tables to transfer I was rather hoping to avoid that.


TIA

Geoff Worboys
Telesis Computing