Board index » delphi » BDE to ADO - ADO really slow

BDE to ADO - ADO really slow

I was looking at migrating our BDE based software over to ADO but I can't
seem to get any kind of performance out of ADO. We are currently connecting
to MS-SQL 7/2000 servers using TQueries. I converted the processing section
of one program over to TADOqueries and watched the processing time go from
35 secs to over 3 minutes. I tried various combinations of settings, but to
no avail. Can somebody tell me what I am missing?

TIA

 

Re:BDE to ADO - ADO really slow


Check out this article and see if it helps you out.

http://community.borland.com/article/0,1410,27790,00.html

Bob

"Kenneth L. Rinaman" <g...@away.net> wrote in message
news:3d76484d$1@newsgroups.borland.com...

Quote
> I was looking at migrating our BDE based software over to ADO but I can't
> seem to get any kind of performance out of ADO. We are currently
connecting
> to MS-SQL 7/2000 servers using TQueries. I converted the processing
section
> of one program over to TADOqueries and watched the processing time go from
> 35 secs to over 3 minutes. I tried various combinations of settings, but
to
> no avail. Can somebody tell me what I am missing?

> TIA

Re:BDE to ADO - ADO really slow


"Kenneth L. Rinaman" <g...@away.net> wrote in message
news:3d76484d$1@newsgroups.borland.com...

Quote
> I was looking at migrating our BDE based software over to ADO but I can't
> seem to get any kind of performance out of ADO. We are currently
connecting
> to MS-SQL 7/2000 servers using TQueries. I converted the processing
section
> of one program over to TADOqueries and watched the processing time go from
> 35 secs to over 3 minutes. I tried various combinations of settings, but
to
> no avail. Can somebody tell me what I am missing?

Is you processing code looping over many records in the ADOQueries ? If so,
call DisableControls on the datasets before the loop and you will get a big
speed improvement.

Andy Mackie.

Re:BDE to ADO - ADO really slow


I tried the suggestions people made (thanks) but I still can't get the same
performance. Here is what my program is doing: I open one query (still BDE
at the moment) pulling back around 2000 records. I then loop through those
2000 records performing several other SQL statements with an ADOquery (3
selects, 1 delete, and one insert). The three select statements return one
row of data. Heres an example of my code (q_Query1 is ADO and q_Loc is BDE):

      //Ensure Account No is active
      q_Query1.Sql.Clear;
      q_Query1.Sql.Add('Select Active From AccountNo');
      q_Query1.Sql.Add('Where Account_No = :Account_No');
      q_Query1.Sql.Add('And Entity = :Entity');
      q_Query1.Parameters.ParamByName('Account_No').Value :=
q_Loc.FieldByName('Account_No').AsString;
      q_Query1.Parameters.ParamByName('Entity').Value :=
q_Loc.FieldByName('Entity').AsString;
      q_Query1.Active := True;
      If q_Query1.FieldByName('Active').AsBoolean = False Then
        Begin
          q_Loc.Next;
          Continue;
        End;

Running a line profiler I came up with the following results for the
q_Query1.Active := true line:

BDE - avg speed 4.35 ms
ADO - avg speed 8.15 ms (this was best results using clUseServer,
ctOpenForwardOnly, ltReadOnly) other settings were as high as 12.79 ms

Any Ideas?

Re:BDE to ADO - ADO really slow


Quote
>      q_Query1.Sql.Clear;
>      q_Query1.Sql.Add('Select Active From AccountNo');
>      q_Query1.Sql.Add('Where Account_No = :Account_No');
>      q_Query1.Sql.Add('And Entity = :Entity');

Each SQL.ADD you do has a performance hit.
         Build the querystring separately and use a single SQL.add
--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:BDE to ADO - ADO really slow


That's one of the things that frustrates me with the TCustomADODataSet. The
fact that the parameter list is rebuilt whenever the query is changed is
fine, but going to the server to retrieve the actual parameter type
information every time is just crazy.

It really makes working with run-time generated queries with ":" style
parameters a pain. Even if you set paramcheck=false, the list of parameters
is cleared whenever you change the CommandText, which is just wrong.

I think it's a serious design flaw.

Pierre le Riche

"Brian Bushay TeamB" <BBus...@Nmpls.com> wrote in message
news:969gnugb7q1d01p3apelok6g1mb64ten59@4ax.com...

Quote

> >      q_Query1.Sql.Clear;
> >      q_Query1.Sql.Add('Select Active From AccountNo');
> >      q_Query1.Sql.Add('Where Account_No = :Account_No');
> >      q_Query1.Sql.Add('And Entity = :Entity');

> Each SQL.ADD you do has a performance hit.
>          Build the querystring separately and use a single SQL.add
> --
> Brian Bushay (TeamB)
> Bbus...@NMPLS.com

Re:BDE to ADO - ADO really slow


Thanks,

I gave that a try but I'm still not able to match the BDE performance.
Currently I am seeing 0:35 for the BDE and 1:43 for ADO. Based on the line
profiling I'm seeing the query.active := true statements being the culprit.
ADO seems to take twice as long (8 ms) as the BDE (4 ms).

Any other suggestions?

"Brian Bushay TeamB" <BBus...@Nmpls.com> wrote in message
news:969gnugb7q1d01p3apelok6g1mb64ten59@4ax.com...

Quote
> Each SQL.ADD you do has a performance hit.
>          Build the querystring separately and use a single SQL.add
> --
> Brian Bushay (TeamB)
> Bbus...@NMPLS.com

Re:BDE to ADO - ADO really slow


Quote
> I gave that a try but I'm still not able to match the BDE performance.
> Currently I am seeing 0:35 for the BDE and 1:43 for ADO. Based on the line
> profiling I'm seeing the query.active := true statements being the
culprit.
> ADO seems to take twice as long (8 ms) as the BDE (4 ms).

> Any other suggestions?

You can try to set the AdoQuery's Prepared property to True. I haven't used
it, but it might/should boost performance when doing repetitive Sql.

-tor
--
gitek as

Re:BDE to ADO - ADO really slow


Quote
>I gave that a try but I'm still not able to match the BDE performance.
>Currently I am seeing 0:35 for the BDE and 1:43 for ADO. Based on the line
>profiling I'm seeing the query.active := true statements being the culprit.
>ADO seems to take twice as long (8 ms) as the BDE (4 ms).

You code is recreating the query is that necessary.  ADO is faster with an
existing query when you change the parameter values and use Requery.

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

Re:BDE to ADO - ADO really slow


No it's not necessary, rather than having six query objects I just used the
same one over again for convenience. I didn't realize the speed hit I was
taking. I changed the code for that one query and noticed a huge difference
down to 2ms (twice as fast as the BDE). Thanks!
Quote
> You code is recreating the query is that necessary.  ADO is faster with an
> existing query when you change the parameter values and use Requery.

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

Re:BDE to ADO - ADO really slow


Quote
>As I built the query already (per your advice). Using this I can't get it to
>go faster than 42.85 ms. I've tried various locktypes, cursorlocations, etc.
>Do you have any suggestions?

None of the various settings.  Try using a TadoCommand component its properties
are the only ones needed for a command query.
What are you timing here the first Delete query or subsequent Deletes?
What database are you using?
--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:BDE to ADO - ADO really slow


I had already tried the TAdoCommand with the same results.
My timing is the average of all the runs. Running it again with current
settings I got a Max of 1081ms, Min 39.38, and Avg of 48.01.

I am using MS SQL server 2000. I also captured the SQL statements being sent
via the BDE and ADO. Although the statements where different they both ran
the same in Query Analyzer (3 secs for 1000 iterations). One thing I did
notice running the SQL Profiler is that for every loop in my program there
is an Audit Logout, Audit Login, and the Delete query with using TAdoQuery.

"Brian Bushay TeamB" <BBus...@Nmpls.com> wrote in message
news:9n6snukar11c8fnquvj9ubb1t8b6igeql7@4ax.com...

Quote
> >As I built the query already (per your advice). Using this I can't get it
to
> >go faster than 42.85 ms. I've tried various locktypes, cursorlocations,
etc.
> >Do you have any suggestions?

> None of the various settings.  Try using a TadoCommand component its
properties
> are the only ones needed for a command query.
> What are you timing here the first Delete query or subsequent Deletes?
> What database are you using?
> --
> Brian Bushay (TeamB)
> Bbus...@NMPLS.com

Re:BDE to ADO - ADO really slow


Quote
>I am using MS SQL server 2000. I also captured the SQL statements being sent
>via the BDE and ADO. Although the statements where different they both ran
>the same in Query Analyzer (3 secs for 1000 iterations). One thing I did
>notice running the SQL Profiler is that for every loop in my program there
>is an Audit Logout, Audit Login, and the Delete query with using TAdoQuery.

Audit logging is a configurable property on the server that writes login
information to the error log and NT event log.  If you have this turned on for
your server you have a general performance problem.  My guess is it is only
happening because you have the query analyzer set to record it.
BDE uses a much older API for connecting to SQL server and my guess is that API
does not support Audit login so it is not generated.

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

Re:BDE to ADO - ADO really slow


Quote
>You can solve this problem by either going for design-time queries, or using
>"?" style parameters and setting ParamCheck = false. Alternately, you may
>want to try and combine the queries into fewer but more complex queries to
>cut down on the parameter fetching overhead.

>Pierre

Do you have to create the parameters manually to use '?' parameter
markers? How do you use '?' in TADOQuery? Do you have sample code for
a dynamically created query?

Thanks,

Matt Jacobs

--

My reply-to address is purposely mangled to thwart auto-reply bots.
Please remove the two leading underscores if you wish to reply via
e-mail.

Go to page: [1] [2]

Other Threads