"Live Data Windows Mode" in UniDAC

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Ivan_Carpio89
Posts: 29
Joined: Mon 10 Dec 2018 23:49

"Live Data Windows Mode" in UniDAC

Post by Ivan_Carpio89 » Thu 13 Dec 2018 17:51

Hello,

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

Thanks in advance.

Ivan_Carpio89
Posts: 29
Joined: Mon 10 Dec 2018 23:49

Re: "Live Data Windows Mode" in UniDAC

Post by Ivan_Carpio89 » Thu 13 Dec 2018 23:39

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
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: "Live Data Windows Mode" in UniDAC

Post by azyk » Fri 14 Dec 2018 11:39

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
Posts: 29
Joined: Mon 10 Dec 2018 23:49

Re: "Live Data Windows Mode" in UniDAC

Post by Ivan_Carpio89 » Fri 14 Dec 2018 16:40

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
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: "Live Data Windows Mode" in UniDAC

Post by azyk » Tue 18 Dec 2018 13:52

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
Posts: 29
Joined: Mon 10 Dec 2018 23:49

Re: "Live Data Windows Mode" in UniDAC

Post by Ivan_Carpio89 » Tue 18 Dec 2018 18:34

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
Devart Team
Posts: 304
Joined: Tue 03 Oct 2017 11:00

Re: "Live Data Windows Mode" in UniDAC

Post by Stellar » Wed 19 Dec 2018 09:14

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
Posts: 29
Joined: Mon 10 Dec 2018 23:49

Re: "Live Data Windows Mode" in UniDAC

Post by Ivan_Carpio89 » Wed 19 Dec 2018 14:44

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
Posts: 165
Joined: Sat 23 Feb 2013 18:46

Re: "Live Data Windows Mode" in UniDAC

Post by FCS » Wed 19 Dec 2018 17:20

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
Posts: 29
Joined: Mon 10 Dec 2018 23:49

Re: "Live Data Windows Mode" in UniDAC

Post by Ivan_Carpio89 » Wed 19 Dec 2018 18:05

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

Regards

FCS
Posts: 165
Joined: Sat 23 Feb 2013 18:46

Re: "Live Data Windows Mode" in UniDAC

Post by FCS » Wed 19 Dec 2018 19:08

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
Posts: 148
Joined: Wed 13 Jan 2016 16:00

Re: "Live Data Windows Mode" in UniDAC

Post by ertank » Thu 20 Dec 2018 07:00

Ivan_Carpio89 wrote:
Wed 19 Dec 2018 18:05
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
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: "Live Data Windows Mode" in UniDAC

Post by azyk » Thu 20 Dec 2018 14:40

Ivan_Carpio89 wrote:
Wed 19 Dec 2018 14:44
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
Posts: 29
Joined: Mon 10 Dec 2018 23:49

Re: "Live Data Windows Mode" in UniDAC

Post by Ivan_Carpio89 » Thu 20 Dec 2018 17:11

ertank wrote:
Thu 20 Dec 2018 07:00
Ivan_Carpio89 wrote:
Wed 19 Dec 2018 18:05
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
Posts: 29
Joined: Mon 10 Dec 2018 23:49

Re: "Live Data Windows Mode" in UniDAC

Post by Ivan_Carpio89 » Thu 20 Dec 2018 17:59

azyk wrote:
Thu 20 Dec 2018 14:40
Ivan_Carpio89 wrote:
Wed 19 Dec 2018 14:44
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!

Post Reply