Discussion forums for open issues and questions concerning database tools, data access components and developer tools from Devart
https://forums.devart.com/
Code: Select all
UniTable1.SmartFetch.Enabled: = True;Code: Select all
SELECT Field_Key1, Field_Key2
FROM MyTableCode: 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;//WhileCode: 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;//WhileI 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 MyTableWith 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
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;
Hello,Ivan_Carpio89 wrote: ↑Wed 19 Dec 2018 18:05 Thank you! But, I do need to modify (CRUD) the rows.
Regards
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.
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.
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.
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