Re: ADO vs ADOExpress time trials. Not good for ADOExpress
Kevin Frevert
"Ian Boyd" <ian.borla...@avatopia.com> wrote in message
news:42a5b427$1...@newsgroups.borland.com...
> Results:
> Loop Results ADO: 0.2s
> Loop Results ADOExpress: 28s
> Get Values ADO: 4.5s
> Get Values ADO Express: 46.6s
>
> Conclusions:
> Obvious.
>
> Comments?
Try again using TADODataSet and call DisableControls before opening the
dataset. I'd be curious of the results.
krf
Ian Boyd
20881 rows in the table
Test No.1
ADOQuery1.Open
Result: 1472ms to return 20881 rows from the server to the client
(ADOQuery.Open does not return until all rows are sitting client side)
Test No. 2
while not ADOQuery1.EOF do
ADOQuery1.Next
Result: 28161ms to loop through the resultset using ADO Express
Test No. 3
while not ADOQuery1.EOF do
begin
ADOQuery1.FieldByName('PatronGUID').AsString; //uniqueidentifier
ADOQuery1.FieldByName('AccountNumber').AsString; //int]
ADOQuery1.FieldByName('FirstName').AsString; //varchar] (
ADOQuery1.FieldByName('MiddleInitial').AsString;//varchar] (
ADOQuery1.FieldByName('Lastname').AsString; //varchar] (
ADOQuery1.FieldByName('AccountType').AsString; //varchar] (
ADOQuery1.FieldByName('AccountCreditStatus').AsString; //varchar] (
ADOQuery1.FieldByName('CreditLimit').AsString; //money]
ADOQuery1.FieldByName('CardLevelSequenceNumber').AsString; //int]
ADOQuery1.FieldByName('ResidentialAddress1').AsString; //varchar] (
ADOQuery1.FieldByName('ResidentialAddress2').AsString; //varchar] (
ADOQuery1.FieldByName('ResidentialAddress3').AsString; //varchar] (
ADOQuery1.FieldByName('ResidentialCity').AsString; //varchar] (
ADOQuery1.FieldByName('ResidentialPostalCode').AsString; //varchar]
(
ADOQuery1.FieldByName('ResidentialRegion').AsString; //varchar] (
ADOQuery1.FieldByName('ResidentialCountry').AsString; //varchar] (
ADOQuery1.FieldByName('ResidentialPhoneNumber').AsString; //varchar]
(
ADOQuery1.FieldByName('EmailAddress').AsString; //varchar] (
ADOQuery1.FieldByName('ParkingMode').AsString; //varchar] (
ADOQuery1.FieldByName('ParkingDays').AsString; //tinyint]
ADOQuery1.FieldByName('LastUpdateFromGamingSystem').AsString;
//datetime]
ADOQuery1.Next;
end
Result: 46416ms to loop through the results, and interrogate all fields
(by name)
Test No. 3
rs: _Recordset;
rs := ADOQuery1.Recordset;
while not rs.EOF do
rs.MoveNext;
Result: 230ms to loop through the result set using ADO directly (122x
faster than ADOExpress)
Test No. 4
rs: _Recordset;
rs := ADOQuery1.Recordset;
while not rs.EOF do
begin
rs.Fields.Item['PatronGUID'].Value;
rs.Fields.Item['PatronGUID'].Value; //uniqueidentifier
rs.Fields.Item['AccountNumber'].Value; //int]
rs.Fields.Item['FirstName'].Value; //varchar] (
rs.Fields.Item['MiddleInitial'].Value;//varchar] (
rs.Fields.Item['Lastname'].Value; //varchar] (
rs.Fields.Item['AccountType'].Value; //varchar] (
rs.Fields.Item['AccountCreditStatus'].Value; //varchar] (
rs.Fields.Item['CreditLimit'].Value; //money]
rs.Fields.Item['CardLevelSequenceNumber'].Value; //int]
rs.Fields.Item['ResidentialAddress1'].Value; //varchar] (
rs.Fields.Item['ResidentialAddress2'].Value; //varchar] (
rs.Fields.Item['ResidentialAddress3'].Value; //varchar] (
rs.Fields.Item['ResidentialCity'].Value; //varchar] (
rs.Fields.Item['ResidentialPostalCode'].Value; //varchar] (
rs.Fields.Item['ResidentialRegion'].Value; //varchar] (
rs.Fields.Item['ResidentialCountry'].Value; //varchar] (
rs.Fields.Item['ResidentialPhoneNumber'].Value; //varchar] (
rs.Fields.Item['EmailAddress'].Value; //varchar] (
rs.Fields.Item['ParkingMode'].Value; //varchar] (
rs.Fields.Item['ParkingDays'].Value; //tinyint]
rs.Fields.Item['LastUpdateFromGamingSystem'].Value; //datetime]
rs.MoveNext;
end;
Result: 4526ms to loop through the resultset and interrogate all fields
using ADO directly (10x faster than ADOExpress)
Ian Boyd
"Kevin Frevert" <ke...@workdrinkingdietcoke.com> wrote in message
news:42a5b527$1...@newsgroups.borland.com...
>
> "Ian Boyd" <ian.borla...@avatopia.com> wrote in message
> news:42a5b427$1...@newsgroups.borland.com...
>> Results:
>> Loop Results ADO: 0.2s
>> Loop Results ADOExpress: 28s
>> Get Values ADO: 4.5s
>> Get Values ADO Express: 46.6s
>>
> dataset. I'd be curious of the results.
Results are interesting, and disturbing.
1. Using TADODataSet (verses TADOQuery) makes no difference.
2. Adding .DisableControls before iterating the results set yields:
Loop Results ADO: 0.2s
Loop Results ADOExpress wo/DisableControls: 28s
Loop Results ADOExpress w/DisableControls: 0.5s
Get Values ADO: 4.7s
Get Values ADOExpress wo/DisableControls: 46.6s
Get Values ADOExpress w/DisableControls: 17s
Much improvement in simply looping through a resultsets.
Now ADOExpress is only 3x slower than ADO.
Results are disturbing, because there is no attached DB controls. There is
only code.
i.e. why does DisableControls make a difference?
Viatcheslav V. Vassiliev
if ControlsDisabled then
RecordNumber := -2 else
RecordNumber := Recordset.AbsolutePosition;
//------------------------------------------
Regards,
Vassiliev V. V.
http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
ADO.Net
http://www.oledbdirect.com - The fastest way to access MS SQL Server,
MS Jet (Access) and Interbase (through OLEDB)
"Ian Boyd" <ian.borla...@avatopia.com> сообщил/сообщила в новостях
следующее: news:42a5d857$1...@newsgroups.borland.com...
Kevin Frevert
> Much improvement in simply looping through a resultsets.
> Now ADOExpress is only 3x slower than ADO.
>
Just curious, try using persistant fields instead of calling FieldByName.
> Results are disturbing, because there is no attached DB controls. There is
> only code.
> i.e. why does DisableControls make a difference?
>
Accessing via the 'raw' ADO level is going to be faster, but I'm more
curious than disturbed by the behavior. Kind of like why does my Delphi
disappear if I have a query using an aggregate (SUM, MIN, MAX) inside a
sub-select?
Step 1: At design time, set a TADODataSet.CommandText to
Select
I.InvoiceID,
I.InvoiceDateTime,
(Select SUM(L.Qty * L.PriceEach)
From InvoiceItems L
Where L.InvoiceID = I.InvoiceID) [LineItemTotal]
From
Invoices I
Where
I.InvoiceID = :InvoiceID
Step 2: Click OK (in the design-time commandtext editor)
*POOF* Delphi disappears.
Sometimes I get one of those intuitive COM errors 'Unexpected error', then
*POOF*, but mostly *POOF*
I can reproduce the behavior on my laptop, desktop and a test machine, but
none of our other 4 developers can (we all have identical desktop machines).
Oh well, back to work.
krf
Ian Boyd
Absoutely not. Persistant fields are the work of the devil. We stopped using
those 7 years ago.
Evil i tells ya. Evil!
Besides, we absoutely never ever use data-aware controls. And we never ever
use a dedicated TADOQuery for each query we run. We create a query component
on the stack, and get results through it.
i.e.
qryLocal := TADOQuery.Create(nil);
try
qryLocal.Recordset := Datamodule.ADOConnection1.Execute(szQuery);
while not qryLocal.EOF do
begin
end;
finally
qryLocal.Free;
end;
Kevin Frevert
> > Just curious, try using persistant fields instead of calling
FieldByName.
>
> Absoutely not. Persistant fields are the work of the devil. We stopped
using
> those 7 years ago.
>
> Evil i tells ya. Evil!
>
> Besides, we absoutely never ever use data-aware controls.
As complex as our apps get, I'd rather have a bad venereal disease than
*not* use data-bound controls (DevExpress's DBGrid rocks! :).
>And we never ever
> use a dedicated TADOQuery for each query we run. We create a query
component
> on the stack, and get results through it.
>
> i.e.
> qryLocal := TADOQuery.Create(nil);
> try
> qryLocal.Recordset := Datamodule.ADOConnection1.Execute(szQuery);
> while not qryLocal.EOF do
> begin
> end;
> finally
> qryLocal.Free;
> end;
You can avoid the TADOQuery all together and use the returned RecordSet
directly..
var
RecordSet :_RecordSet;
begin
RecordSet := Datamodule.ADOConnection1.Execute(szQuery);
RecordSet.MoveFirst;
while NOT(RecordSet.EOF) do
begin
{do your stuff}
RecordSet.MoveNext;
end;
end;
Good luck,
krf
Ian Boyd
> directly..
> var
> RecordSet :_RecordSet;
> begin
> RecordSet := Datamodule.ADOConnection1.Execute(szQuery);
> RecordSet.MoveFirst;
> while NOT(RecordSet.EOF) do
> begin
> {do your stuff}
> RecordSet.MoveNext;
> end;
> end;
We know, but since the syntax:
Recordset.Field.Item['MyFieldName'].Value
is awkward, if i even got it right. Additionally, that returns a variant.
Now there are very careful rules for converting a variant to a data type,
and not all conversions can be done by a RTL function.
So it becomes safer/faster/easier to re-use the code invented inside
.AsString, .AsInteger, .AsDateTime, .AsCurrency.
But considering it is 3x faster to do the variant conversions yourself, than
let TField do it - i might be forcing people here to learn the syntax.
From some more testing, the following has about the same speed as ADO:
Query.FieldByName('Fieldxx');
What slows it down is:
Query.FieldByName('Fieldxx').AsString;
If there was a way i could get the raw value from a TField as a variant, and
convert it myself, i would.
Ian Boyd
Loop Results Get Values
ADO: 0.2s 4.7s
ADOExpress: 28.0s 46.6s
ADOExpress w/DisableControls: 0.5s 17.0s
Kevin Frevert
>
> Recordset.Field.Item['MyFieldName'].Value
>
> is awkward, if i even got it right. Additionally, that returns a variant.
>
Microsoft design something awkward and hard to use, no way!..:)
> From some more testing, the following has about the same speed as ADO:
>
> Query.FieldByName('Fieldxx');
>
> What slows it down is:
> Query.FieldByName('Fieldxx').AsString;
>
>
> If there was a way i could get the raw value from a TField as a variant,
and
> convert it myself, i would.
>
Great thing about Delphi is you can (with enough time and money of course
:). There are other ways to get to MSSQL than ADOExpress
(http://www.oledbdirect.com). if .3 seconds difference between ADO directly
vs. ADOExpress is a big enough issue.
krf
Bill Todd
use FieldByNam since it requires a sequential search for the field name
every time you call it.
--
Bill Todd (TeamB)
Bill Todd
The point is to eliminate the sequential search through all of the
field names that FieldByName makes every time you reference a field.
--
Bill Todd (TeamB)
Ian Boyd
> The point is to eliminate the sequential search through all of the
> field names that FieldByName makes every time you reference a field.
Scroll to the end of the post you responded to:
>From some more testing, the following has about the same speed as ADO:
>
> Query.FieldByName('Fieldxx');
>
>What slows it down is:
> Query.FieldByName('Fieldxx').AsString;
>
>
>If there was a way i could get the raw value from a TField as a variant,
>and
>convert it myself, i would.
FieldByName isn't the problem, since the ADO equivalent
Query.Recordset.Fields.Items['Fieldxx']
is just a "bad", so it's a wash. Delphi loses the race inside TField.AsXxxx
Ian Boyd
"Bill Todd" <n...@no.com> wrote in message
news:42a63085$1...@newsgroups.borland.com...
> As Kevin pointed out the results are not terribly meaningful when you
> use FieldByNam since it requires a sequential search for the field name
> every time you call it.
Loop Results Get Values
ADO: 0.2s 4.7s
ADOExpress w/DisableControls: 0.5s 17.0s
ADO doesn't seem to suffer from the same problem.
Actually, as it turns out, FieldByName is not the problem. If you only call
Query.FieldByName('MyField')
it is as fast as ADO:
Query.Recordset.Fields.Items['MyField']
What is slowing down Delphi is the TField.AsXxxx methods.
Query.FieldByName('MyField').AsString;
it's faster to use
VarToStr(Query.Recordset.Fields.Items['MyField'].Value);
i wish there was a way to get the raw variant value that ADOExpress get from
the database. A syntax like the following doesn't work?
VarToStr(Query.FieldByName('MyField').AsVariant)
because the field is (for example) a TStringField. So the TStringField get's
the data into a string, and then converts it back into a variant for you.
Kostas Terzides
> it's faster to use
> VarToStr(Query.Recordset.Fields.Items['MyField'].Value);
>
> i wish there was a way to get the raw variant value that ADOExpress get from
> the database. A syntax like the following doesn't work?
>
> VarToStr(Query.FieldByName('MyField').AsVariant)
>
> because the field is (for example) a TStringField. So the TStringField get's
> the data into a string, and then converts it back into a variant for you.
>
>
>
What happens if you use:
Query.Fields[i].Value
It returns a variant.
Ian Boyd
> Query.Fields[i].Value
>
> It returns a variant.
Problem is that it doesn't actually return a variant.
If you look at the code:
function TStringField.GetAsVariant: Variant;
var
S: string;
begin
if GetValue(S) then Result := S else Result := Null;
end;
What it actually does it return a string, and then cast it to a variant.
Which is part of why ADOExpress is so slow.
Ian Boyd
function TStringField.GetAsString: string;
begin
if not GetValue(Result) then Result := '';
end;
function TStringField.GetValue(var Value: string): Boolean;
begin
Result := GetData(@Buffer);
...
end;
function TField.GetData(Buffer: Pointer; NativeFormat: Boolean = True):
Boolean;
begin
...
Result := FDataSet.GetFieldData(Self, Buffer, NativeFormat);
end;
function TCustomADODataSet.GetFieldData(Field: TField; Buffer: Pointer;
NativeFormat: Boolean): Boolean;
var
Data: OleVariant;
begin
...
Data := Recordset.Fields[Field.FieldNo-1].Value;
..
end;
Compare all that to:
Recordset.Fields[FieldNumber].Value
is skipping 4 functions, and 4 screens-full of code.
Kostas Terzides
be able to work with other data access technologies (dbexpress, BDE,
e.t.c), that's why the implementation is based on getting the data from
FDataset.
I can see this as a problem only as long as data aware controls are used
(and you mentioned you don't need them).
In all other cases (in your code) you can use RecordSet directly as you
suggest. If the real problem is the awkard syntax as you mentioned, then
subclass TAdoQuery (or TADoDataSet whatever you use) and add a simple
public function (or read only propery) to make you life easier,
something like:
function FieldValueByName(const FieldName:String):Variant;
begin
result:=RecordSet.Fields.Items[FieldName].Value;
end;
In this class you could also write your custom variant conversion
routines. This way you could "bypass" all TField involvement.