Board index » delphi » ADO Performance (Brian of TeamB?)

ADO Performance (Brian of TeamB?)

"Brian Bushay TeamB" <BBus...@Nmpls.com> wrote in message
news:398c8669.24565569@forums.Inprise.com...

Quote

> Ado is best for Access and MSSQL server.  There are probably better
choices for
> all other databases

I keep hearing how ADO is best, but don't seem to be able to extract any
sort of performance that makes it faster (or even as fast as) the BDE via
ODBC ... I am so keen to find out how to make the ADO faster than the BDE,
as it would do wonders for our IIS/COM applications...

i.e.   This quick & simple benchmark procedure takes almost twice as long
under ADO as it does BDE, with minor variations from modifying the ADO
connection parameters...

procedure TForm1.Button1Click(Sender: TObject);
var
  i: integer;
  Start: TDateTime;
begin
  ADOStoredProc1.Prepared := True;
  StoredProc1.Prepare;
  Start := Now;
  for i:=0 to 200 do begin
    {$ifdef DATAPOOL_ADO}
    ADOStoredProc1.Parameters.ParamByName('@CustomerID').Value :=
sl[Random(sl.count-1)];
    ADOStoredProc1.Open;
    DoRetrieve(ADOStoredProc1);
    ADOStoredProc1.Close;
    {$else}
    StoredProc1.ParamByName('@CustomerID').AsString :=
sl[Random(sl.count-1)];
    StoredProc1.Open;
    DoRetrieve(StoredProc1);
    StoredProc1.Close;
    {$endif}
  end;
  ShowMessage(TimeToStr(Now-Start));
end;

// Simple routine to access each field/row of a dataset
function TForm1.DoRetrieve(ADataset: TDataset): integer;
var
  s: string;
  i: integer;
begin
  while not ADataset.Eof do begin
    for i := 0 to ADataSet.Fields.Count-1 do
      s := ADataSet.Fields[i].AsString;
    ADataset.Next;
  end;
end;

 

Re:ADO Performance (Brian of TeamB?)


Laurie:

I think that ADO's real advantage - to this point - is that it exposes a lot
more capability than does its competition. If you need to do nested tables,
the using ADO really is much faster than doing it any other way.

If speed is the only consideration, check out www.kylecordes.com and look at
what is available for your particular database. I have read good things
about Direct Oracle Access, for example. Some of these products communicate
directly with the RDBMS's client software. If your database doesn't have an
OLE DB provider yet, see if you can find a product that will communicate
directly with its client software. That will - possibly - give you the best
performance.

Mike

Quote
Laurie <l...@iinet.net.au> wrote in message news:39890469_1@dnews...

> "Brian Bushay TeamB" <BBus...@Nmpls.com> wrote in message
> news:398c8669.24565569@forums.Inprise.com...

> > Ado is best for Access and MSSQL server.  There are probably better
> choices for
> > all other databases

> I keep hearing how ADO is best, but don't seem to be able to extract any
> sort of performance that makes it faster (or even as fast as) the BDE via
> ODBC ... I am so keen to find out how to make the ADO faster than the BDE,
> as it would do wonders for our IIS/COM applications...

> i.e.   This quick & simple benchmark procedure takes almost twice as long
> under ADO as it does BDE, with minor variations from modifying the ADO
> connection parameters...

> procedure TForm1.Button1Click(Sender: TObject);
> var
>   i: integer;
>   Start: TDateTime;
> begin
>   ADOStoredProc1.Prepared := True;
>   StoredProc1.Prepare;
>   Start := Now;
>   for i:=0 to 200 do begin
>     {$ifdef DATAPOOL_ADO}
>     ADOStoredProc1.Parameters.ParamByName('@CustomerID').Value :=
> sl[Random(sl.count-1)];
>     ADOStoredProc1.Open;
>     DoRetrieve(ADOStoredProc1);
>     ADOStoredProc1.Close;
>     {$else}
>     StoredProc1.ParamByName('@CustomerID').AsString :=
> sl[Random(sl.count-1)];
>     StoredProc1.Open;
>     DoRetrieve(StoredProc1);
>     StoredProc1.Close;
>     {$endif}
>   end;
>   ShowMessage(TimeToStr(Now-Start));
> end;

> // Simple routine to access each field/row of a dataset
> function TForm1.DoRetrieve(ADataset: TDataset): integer;
> var
>   s: string;
>   i: integer;
> begin
>   while not ADataset.Eof do begin
>     for i := 0 to ADataSet.Fields.Count-1 do
>       s := ADataSet.Fields[i].AsString;
>     ADataset.Next;
>   end;
> end;

Re:ADO Performance (Brian of TeamB?)


Quote
>I keep hearing how ADO is best, but don't seem to be able to extract any
>sort of performance that makes it faster (or even as fast as) the BDE via
>ODBC ... I am so keen to find out how to make the ADO faster than the BDE,
>as it would do wonders for our IIS/COM applications...

>i.e.   This quick & simple benchmark procedure takes almost twice as long
>under ADO as it does BDE, with minor variations from modifying the ADO
>connection parameters...

What database are you working with? At this time I would only recommend ADO for
Access and MSSQL
BDE is not likely to see any future revisions so that is one of the reasons for
migrating to BDE.  There are already many BDE problems with Access 2000 and some
with MSsql 7.

With your code make sure you are using serverside cursors that should make quite
a bit of difference.

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:ADO Performance (Brian of TeamB?)


Thanks Micheal for the www page ..  there's a couple of direct components I
shall check out (we using SQL Server 7)

..Laurie

"Michael J. Austin" <maus...@hevanet.com> wrote in message
news:39898f08_2@dnews...

Quote
> Laurie:

> I think that ADO's real advantage - to this point - is that it exposes a
lot
> more capability than does its competition. If you need to do nested
tables,
> the using ADO really is much faster than doing it any other way.

> If speed is the only consideration, check out www.kylecordes.com and look
at
> what is available for your particular database. I have read good things
> about Direct Oracle Access, for example. Some of these products
communicate
> directly with the RDBMS's client software. If your database doesn't have
an
> OLE DB provider yet, see if you can find a product that will communicate
> directly with its client software. That will - possibly - give you the
best
> performance.

> Mike

Re:ADO Performance (Brian of TeamB?)


Thanks for your comments Brian & Micheal, much appreciated.

    We are using SQL Server 7.   Just spent a couple of hours trying just
about every combination of access features there are for ADO, with only
minor increases in speed.

    Over 90% of our activity against SQL Server 7 is via 1 of 10 Stored
Procedures, which take a single parameter, and return less than 20 rows.
    I tried server side cursors, and it blew the results out to 1
minute -vs- 18 seconds for client side... and the BDE is performing the same
procedure/return of rows in < 3 seconds.   Even SQL Links takes 10 seconds!?

    (The test was to time how long it took for 200 x StoredProcedure +
Process Returned Rows)

    Best figures:
        ADO - 8 procedures+results / second,
        BDE/ODBC - 60 procedures+results / second.

    Am I chasing the holy grail trying to get the D5 Enterprise ADO
components to be as fast as the BDE?
    Does anyone have any benchmarks for such a simple test in which ADO goes
at least the same speed as the BDE?

Many thanks,
Laurie  :-)

p.s.  Here's the test routine:

procedure TForm1.Button1Click(Sender: TObject);
var
  n,i: integer;
  Start: TDateTime;
  function DoRetrieve(ADataset: TDataset): integer;
  var
    i: integer;
    s: string;
   begin
     while not ADataset.Eof do begin
       for i := 0 to ADataSet.Fields.Count-1 do
         s := ADataSet.Fields[i].AsString;
       Inc(n);
       ADataset.Next;
     end;
   end;
begin
  n :=0;
  ADOStoredProc1.Prepared := True;
  StoredProc1.Prepare;
  Start := Now;
  for i:=0 to 200 do begin
    {$ifdef ADO}
    ADOStoredProc1.Parameters.ParamByName('@CustomerID').Value :=
sl[Random(sl.count-1)]; // 1 of 1500 random ID's
    ADOStoredProc1.Open;
    DoRetrieve(Form1.ADOStoredProc1);
    ADOStoredProc1.Close;
    {$else}
    StoredProc1.ParamByName('@CustomerID').AsString :=
sl[Random(sl.count-1)];  // 1 of 1500 random ID's
    StoredProc1.Open;
    DoRetrieve(StoredProc1);
    StoredProc1.Close;
    {$endif}
  end;
  ShowMessage('Rows: '+IntToStr(n)+' - time '+TimeToStr(Now-Start));
end;

"Brian Bushay TeamB" <BBus...@Nmpls.com> wrote in message
news:3996241d.33848345@forums.Inprise.com...

Quote

> >I keep hearing how ADO is best, but don't seem to be able to extract any
> >sort of performance that makes it faster (or even as fast as) the BDE via
> >ODBC ... I am so keen to find out how to make the ADO faster than the
BDE,
> >as it would do wonders for our IIS/COM applications...

> >i.e.   This quick & simple benchmark procedure takes almost twice as long
> >under ADO as it does BDE, with minor variations from modifying the ADO
> >connection parameters...
> What database are you working with? At this time I would only recommend
ADO for
> Access and MSSQL
> BDE is not likely to see any future revisions so that is one of the
reasons for
> migrating to BDE.  There are already many BDE problems with Access 2000
and some
> with MSsql 7.

> With your code make sure you are using serverside cursors that should make
quite
> a bit of difference.

> --
> Brian Bushay (TeamB)
> Bbus...@NMPLS.com

Re:ADO Performance (Brian of TeamB?)


You can decrease the time that an TADODataSet takes to go through a
recordset by using the DisableControls Method of the TADOdataSet

Quote
"Laurie" <l...@iinet.net.au> wrote in message news:398a7b04_1@dnews...
> Thanks for your comments Brian & Micheal, much appreciated.

>     We are using SQL Server 7.   Just spent a couple of hours trying just
> about every combination of access features there are for ADO, with only
> minor increases in speed.

>     Over 90% of our activity against SQL Server 7 is via 1 of 10 Stored
> Procedures, which take a single parameter, and return less than 20 rows.

>     I tried server side cursors, and it blew the results out to 1
> minute -vs- 18 seconds for client side... and the BDE is performing the
same
> procedure/return of rows in < 3 seconds.   Even SQL Links takes 10
seconds!?

>     (The test was to time how long it took for 200 x StoredProcedure +
> Process Returned Rows)

>     Best figures:
>         ADO - 8 procedures+results / second,
>         BDE/ODBC - 60 procedures+results / second.

>     Am I chasing the holy grail trying to get the D5 Enterprise ADO
> components to be as fast as the BDE?
>     Does anyone have any benchmarks for such a simple test in which ADO
goes
> at least the same speed as the BDE?

> Many thanks,
> Laurie  :-)

> p.s.  Here's the test routine:

> procedure TForm1.Button1Click(Sender: TObject);
> var
>   n,i: integer;
>   Start: TDateTime;
>   function DoRetrieve(ADataset: TDataset): integer;
>   var
>     i: integer;
>     s: string;
>    begin
>      while not ADataset.Eof do begin
>        for i := 0 to ADataSet.Fields.Count-1 do
>          s := ADataSet.Fields[i].AsString;
>        Inc(n);
>        ADataset.Next;
>      end;
>    end;
> begin
>   n :=0;
>   ADOStoredProc1.Prepared := True;
>   StoredProc1.Prepare;
>   Start := Now;
>   for i:=0 to 200 do begin
>     {$ifdef ADO}
>     ADOStoredProc1.Parameters.ParamByName('@CustomerID').Value :=
> sl[Random(sl.count-1)]; // 1 of 1500 random ID's
>     ADOStoredProc1.Open;
>     DoRetrieve(Form1.ADOStoredProc1);
>     ADOStoredProc1.Close;
>     {$else}
>     StoredProc1.ParamByName('@CustomerID').AsString :=
> sl[Random(sl.count-1)];  // 1 of 1500 random ID's
>     StoredProc1.Open;
>     DoRetrieve(StoredProc1);
>     StoredProc1.Close;
>     {$endif}
>   end;
>   ShowMessage('Rows: '+IntToStr(n)+' - time '+TimeToStr(Now-Start));
> end;

> "Brian Bushay TeamB" <BBus...@Nmpls.com> wrote in message
> news:3996241d.33848345@forums.Inprise.com...

> > >I keep hearing how ADO is best, but don't seem to be able to extract
any
> > >sort of performance that makes it faster (or even as fast as) the BDE
via
> > >ODBC ... I am so keen to find out how to make the ADO faster than the
> BDE,
> > >as it would do wonders for our IIS/COM applications...

> > >i.e.   This quick & simple benchmark procedure takes almost twice as
long
> > >under ADO as it does BDE, with minor variations from modifying the ADO
> > >connection parameters...
> > What database are you working with? At this time I would only recommend
> ADO for
> > Access and MSSQL
> > BDE is not likely to see any future revisions so that is one of the
> reasons for
> > migrating to BDE.  There are already many BDE problems with Access 2000
> and some
> > with MSsql 7.

> > With your code make sure you are using serverside cursors that should
make
> quite
> > a bit of difference.

> > --
> > Brian Bushay (TeamB)
> > Bbus...@NMPLS.com

Other Threads