memory usage using CRBatchMove

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
mervinpearce
Posts: 16
Joined: Mon 08 Oct 2012 14:08

memory usage using CRBatchMove

Post by mervinpearce » Mon 08 Oct 2012 14:17

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

Re: memory usage using CRBatchMove

Post by AndreyZ » Tue 09 Oct 2012 11:44

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
Posts: 16
Joined: Mon 08 Oct 2012 14:08

Re: memory usage using CRBatchMove

Post by mervinpearce » Tue 09 Oct 2012 12:51

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

Re: memory usage using CRBatchMove

Post by AndreyZ » Wed 10 Oct 2012 08:28

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
Posts: 16
Joined: Mon 08 Oct 2012 14:08

Re: memory usage using CRBatchMove

Post by mervinpearce » Mon 14 Nov 2016 11:12

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
Devart Team
Posts: 1030
Joined: Mon 06 Jul 2015 11:34

Re: memory usage using CRBatchMove

Post by MaximG » Mon 14 Nov 2016 14:59

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

Post Reply