memory usage using CRBatchMove

[mervinpearce]

I am using CRBatchMove from a Oracle to MS SQL server with currently just under 2 million records which will grow over time...
The problem is that the target DB I presume is not flushing and the Memory gets depleted after a while. Is there a solution or should I rather code manually?

Regards
Mervin

[AndreyZ]

Hello,

Please specify where you encounter memory depletion: on the client computer that runs your application, or on the server computer where SQL Server resides. Also, do you encounter any errors (like 'Out of memory') related to memory depletion?
Please note, that SQL Server reserves once used memory for the next operations. SQL Server allocates enough memory for the current operation and doesn't release it after the operation is completed. SQL Server uses this memory for all the next operations, and it allocates more memory if there is not enough memory for an operation. We cannot influence such SQL Server behaviour.

[mervinpearce]

I have an application running on server 0, oracle server 1 and MS SQL server 2. The objective is to copy table on server 1 to server 2 using CRBatchMove

It is on the calling system where my application resides.... not the SQL Server nor the Oracle server. I did a memory call on the application to call memory usage on the progress of CRBatchMove per 1000 records with the following results....

1,000: Memory used: 22,472 K:2012/10/08 17:36:44
2,000: Memory used: 24,376 K:2012/10/08 17:36:47
3,000: Memory used: 26,296 K:2012/10/08 17:36:49
4,000: Memory used: 28,176 K:2012/10/08 17:36:51
5,000: Memory used: 30,128 K:2012/10/08 17:36:54
6,000: Memory used: 32,092 K:2012/10/08 17:36:56
7,000: Memory used: 34,088 K:2012/10/08 17:36:58
8,000: Memory used: 36,064 K:2012/10/08 17:37:00
.
.
.
1,134,000: Memory used: 2,006,384 K:2012/10/08 18:16:30
1,135,000: Memory used: 2,008,100 K:2012/10/08 18:16:32
1,136,000: Memory used: 2,009,804 K:2012/10/08 18:16:34
1,137,000: Memory used: 2,011,504 K:2012/10/08 18:16:36
and at this point I get a Memory full error

So the application does not 'flush' the buffer and it just grows...

[AndreyZ]

The TCRBatchMove component copies records between datasets. In your case, it means that on the computer where your application runs, you have two datasets: one that contains over two millions records (Oracle data) and another where you are going to copy these records (future SQL Server data). Because datasets store data in memory, at some point of copying data you encounter the 'Out of memory' error. To avoid this problem, you can set up the source dataset (Oracle) to read data by portions and release memory that is not needed. For this, you should set the UniDirectional property of the source dataset (it may be TOraQuery, TOraTable, or TSmartQuery) to True.

[mervinpearce]

A very old post but I have the same issue.

I have an Oracle TOraQuery and have set the UniDirectional := True
My destination DB ir a SDAC. I have [DoNotFetchAll as True], My source is about 73 million records.
CRBatchMove still seems to be growing and runs out of memory. The destination database seems to be the one using RAM as if I force a close and open on destination memory gets released. This cannot be done using CRBatchMove though. Any additional pointers that may help here?

[MaximG]

The Unidirectional and FetchAll properties are mutually exclusive: when setting UniDirectional=True the FetchAll value will become False, and vice versa. Unfortunately, this detail wasn't mentioned in description of these properties in our documentation for ODAC. We will correct this. Since such behavior applies to all Devart DAC products, you can learn about Unidirectional behavior using SDAC documentation: https://www.devart.com/sdac/docs/?devar ... tional.htm