Geoff,

Try selecting the natural values from the source table, and pass each row to a
single stored procedure on the target. The target SP can translate any values,
and write the row into the target database.

Vince

Geoff Worboys wrote:

>
> 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
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/

--
Vince Duggan
Synectics Software
Inprise C/S Business Partner
South Africa
http://www.synectics.co.za
mailto:vince@...