Page 1 of 1
TZAbstractDataSet too slow to iterate 7668 rows
Posted: 23.04.2021, 09:49
by louis
This code, with Delphi 2010 and FB 2.5.9, takes 11 seconds to complete:
Code: Select all
begin
Cursore := Screen.Cursor;
Screen.Cursor := crHourGlass;
Result := 0;
bmBookmark := ADataSet.GetBookmark;
ADataSet.DisableControls;
try
ADataSet.First;
while (not ADataSet.Eof) do
begin
if (ADataSet.FieldByName(AField).AsInteger >= Result) then
Result := ADataSet.FieldByName(AField).AsInteger + 1;
ADataSet.Next;
end;
finally
ADataSet.GotoBookmark(bmBookmark);
ADataSet.FreeBookmark(bmBookmark);
ADataSet.EnableControls;
Screen.Cursor := Cursore;
end;
end;
How I can speed up?
Thanks
Re: TZAbstractDataSet too slow to iterate 7668 rows
Posted: 23.04.2021, 10:47
by marsupilami
You could try to see what happens when you use the Zeos 8.0 Beta. It also really depends on the data that is loaded into the dataset. It also could be a problem on the firebird side. If it is a real firebird server: Is processing time consumed by the Delphi application or by the firbird process?
Re: TZAbstractDataSet too slow to iterate 7668 rows
Posted: 23.04.2021, 12:03
by louis
marsupilami wrote: ↑23.04.2021, 10:47
You could try to see what happens when you use the Zeos 8.0 Beta.
At a moment I cant try with this version,
t also really depends on the data that is loaded into the dataset.
Data fields are:
Integer x 2
Smallint x 7
Char(29) x 2,
Char(4) x 1,
Char(2) x 1,
Varchar(480) x 1,
Varchar(10) x 1,
Varchar(60) x 1,
Numeric(7,4) x 1,
Numeric(18,4) x 4,
Float x 10,
Date x 1
It also could be a problem on the firebird side. If it is a real firebird server: Is processing time consumed by the Delphi application or by the firbird process?
Reading by Windows Task Manager: Delphi = 51% - Firebird = 0.8% during iteration.
Other Suggestions?
Thanks.
Re: TZAbstractDataSet too slow to iterate 7668 rows
Posted: 23.04.2021, 13:53
by marsupilami
louis wrote: ↑23.04.2021, 12:03
Other Suggestions?
Indeed. Windows 64 Bits is slow when changing the cursor for 32 Bits applications. If you didn't disable the SQLHourglass cursor on the connection, try the following code:
Code: Select all
begin
//Cursore := Screen.Cursor;
//Screen.Cursor := crHourGlass;
ADataSet.Connection.ShowSQLHourglass;
Result := 0;
bmBookmark := ADataSet.GetBookmark;
ADataSet.DisableControls;
try
ADataSet.First;
while (not ADataSet.Eof) do
begin
if (ADataSet.FieldByName(AField).AsInteger >= Result) then
Result := ADataSet.FieldByName(AField).AsInteger + 1;
ADataSet.Next;
end;
finally
ADataSet.GotoBookmark(bmBookmark);
ADataSet.FreeBookmark(bmBookmark);
ADataSet.EnableControls;
//Screen.Cursor := Cursore;
ADataSet.Connection.HideSQLHourglass;
end;
end;
Re: TZAbstractDataSet too slow to iterate 7668 rows
Posted: 23.04.2021, 15:28
by louis
marsupilami wrote: ↑23.04.2021, 13:53If you didn't disable the SQLHourglass cursor on the connection, try the following code:
Takes more than 1 second longer
Thanks
Re: TZAbstractDataSet too slow to iterate 7668 rows
Posted: 23.04.2021, 17:24
by miab3
Hi,
For me, it takes 5 seconds for 1 million iterations(without crHourGlass).
If dbgrid is connected, it takes 1 second, but the application starts 4 seconds (loads data to the cache and dbgrid).
procedure TForm1.Button1Click(Sender: TObject);
var
Res1:integer;
AField:string;
bmBookmark:TBookmark;
begin
AField:='TINT';
// Cursore := Screen.Cursor;
// Screen.Cursor := crHourGlass;
Res1 := 0;
bmBookmark := ADataSet.GetBookmark;
ADataSet.DisableControls;
try
ADataSet.First;
while (not ADataSet.Eof) do
begin
if (ADataSet.FieldByName(AField).AsInteger >= Res1) then
Res1 := ADataSet.FieldByName(AField).AsInteger + 1;
ADataSet.Next;
end;
finally
ADataSet.GotoBookmark(bmBookmark);
ADataSet.FreeBookmark(bmBookmark);
ADataSet.EnableControls;
// Screen.Cursor := Cursore;
end;
Edit1.Text:=IntToStr(Res1);
end;
Zeos8 svn7460; Firebird-2.5.9.27152-0_x64; Delphi 10.3.3-Win32 or Win64 on Win10-64
Michał
Re: TZAbstractDataSet too slow to iterate 7668 rows
Posted: 23.04.2021, 18:03
by louis
miab3 wrote: ↑23.04.2021, 17:24
For me, it takes 5 seconds for 1 million iterations(without crHourGlass)
Zeos8 svn7460; Firebird-2.5.9.27152-0_x64; Delphi 10.3.3-Win32 on Win10-64
I have a problem to install Zeos8 because in this project I need to replace hundred of TFloatField to TBCDField but those TFloatField often have a Validate function combined.
I don't know how to replace those TFloatFields without risking to introduce some bugs dooing to losing some functions.
Any suggestion?
Thanks
Re: TZAbstractDataSet too slow to iterate 7668 rows
Posted: 24.04.2021, 08:30
by marsupilami
Usually I would use find and replace in the dfm files to just change the type information.
Re: TZAbstractDataSet too slow to iterate 7668 rows
Posted: 24.04.2021, 11:33
by louis
marsupilami wrote: ↑24.04.2021, 08:30
Usually I would use find and replace in the dfm files to just change the type information.
Do you mean:
1) view as text
2) search and replace?
Zeos8 is usable in production?
Thanks
Re: TZAbstractDataSet too slow to iterate 7668 rows
Posted: 24.04.2021, 19:31
by aehimself
louis wrote: ↑24.04.2021, 11:33Zeos8 is usable in production?
Absolutely. I even used it in production when it was still called 7.3. There were no deal-breakers, only some minor inconveniences - pretty easy to work around.
For SQLite, MySQL, MSSQL (FreeTDS) and Oracle, that is. I have no experience with the other protocols.
Re: TZAbstractDataSet too slow to iterate 7668 rows
Posted: 24.04.2021, 22:04
by marsupilami
louis wrote: ↑24.04.2021, 11:33
Do you mean:
1) view as text
2) search and replace?
More or less - yes. I am storing my DFMs as text for ages now. So I can edit them with any editor and also get the ability to see changes in SVN.
Honestly - I wonder what happens on your computer. Usually I would suggest to try a profiler and see where time is spent. Some profilers I have found for now:
I do have some (ancient) experience on GpProfile. But maybe one of the others is better for your problem.
Re: TZAbstractDataSet too slow to iterate 7668 rows
Posted: 01.05.2021, 11:50
by DPStano
if your dataset is TZquery you can iterate over DbcResultSet, s/t like
Code: Select all
with (DataSet as TZQuery).DbcResultSet do
begin
BeforeFirst;
while Next do
begin
GetIntByName('')
end;
end;