ADO vs ADO Express Time Trials (redux)
Ian Boyd
ADO: 8.3 seconds
ADO Express: 8.3 seconds
Test 2: Looping through a results set
ADO: 0.45 seconds
ADO Express: 338 seconds
ADO Express: 0.95 seconds (with DisableControls)
Test 3: Field Lookup by Name
ADO: 16.3 seconds
ADO Express: 130 seconds
Test 4a: Field Lookup by Ordinal
ADO: 1.6 seconds
ADO Express: 0.66 seconds
Test 5: Access field value
ADO: 14.8 seconds
ADO Express: 48.1 seconds
Total
====
Time to get it all done (field by name)
ADO
Open: 8.3s (20.8%)
Loop: 0.45s (1.1%)
Field by Name: 16.3s (40.9%)
Field Value: 14.8 (37.1%)
Total: 39.9s (100%)
ADO Express
Open: 8.3s (4.4%)
Loop: 0.95s (0.5%)
Field by Name: 130s (69.4%)
Field Value: 48.1s (25.7%)
Total: 187.35s (100%)
Time to get it all done (field by ordinal)
ADO
Open: 8.3s (33.0%)
Loop: 0.45 (1.8%)
Field by Ordinal: 1.6s (6.4%)
Field Value: 14.8s (58.8%)
Total: 25.2s (100%)
ADO Express
Open: 8.3s (14.3%)
Loop: 0.95s (1.6%)
Field by Ordinal: 0.66s (1.1%)
Field Value: 48.1s (82.9%)
Total: 48.1s (100%)
Summary
Your database work will be 80% faster if you change any code that is of the
form:
procedure GetValuesFromQuery(Query: TADOQuery);
begin
FName := Query.FieldByName('Name').AsString;
FAddress := Query.FieldByName('Address').AsString;
...
end;
to
procedure GetValuesFromQuery(Query: TADOQuery);
begin
GetValuesFromRecordset(Query.Recordset);
end;
procedure GetValuesFromRecordset(Recordset: ADOInt._Recordset);
begin
FName := VarAsString(Recordset.Fields.Item['Name']);
FAddress := VarAsString(Recordset.Fields.Items['Address']);
...
end;
ADO Express is much slower than ADO. Even ADO hobbled with by name field
lookups is faster than ADO Express by ordinal. You can use ADO Express to
open a query, but as soon as you do bypass anything written by Borland and
go straight to the
ADOQuery.Recordset
to perform all your field and value lookups. The largest single performance
penalty is trying to access a field value in ADO Express. ADO Express
provides to way to access a field's underlying Variant value. Because of
this, you have to access the field through the Query.Recordset.Fields
collection.
i originally posted this 2 years ago (http://tinyurl.com/33ygmg). It was
time to revisit it because i was once again trying to make things faster,
and i forgot a lot of the specifics that i had discovered.
Bill Todd
methods directly. What is ADO Express? Are you referring to the
CodeGear dbGo components?
--
Bill Todd (TeamB)
Lluis Olle
We have a process that's using ADOExpress ( Delphi 5 ) / dbGO ( Delphi 7 ),
which executes Querys to an MS SQL Server 2005 DB, and writes the whole
result set ( WriteInteger, WriteString, ... ), into a ZLib compressed Stream
... Basically, reads thousands of records, and processes every field in each
record, to put the contents into a Stream.
For the worst Query, which returned something like 500.000 records, with 15
fields, it took 30 seconds to do that .. which I always considered to be a
normal durantion, and I assumed that was mainly due to the ZLib compression.
When we changed the process not to use ADOExpress / dbGo but SDCAC instead,
we get astonished to see the 30 second process reduced to only 3 seconds !
We couldn't belive ...
As the process is the same, the amount of data is the same, the source of
the data is the same, and the ZLib compression is the same, then ... what is
left ? ADOExpress / dbGo ... really, if you have to process 1000 records,
you'll not notice, but that compoment is really, really inefficient ...
whatever you do to bypass it, will produce a big preformance gain.
Regards,
Lluis
Del Murray
Helmut Woess
> What is SDCAC ???
I think it should be SDAC from CoreLab:
http://crlab.com/sdac/
bye,
Helmut
Del Murray
components ??
Are they bertter than DBGo ???
Ian Boyd
was released as an afterthought for Delphi 5.
It was later renamed into something else, but is still the set of
components:
TADOConnection
TADOQuery
etc
"Bill Todd" <n...@no.com> wrote in message
news:4777fbb1$1...@newsgroups.borland.com...
Ian Boyd
news:477833f2$1...@newsgroups.borland.com...
> What is SDCAC ???
It is a Delphi wrapper around OLEDB (like ADO is a wrapper around OLEDB).
In both cases, avoiding what Borland wrote gives much improved performance.
Ian Boyd
> components ??
> Are they bertter than DBGo ???
dbGo *is* ADO Express. Borland renamed it for Delphi 6.
So to answer your question: Yes, it is better than dbGo. But you don't need
a separate product, just talk to the ADO Recordset yourself, rather than
using a TADOQuery or TADODataSet.
Steve Zimmelman
Is
FName := VarAsString(Recordset.Fields.Item['Name']) ;
Faster than
FName := VarAsString(Recordset.Fields['Name']) ;
?
-Steve-
"Ian Boyd" <ian.borla...@avatopia.com> wrote in message
news:4777f4e9$1...@newsgroups.borland.com...