Devart Forums

Discussion forums for open issues and questions concerning database tools, data access components and developer tools from Devart
https://forums.devart.com/

SmartFetch high memory usage on SQL Server

https://forums.devart.com/viewtopic.php?f=28&t=33458

Page 1 of 1

SmartFetch high memory usage on SQL Server

Posted: Mon 04 Apr 2016 15:51
by cross_join
Hello,

I'm testing SmartFetch with SQL Server on some large table (several millions lines, about 20 columns, no BLOBs or texts). All connection settings are by default so OLEDB client should be used.

Indeed, SmartFetch is more rapid to extract data but the memory consumption is the same as I have without SmartFetch.
What's happened?
Open without SmartFetch
Elapsed 22 sec
Memory usage: 1377 MB
Open with SmartFetch, LiveBlock = false
Elapsed 5 sec
Memory usage: 1409 MB
Open with SmartFetch, LiveBlock = true
Elapsed 6 sec
Memory usage: 1409 MB

Code: Select all

  MemUsage1 := CurrentProcessMemory;
  Started := Now;
  Memo1.Lines.Add('Open without SmartFetch');
  UniTable1.Open;
  Memo1.Lines.Add(Format('Elapsed %d sec', [SecondsBetween(Now, Started)]));
  Memo1.Lines.Add(Format('Memory usage: %d MB', [(CurrentProcessMemory - MemUsage1) div (1024 * 1024)]));
  UniTable1.Close;

  Memo1.Lines.Add('Open with SmartFetch, LiveBlock = false');
  UniTable1.SmartFetch.Enabled := true;
  UniTable1.SmartFetch.LiveBlock := false;
  MemUsage1 := CurrentProcessMemory;
  Started := Now;
  UniTable1.Open;
  Memo1.Lines.Add(Format('Elapsed %d sec', [SecondsBetween(Now, Started)]));
  Memo1.Lines.Add(Format('Memory usage: %d MB', [(CurrentProcessMemory - MemUsage1) div (1024 * 1024)]));
  UniTable1.Close;

  Memo1.Lines.Add('Open with SmartFetch, LiveBlock = true');
  UniTable1.SmartFetch.Enabled := true;
  UniTable1.SmartFetch.LiveBlock := true;
  MemUsage1 := CurrentProcessMemory;
  Started := Now;
  UniTable1.Open;
  Memo1.Lines.Add(Format('Elapsed %d sec', [SecondsBetween(Now, Started)]));
  Memo1.Lines.Add(Format('Memory usage: %d MB', [(CurrentProcessMemory - MemUsage1) div (1024 * 1024)]));
  UniTable1.Close;

Re: SmartFetch high memory usage on SQL Server

Posted: Mon 11 Apr 2016 09:18
by AlexP
Hello,

Currently, SmartFetch is effective for queries in which complex fields are used: LOB, BLOB, extString. For simple fields, memory will be allocated as in normal mode (not using SmartFetch).
We will change behavior, so that memory is allocated for key fields only..

Re: SmartFetch high memory usage on SQL Server

Posted: Mon 11 Apr 2016 09:25
by cross_join
AlexP, thank you for answer.
It would be an important evolution of SmartFetch closing to smart BDE behaviour, because the custom option "FetchAll=False" lead to double connection in TUniTable component out of transaction scope.

Re: SmartFetch high memory usage on SQL Server

Posted: Thu 14 Apr 2016 09:54
by AlexP
Please describe in more details the problems you have encountered while using the FetchAll property - and we will try to help you solve them.

Re: SmartFetch high memory usage on SQL Server

Posted: Thu 14 Apr 2016 10:25
by cross_join
AlexP wrote:Please describe in more details the problems you have encountered while using the FetchAll property - and we will try to help you solve them.
Here is an old discussion about (in Russian)

With FetchAll=True for every TUniTable an additional connection and transaction is created.
With many legacy TUniTable this lead to errors like "cannot create new transaction because capacity was exceeded".

Indeed SQL Server doesn't allow multiples transactions in the same connection. But it allows multiples cursors. I hope SmartFetch will use only one connection and won't use transactions but corresponding cursor's type.

P.S. MSSQL server cursors API reference

Re: SmartFetch high memory usage on SQL Server

Posted: Wed 27 Apr 2016 11:54
by azyk
To resolve the issue, try using Direct mode. To use Direct Mode for SQL Server, set the 'OLEDBProvider' option in the connection options to 'prDirect'. For example:

Code: Select all

UniConnection.SpecificOptions.Values['OLEDBProvider'] := 'prDirect';

Re: SmartFetch high memory usage on SQL Server

Posted: Wed 27 Apr 2016 17:33
by cross_join
No, direct mode doesn't resolve this issue.

Code: Select all

UniConnection1.SpecificOptions.Values['Provider'] := 'prDirect';
"Out of memory" error when SmartFetch is disabled (in 32-bits application) so it consumes more memory than OLEDB or native client.

For SmartFetch:
Open with SmartFetch, LiveBlock = false
Elapsed 3 sec
Memory usage: 1062 MB
Open with SmartFetch, LiveBlock = true
Elapsed 3 sec
Memory usage: 1060 MB
Also known issue that "Provider=prDirect" lead to fetching all rows from table regardless FetchAll=False.

Re: SmartFetch high memory usage on SQL Server

Posted: Thu 05 May 2016 10:02
by azyk
We failed to reproduce the specified behavior, when dataset fetch all data from table. Please try compose a small example allowing to reproduce the issue and send it to andreyz*devart*com.

We tried to reproduce it with the following code:

Code: Select all

UniConnection1.SpecificOptions.Values['SQL Server.Provider'] := 'prDirect';
UniTable1.SpecificOptions.Values['SQL Server.FetchAll'] := 'False';
UniTable1.SmartFetch.Enabled := False;

UniTable1.Open;

Re: SmartFetch high memory usage on SQL Server

Posted: Fri 06 May 2016 07:53
by cross_join
azyk wrote:We failed to reproduce the specified behavior, when dataset fetch all data from table. Please try compose a small example allowing to reproduce the issue and send it to andreyz*devart*com.

We tried to reproduce it with the following code:

Code: Select all

UniConnection1.SpecificOptions.Values['SQL Server.Provider'] := 'prDirect';
UniTable1.SpecificOptions.Values['SQL Server.FetchAll'] := 'False';
UniTable1.SmartFetch.Enabled := False;

UniTable1.Open;
Of corse you failed to reproduce because you're using FetchAll = 'False' !
FetchAll is "true" by default and set it to "false" is not usable in "real world" SQL Server's applications because of additional connection and transaction for every TUniTable component.
See my explanation above (http://forums.devart.com/viewtopic.php?p=115848#p115848).

Re: SmartFetch high memory usage on SQL Server

Posted: Thu 19 May 2016 10:52
by azyk
cross_join wrote: Also known issue that "Provider=prDirect" lead to fetching all rows from table regardless FetchAll=False.
We failed to reproduce the specified behavior, when dataset fetch all data from table. Please try compose a small example allowing to reproduce the issue and send it to andreyz*devart*com.

We tried to reproduce it with the following code:

Code: Select all

UniConnection1.SpecificOptions.Values['SQL Server.Provider'] := 'prDirect';
UniTable1.SpecificOptions.Values['SQL Server.FetchAll'] := 'False';
UniTable1.SmartFetch.Enabled := False;

UniTable1.Open;

Re: SmartFetch high memory usage on SQL Server

Posted: Mon 30 May 2016 10:27
by cross_join
azyk wrote:We failed to reproduce the specified behavior, when dataset fetch all data from table. Please try compose a small example allowing to reproduce the issue and send it to andreyz*devart*com.

We tried to reproduce it with the following code:

Code: Select all

UniConnection1.SpecificOptions.Values['SQL Server.Provider'] := 'prDirect';
UniTable1.SpecificOptions.Values['SQL Server.FetchAll'] := 'False';
UniTable1.SmartFetch.Enabled := False;

UniTable1.Open;
What exactly did you fail to reproduce???
Your code lead to fetch all table's rows to dataset. It's known bug of prDirect mode regardless FetchAll option's value.
Try it with any table and check UniTable1.RecordCount if OutOfMemory error won't be shown before :)

Re: SmartFetch high memory usage on SQL Server

Posted: Wed 01 Jun 2016 10:01
by ViktorV
We still can't reproduce the specified behavior, when dataset fetches all data from table. On the latest SDAC version 7.3.12, after executing the TUniTable.Open method, when the property TUniTable.SpecificOptions.Values['SQL Server.FetchAll'] is set to False, the value of the property TUnitable.RecordCount is equal to the value of the property TUnitable.FetchRows. The value of the property TUnitable.RecordCount is equal to the number of records returned by the SELECT query only when the property TUnitable.Options.QueryRecCount is set to True. This behavior is standard for our components. See more information about this property in UniDAC help: https://www.devart.com/unidac/docs/?dev ... ccount.htm
Please make sure you are using the latest SDAC version and that the property TUnitable.Options.QueryRecCount is set to False. If its is so, then please compose a small sample reproducing the issue and send it to viktorv*devart*com. In addition, specify the used SQL Server version.
All times are UTC
Page 1 of 1
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/