Out of memory using TCRBatchMove

[ertank]

Hello,

I am using UniDAC 7.1.3, Delphi 10.2, target Win32 executable.

I have this big result set with more than 1.7 million rows. Data read from SQL Server 2012 Express 64bit. I am using TCRBatchMove to copy this data result into a SQLite3 database table.

If I open TUniQuery with SpecificOptions.FetchAll = True, I immediately get Out of memory error in seconds.
If I open TUniQuery with SpecificOptions.FetchAll = False, I get Out of memory error in a couple of minutes during the process.

My questions are:
1- Is it possible to do automatic "data paging" with TUniQuery, CRBatchMove or TUniLoader?
2- How can I avoid this error without much change in my current code?

Targetting 64bit executable might be one option, but this is not I something I would like to do.

Thanks & regards,
Ertan Küçükoğlu

[Stellar]

When executing the TCRBatchMove.Execute method, all data from the table is loaded first to TCRBatchMove.Source dataset, then is copied to TCRBatchMove.Destination dataset, this leads to double memory consumption. To optimize memory consumption for data sources (TCRBatchMove.Source), you can set the TUniQuery.UniDirectional property to True.
Also you can use TUniLoader instead of TCRBatchMove. in this case, if Source dataset is in UniDirectional mode, then the memory consumption will be minimal.

More details about the UniDirectional property: https://www.devart.com/sdac/docs/?devar ... tional.htm
More about TUniLoader: https://www.devart.com/unidac/docs/inde ... loader.htm

[ertank]

Hello Stellar,

Source query is set as following now:

Code: Select all

  DM.qrySource.Close();
  DM.qrySource.SpecificOptions.Values['FetchAll'] := 'False';
  DM.qrySource.UniDirectional := True;
  DM.qrySource.ReadOnly := True;

TUniLoader is used to load data from SQL Server to SQLite.

My system has 8GB physical memory. If I have several applications running, I still get out of memory error. If I close all other applications and have my application running as only application then it finishes and copies data without any error.

My observation is that I get Out of memory error while application is using about 280MB of memory. Resource Monitor display following:

Code: Select all

Commit: 277.512 KB
Working Set: 302.552 KB
Shareable: 28.636 KB
Private: 273.916 KB

When my application is the only application working, I read application memory usage going up around 565MB peak and then complete data copy. Details of memory usage as far as I can see are:

Code: Select all

Commit: 540.068 KB
Working Set: 565.100 KB
Shareable: 28.708 KB
Private: 536.392 KB

My new questions are:
1- As to my knowledge, Win32 applications can go up to 2GB memory usage and application should be fine below 800MB memory usage as per https://blogs.msdn.microsoft.com/tom/20 ... processes/ So, why I get Out of memory error around 270MB (or lets say around 600 MB)?

2- This data of 1.7 million rows is increasing each year. Moreover, my customers mostly have on average 6GB physical RAM on their systems. Is there anything I can do to reduce memory use further?

Thanks & regards,
Ertan Küçükoğlu

[Stellar]

Unfortunately, we cannot reproduce the Out of memory issue when copying a large amount of data from MS SQL Server to SQLite using TUniLoader, if the Source dataset works in UniDirectional mode. Try copying the data from MS SQL Server to MS SQL Server into different database instances and check how much memory the application consumes and whether the Out of memory exception occurs.

[ertank]

Hello Stellar,

Sent a direct e-mail with demo data & application to re-produce the problem. Hope that can help you to actually re-produce.

Regards,
Ertan Küçükoğlu

[Stellar]

Thank you for the information, we found larger memory consumption in Direct mode with enabled UniDirectional. We'll fix this behavior in one of the next UniDAC builds.
At the moment, to solve the issue, you can use OLEDB to connect to SQL Server.

SQLSERVER.SpecificOptions.Add('Provider=prAuto');