DAMemDataTable 2,5x more slow in Delphi XE that Delphi 2007?


(suportedcoimbracom) #1

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)


(suportedcoimbracom) #3

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;


(suportedcoimbracom) #4

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


(EvgenyK) #5

can you retest the same version of DA with Delphi 2007 (i.e. non-unicode version) and Delphi Seattle (unicode version), pls ?


(suportedcoimbracom) #6

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


(suportedcoimbracom) #7

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


(EvgenyK) #8

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)


(suportedcoimbracom) #9

“Strongly Typed” / “Business Rules ID”. In his view there is a possibility to return to speed in delphi 7 levels?


(EvgenyK) #10

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.


(suportedcoimbracom) #11

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?


(EvgenyK) #12

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


(suportedcoimbracom) #13

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

Code:

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 Question

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?


(EvgenyK) #14

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.


(suportedcoimbracom) #15

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


(suportedcoimbracom) #16

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


(suportedcoimbracom) #17

The problem is ftBCD in driver ADO?

Created https://quality.embarcadero.com/browse/RSP-18569

Sample show the problem if ftBCD versus driver ADO?


(EvgenyK) #19

can you perform your tests without ADO driver, pls?
we have a lot of other drivers that work with MSSQL like FireDAC, SDAC, etc


(suportedcoimbracom) #20

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]);


(suportedcoimbracom) #21

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)^);


(suportedcoimbracom) #22

In both cases added function to convert:

SET: BCDToCurr

GET: CurrToBCD

Before (d2007) not convert, why?