SmartFetch high memory usage on SQL Server

[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;

[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..

[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.

[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.

[cross_join]

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

[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';

[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.

[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;

[cross_join]

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).

[azyk]

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;

[cross_join]

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

[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.