ADO vs ADO Express Time Trials (redux)

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.


.

Relevant Pages

  • Re: ADO Command object in a Loop
    ... By default A2K references ADO version 2.1, ... downloaded MDAC 2.8 (which contains the ADO libraries), ... >I have a problem with a stored procedure call that occurs in a loop. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Performance problem
    ... I would suggest that you use dao (or ado) in this case. ... My guess is that the above loop to insert data will run about 100, ... Note that "jet" ships with windows xp, and vista, so you don't actualy have ... to install access to read/write mdb files on a windows box. ...
    (microsoft.public.access.gettingstarted)
  • Re: Bulk User Import Using a VBScript
    ... Using VBScript, you can use ADO and the FileSystemObject to read in a CSV ... then use a big old loop with variables instead of explicitly defining ...
    (microsoft.public.windows.server.active_directory)
  • ADO vs ADOExpress time trials. Not good for ADOExpress
    ... (ADOQuery.Open does not return until all rows are sitting client side) ... 28161ms to loop through the resultset using ADO Express ... using ADO directly (10x faster than ADOExpress) ...
    (borland.public.delphi.database.ado)
  • Re: How to modify the proxyAdress property in AD with VBScript
    ... multi-value attribute, the GetEx method will raise an error. ... and enumerate the users in a loop. ... you can use ADO to retrieve distinguishedName and proxyAddresses for all ... Otherwise loop through the array to check for the ...
    (microsoft.public.windows.server.scripting)