"Live Data Windows Mode" in UniDAC
-
- Posts: 29
- Joined: Mon 10 Dec 2018 23:49
"Live Data Windows Mode" in UniDAC
Hello,
One question, is there something similar to FireDAC's "Live Data Window Mode" in UniDAC?
Thanks in advance.
One question, is there something similar to FireDAC's "Live Data Window Mode" in UniDAC?
Thanks in advance.
-
- Posts: 29
- Joined: Mon 10 Dec 2018 23:49
Re: "Live Data Windows Mode" in UniDAC
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.
I need to get the records in memory, for small portions or pages, as they use them, that is, on demand.
Re: "Live Data Windows Mode" in UniDAC
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
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
-
- Posts: 29
- Joined: Mon 10 Dec 2018 23:49
Re: "Live Data Windows Mode" in UniDAC
This property had already tested:
However, I observed in the SQL Server Profiler that at the time of activation the TUniTable performs the following query:
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:
I use:
-SQL Server 2014
-RAD Studio XE7
-UniDAC 7.4.11
Thanks for the support.
Code: Select all
UniTable1.SmartFetch.Enabled: = True;
Code: Select all
SELECT Field_Key1, Field_Key2
FROM MyTable
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
-SQL Server 2014
-RAD Studio XE7
-UniDAC 7.4.11
Thanks for the support.
Re: "Live Data Windows Mode" in UniDAC
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
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
-
- Posts: 29
- Joined: Mon 10 Dec 2018 23:49
Re: "Live Data Windows Mode" in UniDAC
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: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;
Code: Select all
SELECT FieldPK1, FIeldPK2 FROM MyTable
Re: "Live Data Windows Mode" in UniDAC
With the enabled SmartFetch option, UniDAC executes a preliminary request to the server to build a list of key field values.SELECT FieldPK1, FIeldPK2 FROM MyTable
This request is required to organize quick navigation across a huge dataset and, in general, improves performance.
-
- Posts: 29
- Joined: Mon 10 Dec 2018 23:49
Re: "Live Data Windows Mode" in UniDAC
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.
Any suggestions with the use of the TUniTable, or any recommendation of another equivalent component in UniDAC?
Thank you for your support.
Re: "Live Data Windows Mode" in UniDAC
Hi,
Maybe this will be helpful for you if you only want to read data for scan it.
I use it in an import/export procedure when I need only read data. This solves the problem of memory usage.
Regards
Michal
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;
Regards
Michal
-
- Posts: 29
- Joined: Mon 10 Dec 2018 23:49
Re: "Live Data Windows Mode" in UniDAC
Thank you! But, I do need to modify (CRUD) the rows.
Regards
Regards
Re: "Live Data Windows Mode" in UniDAC
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
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
Re: "Live Data Windows Mode" in UniDAC
Hello,Ivan_Carpio89 wrote: ↑Wed 19 Dec 2018 18:05 Thank you! But, I do need to modify (CRUD) the rows.
Regards
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.
Re: "Live Data Windows Mode" in UniDAC
Please set the credentials in the UniDAC_SmartFetch console application and run it. Tell us about the results.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?
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.
-
- Posts: 29
- Joined: Mon 10 Dec 2018 23:49
Re: "Live Data Windows Mode" in UniDAC
Hello, I could not use Unidirectional in True because checking the documentation indicates that the rows can not be modified:ertank wrote: ↑Thu 20 Dec 2018 07:00Hello,Ivan_Carpio89 wrote: ↑Wed 19 Dec 2018 18:05 Thank you! But, I do need to modify (CRUD) the rows.
Regards
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.
https://www.devart.com/unidac/docs/deva ... tional.htm
...However, UniDirectional datasets cannot be modified.
-
- Posts: 29
- Joined: Mon 10 Dec 2018 23:49
Re: "Live Data Windows Mode" in UniDAC
Hello, I already did the test, I added that I obtained the duration of the test, and the result was the following:azyk wrote: ↑Thu 20 Dec 2018 14:40Please set the credentials in the UniDAC_SmartFetch console application and run it. Tell us about the results.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?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.
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
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!