ADO vs ADO Express Time Trials (redux)

[Ian Boyd]

Test 1: Open results set
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]

I assume that ADO in your message refers to calling the ADO RecordSet
methods directly. What is ADO Express? Are you referring to the
CodeGear dbGo components?

--
Bill Todd (TeamB)

[Lluis Olle]

Hi,

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]

What is SDCAC ???

[Helmut Woess]

Am Sun, 30 Dec 2007 18:10:40 -0600 schrieb Del Murray:

> What is SDCAC ???

I think it should be SDAC from CoreLab:
http://crlab.com/sdac/

bye,
Helmut

[Del Murray]

That looks pretty interesting .. has anyone any comments about these
components ??
Are they bertter than DBGo ???

[Ian Boyd]

ADO Express was the name Borland gave to it's object wrappers around ADO. It
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]

"Del Murray" <Del.M...@CreditHawk.Net> wrote in message
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]

> That looks pretty interesting .. has anyone any comments about these
> 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]

I'm curious...

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...