ADO is sooo... slow - Any ideas?
Alan Smith
In part of the app we have to first, EOF ... next loop through
a table with 35K rows. Paradox took 2 seconds, ODBC takes
3 and ADO 24! We have tried pretty much all of the options on
the ADO query and its not the query Open that takes the time
its the loop... even when there is only Next inside the loop.
Anyone any ideas?
Many thanks for any help,
Alan Smith
Jason Swager
Different types of cursor locations and types can change performance.
CacheSize helps, depending on your exact scenario.
Jason Swager
Alan Smith <Al...@aurora-uk.com> wrote in message news:394502c1@dnews...
Alan Smith
unless we've missed something really obvious. We just can
reconcile why it is so comparitively slow. During the 24 seconds
these is little server and network traffic it appears to be purely
the act of doing EOF next on a dataset that has been retrieved
in actually only 2 seconds.
Alan Smith.
Jason Swager <jsw...@alohaoi.com> wrote in message news:3945033c@dnews...
Troy Wolbrink
Skip ADO and go directly to OLEDB. In a book I'm reading,
"Professional SQL Server 7.0 Programming", Robert Vieira says (and I'm
quoting)
- "It [OLEDB] is very fast indeed when not used with ADO."
- "It is far more of a pain to program in than in ADO, but it is much
faster..."
- "You're going to need to figure out whether or not the speed is
worth the hassle"
I'm not aware of any TDataSet components written to work directly with
OLEDB, but if there were, it would be a pretty good solution!
Solution 2: SQL
I'm sure you've already tried forward only, read only, etc. so I won't
suggest that. But is there a way you could employ SQL to do whatever you're
trying to do? SQL works quickly for SQL's functions like COUNT or SUM. Or
do you need to read and store detailed info about each record?
Paul Bartlett
I wouldn't say this is ADO, but a difference between C/S and file based
databases. Although 24 seconds is a bit much!!!!
The 3 seconds, is this ODBC are reading 35K records from SQL/Server?
Have you tried server cursors?
Paul
Alan Smith <Al...@aurora-uk.com> wrote in message news:394502c1@dnews...
> We are converting an application from Paradox BDE to SQL 7.
> In part of the app we have to first, EOF ... next loop through
> a table with 35K rows. Paradox took 2 seconds, ODBC takes
Brian Bushay TeamB
>We've just about exhausted every combination of these settings,
>unless we've missed something really obvious.
Well using a TadoDataset with no visual controls attached and a Serverside
cursor is the obvious choice. is that one you have tested.
--
Brian Bushay (TeamB)
Bbu...@NMPLS.com
Harry Van Tassell
"Alan Smith" <Al...@aurora-uk.com> wrote in message news:394504e6@dnews...
> We've just about exhausted every combination of these settings,
> reconcile why it is so comparitively slow. During the 24 seconds
> these is little server and network traffic it appears to be purely
> the act of doing EOF next on a dataset that has been retrieved
> in actually only 2 seconds.
>
There could be a couple of things you may not have tried that have a
pronounced effect on loop timing. One is to simply _always_
Dataset.DisableControls even if you don't have any controls. The other is
to use the RecordSet directly in the loop which can be a bit more coding on
your part.
Here is an example using a 7282 record table in a SQL-7.
With UseClient, Static cursor, CacheSize 100 and a loop that just reads
value of every field of every record into a variable but does nothing with
the value. Time in ms.
Open ReadLoop
------ ----------
1137 13,474 Dataset - ControlsEnabled
1125 4,161 Dataset - ControlsDisabled
1126 1,099 RecordSet
best time 1126+1099= 2225ms.
With UseServer, ForwardOnly cursor, CacheSize 100
Open ReadLoop
------ ----------
84 3,507 Dataset - ControlsEnabled
80 3,450 Dataset - ControlsDisabled
85 2,262 RecordSet
best time 85+2262=2347ms
Using the RecordSet directly is by far the best but if you use the DataSet
always DisableControls.
--Hairy
Alan Smith
I'm keen to get things working with ADO if possible and are not following
other paths at the moment other than for comparisons.
We've tried, server/client side, forward only caching, block read size etc
and
just about every combination!
As far as getting the server to do the work I agree and we do that when ever
we can.
Unfortunately this is just one case where its not possible without really
earth shattering go back and re-design the application principles.
Many thanks anyway,
Alan Smith
Troy Wolbrink <wolb...@ccci.org> wrote in message news:39450ca3@dnews...
> Solution 1: OLEDB
>
> Skip ADO and go directly to OLEDB. In a book I'm reading,
> "Professional SQL Server 7.0 Programming", Robert Vieira says (and I'm
> quoting)
> - "It [OLEDB] is very fast indeed when not used with ADO."
> - "It is far more of a pain to program in than in ADO, but it is
much
> faster..."
> - "You're going to need to figure out whether or not the speed is
> worth the hassle"
>
> I'm not aware of any TDataSet components written to work directly
with
> OLEDB, but if there were, it would be a pretty good solution!
>
> Solution 2: SQL
>
> I'm sure you've already tried forward only, read only, etc. so I
won't
> suggest that. But is there a way you could employ SQL to do whatever
you're
> trying to do? SQL works quickly for SQL's functions like COUNT or SUM.
Or
> do you need to read and store detailed info about each record?
>
>
Alan Smith
Yes we tried that too. The application is not feeding any data aware
controls.
Its simply an ADOQuery and ADOConnection and an EOF.. NEXT loop
reading a Char(20) and an integer field.
Regards,
Alan Smith
Brian Bushay TeamB <BBu...@Nmpls.com> wrote in message
news:39462ea8.15352744@floyd...
>
> >We've just about exhausted every combination of these settings,
> >unless we've missed something really obvious.
>
Jason Swager
performance was an issue? I thought they said you should use the ADODataSet
instead. Even thought the ADOQuery and ADOTable may not be linked to any DB
controls, they still have to perform the extra work necessary to handle a DB
control if one was ever attached. If I remember correctly, a TDataSet never
knows if it has DB controls attached to it or not.
Jason Swager
Alan Smith <Al...@aurora-uk.demon.co.uk> wrote in message
news:39454fa4@dnews...
Alan Smith
and appologies to anyone else who knew this. Basically
I has no idea that calling DisableControls on my ADOQuery
component would make loop processing 20 times faster!
Other options ..cursor type, Server v's client, locking etc
also help as well - but the key was definitely DisableControls.
Many thanks again to all who offered advice.
Regards,
Alan Smith
Alan Smith <Al...@aurora-uk.com> wrote in message news:394502c1@dnews...
> We are converting an application from Paradox BDE to SQL 7.
> In part of the app we have to first, EOF ... next loop through
> a table with 35K rows. Paradox took 2 seconds, ODBC takes
Troy Wolbrink
ODBC: 3 sec
ADO: ??? sec.
Alan Smith
still
slightly slower than ODBC.
Alan Smith
Troy Wolbrink <wolb...@ccci.org> wrote in message news:39463ea8@dnews...
Harry Van Tassell
> With the Modifications suggested we got a time of about 4 seconds which is
> still
> slightly slower than ODBC.
>
Hello Alan,
Did you get that 4 seconds when using the DataSet or when using the
RecordSet to iterate the loop? In almost every instance using the RecordSet
will be significantly faster since that will avoid the overhead of the
DataSet.
--Hairy
Andreas Hesse
news:8i802t$qd...@bornews.borland.com...
Hello Hairy,
how can I use the _RecordSet to get the field Values?
<g>
Andreas
Harry Van Tassell
"Andreas Hesse" <andrea...@softxel.com> wrote in message
news:39605059@dnews...
>
> how can I use the _RecordSet to get the field Values?
>
> <g>
> Andreas
>
Hello Andreas,
I'm fascinated by the placement and possible meaning of the <g> in your
message. Does it mean the you find it so difficult to find the answer that
<g>="grimace" or easier to ask me <g>="grin"?<rbg>
There as several ways to access field objects and, if my memory serves me
well, one of which is:
RecordSet.Fields.Item['fieldname'].Value
--Hairy