BorlandTalk.com

John Hansen · Guest

If I create a TADOQuery and open it with a SQL statement which returns the
top N rows of a very large database table and then iterate throught the
records using Next until EOF and access the values of a few fields in the
query within the while loop I get hugely different timing results using dbGo
in VCL.Win32 than when I am using dbGo in VCL.Net. I used Delphi 2005 in
both cases.

Iterations .NET Win32
100 34 14
1000 220 42
10000 2093 331
20000 4200 676
30000 6280 1009
40000 8416 1350
50000 10526 1726
60000 12713 2064

The code looks like this:

dbQuery := TADOQuery.Create(nil);
try
dbQuery.Connection := dbConnection;
dbQuery.SQL.Text := 'select top ' + IntToStr(iterations) +
' FOR_GRP_SK, START_MOMENT, NCO, NCH, AHT ' +
' from ITD_FOR_CALL_STAT';
dbQuery.DisableControls;
dbQuery.Open;
fldSK := dbQuery.FieldByName('FOR_GRP_SK');
fldNCO := dbQuery.FieldByName('NCO');
fldNCH := dbQuery.FieldByName('NCH');
fldAHT := dbQuery.FieldByName('AHT');
while not dbQuery.Eof do
begin
sk := fldSK.AsFloat;
if not fldNCO.IsNull then
nco := fldNCO.AsInteger
else
nco := 0;

if not fldNCH.IsNull then
nch := fldNCH.AsInteger
else
nch := 0;

if not fldAHT.IsNull then
aht := fldAHT.AsFloat
else
aht := 0;

dbQuery.Next;
end;
finally
dbQuery.Free;
end;

When I profile the .Net code it shows the following:

Method Name % in Method % with Children Called Average
Borland.Vcl.TCustomADODataSet.GetFieldData 28.3 62.2 120,888 20.3
Borland.Vcl.TCustomADODataSet.UpdateRecordSetPosition 11.2 21 80,800
12
Borland.Vcl.TCustomADODataSet.InternalGotoBookmark 10.6 10.7 80,801
11.4
Borland.Vcl.TCustomADODataSet.InternalGetRecord 5.6 6.6 20,204 24.1

Basically, the current implementation of dbGo in VCL.Net is unusably slow
for large datasets. And the slowness is not due to COM interop. If I do a
similar iteration but use the underlying RecordSet and its Field objects
(i.e., direct ADO access) then I get timing numbers that are almost as fast
as the Win32 numbers above.

I would like to have some feedback from Borland regarding this problem. Is
this a known issue that I just have to live with? Looking at the source
code, it seems like there is way too much allocating and freeing of memory
for buffers and copying of data back and forth between the DataSet buffers
and the underlying ADO Recordset/Fields. Will this be addressed at all in a
future release of Delphi or is dbGo a dying set of database access
components?

Can someone please try to verify my results by running a similar test?

Thanks,

John Hansen