Board index » delphi » ADO vs. BDE in Accessing SQL Server 7

ADO vs. BDE in Accessing SQL Server 7

I have done some performance testing with both Delphi 5 Pro and Delphi 6 Ent
with
the latest update packs.  I have tried this on both Win2K and WinXP.

ADO is 15-20 times SLOWER for doing a simple query against a table of user
names.

Has anyone else noticed this horrible speed delay?
Are there any ideas in speeding up the delay?

Thanks in advance,
Gabriel Padilla

 

Re:ADO vs. BDE in Accessing SQL Server 7


Quote
Gabriel Padilla <gpadi...@starstream.net> wrote in message

news:3c0f0c42$1_1@dnews...

Quote
> I have done some performance testing with both Delphi 5 Pro and Delphi 6
Ent
> with
> the latest update packs.  I have tried this on both Win2K and WinXP.

> ADO is 15-20 times SLOWER for doing a simple query against a table of user
> names.

> Has anyone else noticed this horrible speed delay?
> Are there any ideas in speeding up the delay?

> Thanks in advance,
> Gabriel Padilla

What exactly is the slow part of the application ? Is it when calling Open
on the query, or is it after the dataset is open and you are then looping
over all records in the dataset ? If it's the latter, then calling
DisableControls on the dataset before iterating over the records makes a
SIGNIFICANT performance difference, even when you have no controls connected
to the dataset. My tests indicate this improves performance by between15 and
30 times, depending on the number of records.

Also, when building your connection string make sure you use the OLE DB
Provider for SQL Server, not the one for ODBC, as that improves performance
as well.

One last thought. I assume that when opening the ADODataSet/ADOQuery, it is
connected to an ADOConnection object that already has an active connection
to SQL Server ? The delay isn't due to establishing a new connection to the
server is it ?

Regards,
Andy Mackie.

Re:ADO vs. BDE in Accessing SQL Server 7


Quote
>Andy,

>Thanks for the quick reply.
>I am accessing a table of users (I call them entity) of about 2000 records
>with the EntityNo as the primary key.  The test code does not use any DB
>Controls.

>The delay in the ADOQuery appears to be in the open procedure.  It does not
>matter much if the ADOQuery object is created outside or inside the timing
>loop.

>Please see code snippet below.  Do you have any other thoughts?  Are you
>experiencing the same speed degradation.

>-- Start code snippet

>// Time TQuery.
>procedure TForm1.Button1Click(Sender: TObject);
>var
>  Query: TQuery;
>  i: integer;
>  Start: TDateTime;
>begin
>  Start := now;
>  for i := 1 to count do
>    begin
>    Query := TQuery.create(nil);
>    Query.Databasename := Database1.DatabaseName;
>    Query.sql.text := 'Select * from Entity where EntityNo='
>      + IntToStr(Random(10000));
>    Query.open;
>    Query.free;
>    end;

>  showmessage( IntToStr(trunc((now-start)*3600*24*1000)) + ' ms');
>  // For 10 records this takes 20 ms.
>end;

>// Time TADOQuery.  I am using OLEDB and not ODBC.
>procedure TForm1.Button2Click(Sender: TObject);
>var
>  ADOQuery: TADOQuery;
>  i: integer;
>  Start: TDateTime;
>begin
>  Start := now;
>  for i := 1 to count do
>    begin
>    ADOQuery := TADOQuery.create(nil);
>    ADOQuery.Connection := ADOConnection1;
>    ADOQuery.sql.text := 'Select * from Entity where EntityNo='
>       + IntToStr(Random(10000));
>    ADOQuery.open;
>    ADOQuery.free;
>    end;
>  showmessage( IntToStr(trunc((now-start)*3600*24*1000)) + ' ms');
>  // For 10 records this takes 400 ms.
>end;

Gabriel,

Your reply via e-mail is repeated above. I'd suggest making use of
parameters, set Prepared = True, specify the cursor type, and call Requery
than repeatedly calling Open. The routine below speeded things up by 2 to 3
times. Try that on your machine and let us know how you get on. It was
taking about 40ms on my machine (server is local as well). Can anyone else
make Gabriel's routine as fast as the BDE example ?

Also, is repeatedly executing the same query really representative of the
types of queries your application will be executing ?

procedure TForm1.Button2Click(Sender: TObject);
const
  count = 10;
var
  ADODataSet: TADODataSet;
  i: integer;
  Start: TDateTime;
  EntityParameter: TParameter;
begin
  Start := now;
  ADODataSet := TADODataSet.create(nil);
  ADODataSet.Connection := ADOConnection1;
  ADODataSet.CursorType := ctStatic;
  ADODataSet.Prepared := true;
  ADODataSet.commandtext := 'Select * from Entity where EntityNo=
:EntityNo';
  EntityParameter := ADODataSet.Parameters.ParamByName('EntityNo');
  for i := 1 to count do begin
    EntityParameter.Value := Random(10000);
    if i = 1
    then ADODataSet.Open
    else ADODataSet.Requery;
  end;
  showmessage( IntToStr(trunc((now-start)*3600*24*1000)) + ' ms');
end;

Regards,
Andy Mackie.

Re:ADO vs. BDE in Accessing SQL Server 7


Have you considered whether or not you have appropriate indexed?  Does the
SQL7 table have a clustered index or any other indexes?  What type of query
are you using?

Ken

Quote
"Gabriel Padilla" <gpadi...@starstream.net> wrote in message

news:3c0f0c42$1_1@dnews...
Quote
> I have done some performance testing with both Delphi 5 Pro and Delphi 6
Ent
> with
> the latest update packs.  I have tried this on both Win2K and WinXP.

> ADO is 15-20 times SLOWER for doing a simple query against a table of user
> names.

> Has anyone else noticed this horrible speed delay?
> Are there any ideas in speeding up the delay?

> Thanks in advance,
> Gabriel Padilla

Other Threads