When read all records and all fields In Delphi 2007 is more fast with Delphi XE Seatle. With 200k records is bit more slow.
Data for case test :
Datasets: 1
Total Fields: 99, FieldsCalculeds: 11
Records: 9560
Time in seconds: delphi 2007: 0.6041; delphi seatle: 1.8550
In attachment:
Times.pdf with profiller of aqtime pro
Codigo.rar --> code of case test
Times.pdf (34.9 KB)
Codigo.rar (346.0 KB)
To Delphi Seatle alter to:
procedure TForm1.PreparaRegistrosDA;
begin
DARemoteDataAdapter1.DataStreamer := DABin2DataStreamer1;
DAMemDataTable1.RemoteDataAdapter := DARemoteDataAdapter1;
DAMemDataTable1.RemoteFetchEnabled := False;
DAMemDataTable1.LogicalName := ‘tbl_duprec_com_parc’;
DAMemDataTable1.LoadFromFile(‘basededados.da’);
DADataSource1.DataTable := DAMemDataTable1;
end;
Teste NEXT/GET with BusinessRulesId
->Delphi 2007: 438 ms Delphi Seattle: 1225 ms
Teste NEXT/GET without BusinessRulesId
->Delphi 2007: 344 ms Delphi Seattle: 607 ms
Teste EDIT after with BusinessRulesId
->Delphi 2007: 1000 ms Delphi Seattle: 3804 ms
Teste EDIT after without BusinessRulesId
->Delphi 2007: 3827 ms Delphi Seattle: 12874 ms
Delphi 2007 DAv7
Delphi Seatle DAv8
can you retest the same version of DA with Delphi 2007 (i.e. non-unicode version) and Delphi Seattle (unicode version), pls ?
Teste NEXT/GET with BusinessRulesId
Delphi 2007 DA 5: 438 ms --- Delphi 2007 DA 8: 760 ms --- Delphi Seattle DA 8: 1225 ms
Teste NEXT/GET without BusinessRulesId
Delphi 2007 DA 5: 344 ms --- Delphi 2007 DA 8: 446 ms --- Delphi Seattle DA 8: 607 ms
Teste EDIT after with BusinessRulesId
Delphi 2007 DA 5: 1000 ms --- Delphi 2007 DA 8: 3267 ms --- Delphi Seattle DA 8: 3804 ms
Teste EDIT after without BusinessRulesId
Delphi 2007 DA 5: 3827 ms --- Delphi 2007 DA 8: 7860 ms --- Delphi Seattle DA 8: 12874 ms
Correct name of text (inverter):
Teste EDIT without x with
Teste EDIT after without BusinessRulesId
Delphi 2007 DA 5: 1000 ms — Delphi 2007 DA 8: 3267 ms — Delphi Seattle DA 8: 3804 ms
Teste EDIT after with BusinessRulesId
Delphi 2007 DA 5: 3827 ms — Delphi 2007 DA 8: 7860 ms — Delphi Seattle DA 8: 12874 ms
I’ve created console version of your testcase and tested it with DA9 and different versions of delphi.
as you can see from my tests, different versions of Delphi give different results so I think, something inside delphi (i.e. DB.pas) was changed.
non-unicode versions of Delphi give better results, later XE versions have worse time, Delphi 10.* have better optimization in comparing with XE:
Compiler version is 15 (Delphi 7)
410 milisegundos - Nulos:405799 without BusinessRulesID
668 milisegundos - Nulos:319562 with BusinessRulesID
3 segundos 321 milisegundos - edit without BusinessRulesID
6 segundos 873 milisegundos - edit with BusinessRulesID
Compiler version is 18 (Delphi 2007)
454 milisegundos - Nulos:405799 without BusinessRulesID
782 milisegundos - Nulos:319562 with BusinessRulesID
3 segundos 536 milisegundos - edit without BusinessRulesID
8 segundos 160 milisegundos - edit with BusinessRulesID
Compiler version is 21 (Delphi 2010)
400 milisegundos - Nulos:405799 without BusinessRulesID
694 milisegundos - Nulos:319562 with BusinessRulesID
3 segundos 28 milisegundos - edit without BusinessRulesID
7 segundos 160 milisegundos - edit with BusinessRulesID
Compiler version is 23 (XE2)
505 milisegundos - Nulos:405799 without BusinessRulesID
995 milisegundos - Nulos:319562 with BusinessRulesID
3 segundos 8 milisegundos - edit without BusinessRulesID
10 segundos 47 milisegundos - edit with BusinessRulesID
Compiler version is 26 (XE5)
720 milisegundos - Nulos:405799 without BusinessRulesID
1 segundo 416 milisegundos - Nulos:319562 with BusinessRulesID
3 segundos 761 milisegundos - edit without BusinessRulesID
13 segundos 952 milisegundos - edit with BusinessRulesID
Compiler version is 28 (XE7)
710 milisegundos - Nulos:405799 without BusinessRulesID
1 segundo 413 milisegundos - Nulos:319562 with BusinessRulesID
3 segundos 726 milisegundos - edit without BusinessRulesID
13 segundos 846 milisegundos - edit with BusinessRulesID
Compiler version is 30 (Delphi 10 Seattle)
533 milisegundos - Nulos:405799 without BusinessRulesID
1 segundo 115 milisegundos - Nulos:319562 with BusinessRulesID
3 segundos 381 milisegundos - edit without BusinessRulesID
11 segundos 455 milisegundos - edit with BusinessRulesID
Compiler version is 31 (Delphi 10.1 Berlin)
537 milisegundos - Nulos:405799 without BusinessRulesID
1 segundo 122 milisegundos - Nulos:319562 with BusinessRulesID
3 segundos 381 milisegundos - edit without BusinessRulesID
11 segundos 524 milisegundos - edit with BusinessRulesID
console.zip (1.5 KB)
“Strongly Typed” / “Business Rules ID”. In his view there is a possibility to return to speed in delphi 7 levels?
the problem with TBOtbl_duprec_com_parc.OnCalcFields. it’s a quite slow method and fired when every field is modified.
I can recommend to use Enable*/Disable* methods like DisableEventHandlers/EnableEventHandlers for batch operations.
in this case, I’ve got these results:
Compiler version is 28
710 milisegundos - Nulos:405799 without BusinessRulesID
1 segundo 402 milisegundos - Nulos:319562 with BusinessRulesID
1 segundo 574 milisegundos - edit without BusinessRulesID
1 segundo 573 milisegundos - edit with BusinessRulesID
of cource, in some cases this solution isn’t accepted.
Thank you for advice, but,
in this case, that the calculed fields are useds to validate in beforepost and to get values to other objects, so, isn´t accepted.
And In his view there is a possibility to return to speed in delphi 7 levels?
the same DA code and the same PC was used for these tests.
one difference was - different versions of delphi was used for compiling.
the problem in your OnCalcField event - it slows entire progress:
Compiler version is 28
706 milisegundos - Nulos:405799 without BusinessRulesID
1 segundo 404 milisegundos - Nulos:319562 with BusinessRulesID
3 segundos 770 milisegundos - edit without BusinessRulesID
13 segundos 876 milisegundos - edit with BusinessRulesID
RecordCount is 9568
OnCalcField was fired 184707 times
try to optimize it
Changed (Optimized) onCalcFields e executed:
In attachments datas sumarizeds.
CASE 1 - set the eleven calculated fields to 0 with "strongly typed"
Compiler version is 30
654 milisegundos - Nulos:405799 without BusinessRulesID
1088 milisegundos - Nulos:300551 with BusinessRulesID
3952 milisegundos - edit without BusinessRulesID
8325 milisegundos - edit with BusinessRulesID
Compiler version is 18
374 milisegundos - Nulos:405799 without BusinessRulesID
437 milisegundos - Nulos:300551 with BusinessRulesID
2312 milisegundos - edit without BusinessRulesID
3016 milisegundos - edit with BusinessRulesID
CASE 2 - set the eleven calculated fields to 0 with fieldbyname
Compiler version is 30
654 milisegundos - Nulos:405799 without BusinessRulesID
1111 milisegundos - Nulos:300551 with BusinessRulesID
3960 milisegundos - edit without BusinessRulesID
8880 milisegundos - edit with BusinessRulesID
Compiler version is 18
375 milisegundos - Nulos:405799 without BusinessRulesID
671 milisegundos - Nulos:300551 with BusinessRulesID
2311 milisegundos - edit without BusinessRulesID
7422 milisegundos - edit with BusinessRulesID
CASE 3 - set the eleven calculated fields to other field with "strongly typed"
Compiler version is 30
651 milisegundos - Nulos:405799 without BusinessRulesID
1353 milisegundos - Nulos:300551 with BusinessRulesID
3958 milisegundos - edit without BusinessRulesID
13196 milisegundos - edit with BusinessRulesID
Compiler version is 18
360 milisegundos - Nulos:405799 without BusinessRulesID
514 milisegundos - Nulos:300551 with BusinessRulesID
2250 milisegundos - edit without BusinessRulesID
3827 milisegundos - edit with BusinessRulesID
CASE 4 - CalcFields original
Compiler version is 30
650 milisegundos - Nulos:405799 without BusinessRulesID
1335 milisegundos - Nulos:319562 with BusinessRulesID
3918 milisegundos - edit without BusinessRulesID
14007 milisegundos - edit with BusinessRulesID
Compiler version is 18
360 milisegundos - Nulos:405799 without BusinessRulesID
485 milisegundos - Nulos:319562 with BusinessRulesID
2203 milisegundos - edit without BusinessRulesID
4265 milisegundos - edit with BusinessRulesID
CASE 1 - set the eleven calculated fields to 0 with "strongly typed"
procedure TBOtbl_duprec_com_parc.OnCalcFields(DataTable: TDADataTable);
begin
inherited;
recp_valorAddTxEmiss:=0;
Diferenca := 0;
DiasEmAtraso := 0;
recpDlrIndice := 0;
recpDlrPgto := 0;
JuroPago_Pc_Mes := 0;
SituacaoPrazo := ‘’;
VlrComJuros:= 0;
Venc_Ano := 0;
Venc_Mes := 0;
PrazoEmDias := 0;
end;
CASE 2 - set the eleven calculated fields to 0 with fieldbyname
procedure TBOtbl_duprec_com_parc.OnCalcFields(DataTable: TDADataTable);
begin
inherited;
DataTable.Dataset.FieldByName(‘recp_valorAddTxEmiss’).AsCurrency := 0;
DataTable.Dataset.FieldByName(‘Diferenca’).AsCurrency := 0;
DataTable.Dataset.FieldByName(‘DiasEmAtraso’).AsCurrency := 0;
DataTable.Dataset.FieldByName(‘recpDlrIndice’).AsCurrency := 0;
DataTable.Dataset.FieldByName(‘recpDlrPgto’).AsCurrency := 0;
DataTable.Dataset.FieldByName(‘JuroPago_Pc_Mes’).AsCurrency := 0;
DataTable.Dataset.FieldByName(‘SituacaoPrazo’).AsString := ‘’;
DataTable.Dataset.FieldByName(‘VlrComJuros’).AsCurrency := 0;
DataTable.Dataset.FieldByName(‘Venc_Ano’).AsCurrency := 0;
DataTable.Dataset.FieldByName(‘Venc_Mes’).AsCurrency := 0;
DataTable.Dataset.FieldByName(‘PrazoEmDias’).AsCurrency := 0;
end;
CASE 3 - set the eleven calculated fields to other field with "strongly typed"
procedure TBOtbl_duprec_com_parc.OnCalcFields(DataTable: TDADataTable);
begin
inherited;
recp_valorAddTxEmiss:=recp_valor+recp_vlr_outros_acres;
Diferenca := recp_pgto_vlr - recp_valorAddTxEmiss;
DiasEmAtraso := Trunc(date-recp_dt_vencimento);
recpDlrIndice := recp_valor / 2;
recpDlrPgto := (recp_pgto_vlr/2)*recp_dlr_valor;
JuroPago_Pc_Mes := Diferenca;
SituacaoPrazo := ‘RECEBIDO’;
VlrComJuros:= recp_vlr_outros_acres;
Venc_Ano := StrToInt(FormatDateTime(‘yyyy’,recp_dt_vencimento));
Venc_Mes := StrToInt(FormatDateTime(‘mm’,recp_dt_vencimento));
PrazoEmDias := Trunc(recp_dt_vencimento-rec_dt_emiss);
end;
The datatable has 11 calculated fields, the simple fact of defining these fields doubles the time.
CASE 3 only set values.
Optimize for you is drop the code?
you can define these caclulated fields as usual fields (i.e. non-calculated) with LogChanges := False.
SQL can be like
SELECT
... ,
0 as Venc_Ano,
0 as Venc_Mes,
...
FROM ....
and calculate these fields only when it is needed, i.e.
DiasEmAtraso, Venc_Ano and Venc_Mes can be calculated only when recp_dt_vencimento is changed
of course, some initial calculation can be required at opening table, but it will more faster because it will be calculated once per record instead of once per changed field.
Test in IDE Tokio
Compiler version is 32 (Tokio)
532 milisegundos - Nulos:405799 without BusinessRulesID
1 segundo 50 milisegundos - Nulos:319562 with BusinessRulesID
3 segundos 208 milisegundos - edit without BusinessRulesID
10 segundos 905 milisegundos - edit with BusinessRulesID
Compiler version is 18 (D2007)
263 milisegundos - Nulos:405799 without BusinessRulesID
348 milisegundos - Nulos:319562 with BusinessRulesID
1 segundo 921 milisegundos - edit without BusinessRulesID
3 segundos 311 milisegundos - edit with BusinessRulesID
(Tokio) 532 milisegundos - Nulos:405799 without BusinessRulesID
(D2007) 263 milisegundos - Nulos:405799 without BusinessRulesID
(Tokio) 1 segundo 50 milisegundos - Nulos:319562 with BusinessRulesID
(D2007) 348 milisegundos - Nulos:319562 with BusinessRulesID
(Tokio) 3 segundos 208 milisegundos - edit without BusinessRulesID
(D2007) 1 segundo 921 milisegundos - edit without BusinessRulesID
(Tokio) 10 segundos 905 milisegundos - edit with BusinessRulesID
(D2007) 3 segundos 311 milisegundos - edit with BusinessRulesID
Compiler version is 30
521 milisegundos - Nulos:405799 without BusinessRulesID
1 segundo 60 milisegundos - Nulos:319562 with BusinessRulesID
3 segundos 230 milisegundos - edit without BusinessRulesID
11 segundos 48 milisegundos - edit with BusinessRulesID
The problem is ftBCD in driver ADO?
Created https://quality.embarcadero.com/browse/RSP-18569
Sample show the problem if ftBCD versus driver ADO?
can you perform your tests without ADO driver, pls?
we have a lot of other drivers that work with MSSQL like FireDAC, SDAC, etc
The change in get of field
///////////////////////////////////////////////////delphi d2007
function TDACustomField.GetAsCurrency: currency;
function TDAMemoryDataset.GetFieldData(Field: TField; Buffer: Pointer): Boolean;
…
if not IsReferencedField(Field.DataType) then begin
Move(Data^, Buffer^, FDataSizeArray[Field.Index])
procedure TDAMemoryDataset.DataConvert(Field: TField; Source,
Dest: Pointer; ToNative: Boolean);
…
Case Field.Datatype of
ftBCD: PCurrency(Dest)^ := PCurrency(Source)^;
///////////////////////////////////////////////////delphi xe
function TDAMemoryDataset.GetFieldData(Field: TField;{$IFDEF DELPHIXE4UP}var{$ENDIF} Buffer: Dataset_TValueBuffer): Boolean;
begin
Result := intGetFieldData(Field, Buffer);
end;
function TDAMemoryDataset.intGetFieldData(Field: TField;{$IFDEF DELPHIXE4UP}var{$ENDIF} Buffer: Dataset_TValueBuffer): Boolean;
…
{$ENDIF DA_FixedWideCharSupport}
else
Move(Data^, lbuf^, FDataSizeArray[i])
…
if Result and (FDataTypeArray[i] = ftBCD) then begin
CurrToBCD(PCurrency(lbuf)^, TBCD(lbuf^), 32, FFieldSizeArray[i]);
/////////////////////////////////////////////////// comparativo
In d2007 GetFieldData->Move->DataConvert
ftBCD: PCurrency(Dest)^ := PCurrency(Source)^;
In xe GetFieldData->intGetFieldData->
Move(Data^, lbuf^, FDataSizeArray[i])
if Result and (FDataTypeArray[i] = ftBCD) then begin
CurrToBCD(PCurrency(lbuf)^, TBCD(lbuf^), 32, FFieldSizeArray[i]);
The change in set of field
///////////////////////////////////////////////////DELPHI D2007
procedure TDACustomField.SetAsCurrency(const aValue: currency);
begin
if Assigned(fField) then
fField.AsCurrency := aValue
procedure TDAMemoryDataset.DataConvert(Field: TField; Source,
Case Field.Datatype of
ftBCD: PCurrency(Dest)^ := PCurrency(Source)^;
///////////////////////////////////////////////////DELPHI XE
procedure TDACustomField.SetAsCurrency(const aValue: currency);
begin
if Assigned(fField) then
fField.AsCurrency := aValue
procedure TDAMemoryDataset.DataConvert(Field: TField; Source: Dataset_TValueBuffer;
… aparente nenhum tratamento para ftBCD em DataConvert
procedure TDAMemoryDataset.SetFieldData(Field: TField; Buffer: Dataset_TValueBuffer);
…
InternalSetFieldData(Field, Buffer);
procedure TDAMemoryDataset.InternalSetFieldData(Field: TField; Buffer: Dataset_TValueBuffer);
…
ftBCD: BCDToCurr(PBCD(lbuf)^, PCurrency(Data)^);
///////////////////////////////////////////////////COMPARATIVO
D2007: ftBCD: PCurrency(Dest)^ := PCurrency(Source)^;
XE: ftBCD: BCDToCurr(PBCD(lbuf)^, PCurrency(Data)^);
In both cases added function to convert:
SET: BCDToCurr
GET: CurrToBCD
Before (d2007) not convert, why?