"Live Data Windows Mode" in UniDAC

[Ivan_Carpio89]

Hello,

One question, is there something similar to FireDAC's "Live Data Window Mode" in UniDAC?

Thanks in advance.

[Ivan_Carpio89]

Hello, sorry, I did not say, what if the "Live Data Window" functionality exists in the TUniTable?

I need to get the records in memory, for small portions or pages, as they use them, that is, on demand.

[azyk]

In UniDAC, there is the SmartFetch mode. This feature similar to the Live Data Window. When the SmartFetch mode is enabled and LiveBlock=True, memory consumption is minimized - a block of memory is allocated for the number of records specified in the FetchRows property. When fetching subsequent records, new memory is not allocated and the records that went beyond the block are deleted from the memory.

When the SmartFetch mode is enabled, the TUniTable dataset modifies the table data directly on the server side. In this case, no data caching on the client side occurs.

Learn more about TSmartFetchOptions in our online documentation: https://www.devart.com/unidac/docs/deva ... embers.htm

[Ivan_Carpio89]

This property had already tested:

Code: Select all

UniTable1.SmartFetch.Enabled: = True;

However, I observed in the SQL Server Profiler that at the time of activation the TUniTable performs the following query:

Code: Select all

SELECT Field_Key1, Field_Key2
FROM MyTable

Considering that this table has a million records, this has a considerable performance impact. I understand that "SmartFetch" is similar to "Live Data Window" and that could have differences, but I would like to know how to configure the TUniTable to have the following functionality:

Code: Select all

//Get only one or two records from the table
UniTable1.Active:= True;

While not UniTable1.Eof do begin
    //Some code

    //Get blocks of 30 records as you need them and remove the blocks unnecessary
    UniTable1.Next;
end;//While

I use:
-SQL Server 2014
-RAD Studio XE7
-UniDAC 7.4.11

Thanks for the support.

[azyk]

Code: Select all

UniTable1.SmartFetch.LiveBlock := True;
UniTable1.SmartFetch.Enabled := True;
UniTable1.SpecificOptions.Values['SQL Server.FetchAll'] := 'False';
UniTable1.FetchRows := 1;
//Get only one or two records from the table
UniTable1.Active:= True;

UniTable1.FetchRows := 30;
UniTable1.Active:= True;
While not UniTable1.Eof do begin
    //Some code

    //Get blocks of 30 records as you need them and remove the blocks unnecessary
    UniTable1.Next;
end;//While

Also you can use server cursors of SQL Server. In order for a dataset to start using a server cursor, before opening a dataset, you should set the TUniTable.SpecificOptions.Values['SQL Server.CursorType'] property to one of these values: 'ctStatic', 'ctKeyset', or 'ctDynamic'.

You can read about using server cursors in UniDAC: https://www.devart.com/unidac/docs/usin ... StoredProc
The description of server cursors of SQL Server at MSDN: http://technet.microsoft.com/en-us/libr ... 110).aspx

[Ivan_Carpio89]

UniTable1.SmartFetch.LiveBlock := True;
UniTable1.SmartFetch.Enabled := True;
UniTable1.SpecificOptions.Values['SQL Server.FetchAll'] := 'False';
UniTable1.FetchRows := 1;
//Get only one or two records from the table
UniTable1.Active:= True;

I did a test, and when activating the TUniTable, it consults all the records of the table of the database. Reviewing the SQL Server profiler executes:

Code: Select all

SELECT FieldPK1, FIeldPK2 FROM MyTable

[Stellar]

SELECT FieldPK1, FIeldPK2 FROM MyTable

With the enabled SmartFetch option, UniDAC executes a preliminary request to the server to build a list of key field values.
This request is required to organize quick navigation across a huge dataset and, in general, improves performance.

[Ivan_Carpio89]

Okay. It is complicated for me, because I have tables with more than 1 million records, and to be activating TUniTable's is slow because the SQL sentences take time. Also the memory of my application grows to 1.5 GB only by activating TUniTable, this is why all the records in the table are brought to memory.

Any suggestions with the use of the TUniTable, or any recommendation of another equivalent component in UniDAC?

Thank you for your support.

[FCS]

Hi,

Maybe this will be helpful for you if you only want to read data for scan it.

Code: Select all

  UQ_PG:=TUniQuery.Create(nil);
  UQ_PG.Connection           := DM_01.UniConnection1;
  UQ_PG.UniDirectional       := true;
  UQ_PG.FetchRows            := 1000;
  UQ_PG.SQL.Clear;
  UQ_PG.SQL.Add('SELECT * FROM xxx_table; ');
  UQ_PG.Open;
  while not UQ_PG.Eof do begin
     ...........
     UQ_PG.Next; 
  end;
  UQ_PG.Close;
  UQ_PG.Free;

I use it in an import/export procedure when I need only read data. This solves the problem of memory usage.

Regards
Michal

[Ivan_Carpio89]

Thank you! But, I do need to modify (CRUD) the rows.

Regards

[FCS]

Hi,

I didn't test it, but probably the edit is possible in the read part of records. In unidirection=true, you can not navigate backward, especially before previous part of records. Try test this.

Regards
Michal

[ertank]

Thank you! But, I do need to modify (CRUD) the rows.

Regards

Hello,

I see your code works in one direction that is always fetching next records. You may consider using UniDirectional=True
that will help you work on a single record at a time and dramatically reduces memory consumption.

However, you cannot use a TDBGrid or similar to display data as this kind of component needs UniDirectional=False by design.

[azyk]

Okay. It is complicated for me, because I have tables with more than 1 million records, and to be activating TUniTable's is slow because the SQL sentences take time. Also the memory of my application grows to 1.5 GB only by activating TUniTable, this is why all the records in the table are brought to memory.

Any suggestions with the use of the TUniTable, or any recommendation of another equivalent component in UniDAC?

Please set the credentials in the UniDAC_SmartFetch console application and run it. Tell us about the results.

Code: Select all

program UniDAC_SmartFetch;

{$APPTYPE CONSOLE}
{$R *.res}

uses
  SysUtils,
  UniProvider,
  SQLServerUniProvider,
  Uni,
  ActiveX,
  psAPI,
  Winapi.Windows;

var
  Con: TUniConnection;
  UniTable: TUniTable;

function CurrentProcessMemoryMB: Cardinal;
var
  MemCounters: TProcessMemoryCounters;
begin
  MemCounters.cb := SizeOf(MemCounters);
  if GetProcessMemoryInfo(GetCurrentProcess,
      @MemCounters,
      SizeOf(MemCounters)) then
    Result := MemCounters.WorkingSetSize div (1024*1024)
  else
    RaiseLastOSError;
end;

begin
  CoInitialize(nil);
  try
    Con := TUniConnection.Create(nil);
    try

      Con.ProviderName := 'SQL Server';
//  Set up credentials here
      Con.Server := 'XXXXX';
      Con.Database := 'XXXXX';
      Con.Username := 'XXXXX';
      Con.Password := 'XXXXX';
      Con.Connect;

      UniTable := TUniTable.Create(nil);
      try
        UniTable.Connection := Con;

        UniTable.SmartFetch.LiveBlock := False;
        UniTable.SmartFetch.Enabled := False;
        UniTable.SpecificOptions.Values['SQL Server.FetchAll'] := 'True';
        UniTable.FetchRows := 25;

        UniTable.SQL.Text := 'select * from MyTable';
        UniTable.Active:= True;
        while not UniTable.eof do begin
          UniTable.Next;
          UniTable.Fields[0].Value;
        end;

        Writeln(Format('SmartFetch=False. Memory usage=%d Mb', [CurrentProcessMemoryMB]));
      finally
        UniTable.Free;
      end;

      UniTable := TUniTable.Create(nil);
      try
        UniTable.Connection := Con;

        UniTable.SmartFetch.LiveBlock := True;
        UniTable.SmartFetch.Enabled := True;
        UniTable.SpecificOptions.Values['SQL Server.FetchAll'] := 'False';
        UniTable.FetchRows := 25;

        UniTable.SQL.Text := 'select * from MyTable';
        UniTable.Active:= True;
        while not UniTable.eof do begin
          UniTable.Next;
          UniTable.Fields[0].Value;
        end;

        Writeln(Format('SmartFetch=True.  Memory usage=%d Mb', [CurrentProcessMemoryMB]));
      finally
        UniTable.Free;
      end;

    finally
      Con.Free;
    end;
  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;
  Writeln('Press ENTER to exit.');

  Readln;
end.

[Ivan_Carpio89]

Thank you! But, I do need to modify (CRUD) the rows.

Regards

Hello,

I see your code works in one direction that is always fetching next records. You may consider using UniDirectional=True
that will help you work on a single record at a time and dramatically reduces memory consumption.

However, you cannot use a TDBGrid or similar to display data as this kind of component needs UniDirectional=False by design.

Hello, I could not use Unidirectional in True because checking the documentation indicates that the rows can not be modified:

https://www.devart.com/unidac/docs/deva ... tional.htm

...However, UniDirectional datasets cannot be modified.

[Ivan_Carpio89]

Okay. It is complicated for me, because I have tables with more than 1 million records, and to be activating TUniTable's is slow because the SQL sentences take time. Also the memory of my application grows to 1.5 GB only by activating TUniTable, this is why all the records in the table are brought to memory.

Any suggestions with the use of the TUniTable, or any recommendation of another equivalent component in UniDAC?

Please set the credentials in the UniDAC_SmartFetch console application and run it. Tell us about the results.

Code: Select all

program UniDAC_SmartFetch;

{$APPTYPE CONSOLE}
{$R *.res}

uses
  SysUtils,
  UniProvider,
  SQLServerUniProvider,
  Uni,
  ActiveX,
  psAPI,
  Winapi.Windows;

var
  Con: TUniConnection;
  UniTable: TUniTable;

function CurrentProcessMemoryMB: Cardinal;
var
  MemCounters: TProcessMemoryCounters;
begin
  MemCounters.cb := SizeOf(MemCounters);
  if GetProcessMemoryInfo(GetCurrentProcess,
      @MemCounters,
      SizeOf(MemCounters)) then
    Result := MemCounters.WorkingSetSize div (1024*1024)
  else
    RaiseLastOSError;
end;

begin
  CoInitialize(nil);
  try
    Con := TUniConnection.Create(nil);
    try

      Con.ProviderName := 'SQL Server';
//  Set up credentials here
      Con.Server := 'XXXXX';
      Con.Database := 'XXXXX';
      Con.Username := 'XXXXX';
      Con.Password := 'XXXXX';
      Con.Connect;

      UniTable := TUniTable.Create(nil);
      try
        UniTable.Connection := Con;

        UniTable.SmartFetch.LiveBlock := False;
        UniTable.SmartFetch.Enabled := False;
        UniTable.SpecificOptions.Values['SQL Server.FetchAll'] := 'True';
        UniTable.FetchRows := 25;

        UniTable.SQL.Text := 'select * from MyTable';
        UniTable.Active:= True;
        while not UniTable.eof do begin
          UniTable.Next;
          UniTable.Fields[0].Value;
        end;

        Writeln(Format('SmartFetch=False. Memory usage=%d Mb', [CurrentProcessMemoryMB]));
      finally
        UniTable.Free;
      end;

      UniTable := TUniTable.Create(nil);
      try
        UniTable.Connection := Con;

        UniTable.SmartFetch.LiveBlock := True;
        UniTable.SmartFetch.Enabled := True;
        UniTable.SpecificOptions.Values['SQL Server.FetchAll'] := 'False';
        UniTable.FetchRows := 25;

        UniTable.SQL.Text := 'select * from MyTable';
        UniTable.Active:= True;
        while not UniTable.eof do begin
          UniTable.Next;
          UniTable.Fields[0].Value;
        end;

        Writeln(Format('SmartFetch=True.  Memory usage=%d Mb', [CurrentProcessMemoryMB]));
      finally
        UniTable.Free;
      end;

    finally
      Con.Free;
    end;
  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;
  Writeln('Press ENTER to exit.');

  Readln;
end.

Hello, I already did the test, I added that I obtained the duration of the test, and the result was the following:

Duration = 00:01:24.244. SmartFetch = False. Memory usage = 4633 Mb

Duration = 00:07:47,482. SmartFetch = True. Memory usage = 1614 Mb

I observe the following:

1.- With Smart Fetch the memory went down from 4.6 GB to 1.6 GB.
2.- The duration increased, going from 1 minute to 7 minutes. This has its explanation since now it consults the fields of the table for every 25 rows.

Doubt:

1.- If the table is in constant change, that is, is being affected by INSERT's, UPDATE's and DELETE's, have you considered that the WHERE was as follows ?:

Code: Select all

SELECT *
FROM MyTable
WHERE (PK_FIELD1 =: p1 AND PK_FIELD2 =: p2 AND PK_FIELD3>: p3) OR
     (PK_FIELD1 =: p1 AND PK_FIELD2>: p2) OR
     (PK_FIELD1>: p1)
ORDER BY PK_FIELD1 ASC, PK_FIELD2 ASC, PK_FIELD3 ASC
OFFSET 0 ROWS FETCH FIRST 100 ROWS ONLY

My question is why the logic of the TUniTable with SmartFetch in True, from what I understand is:

1.- Get all the rows of the table, only by consulting the fields of the primary key.
2.- In each Next the fields of the first 25 rows are obtained, and so on for every 25 rows traveled.

I commented to you the first thing I'm looking for is that when I migrate from TFDTable to TUniTable, I have the lowest
cost in having to change our code in our projects, because they are large projects (in source code).
Now, in the future we are changing the use of the TFDTable by TFDQuery, to only consult what is used
in each function that we have, but at the moment it is a change that is in transition, and we are doing it little by little, that is why I am reviewing which configuration in the TUniTable is the most viable, or which component of the UniDAC to use, that we are interested in using the UniDAC components.

Regards!