[AccessD] ADO Recordset Procedure markedly slower with 'newer' data?

Ryan W wrwehler at gmail.com
Wed Apr 28 10:58:53 CDT 2021


Bill,
  It's POSSIBLE... but we're almost 5 months into 2021... usually once we
process and report the data to the clients there's not a lot of "going
back" unless the clients are questioning the data we gave them as possibly
inaccurate or needing more info.



On Wed, Apr 28, 2021 at 10:56 AM Bill Benson <bensonforums at gmail.com> wrote:

> How about the possibility there are several other clients accessing the
> newer data at the same time, the older data not so much!
>
> On Wed, Apr 28, 2021 at 11:18 AM Jim Dettman via AccessD <
> accessd at databaseadvisors.com> wrote:
>
> >
> >  I would look to the network.
> >
> >  The newer data may be larger, causing packets to fragment.
> >
> >  Run Wireshark with the "Bad TCP/IP" filter on and see what it shows.
> >
> > Jim.
> >
> > -----Original Message-----
> > From: AccessD On Behalf Of Ryan W
> > Sent: Wednesday, April 28, 2021 11:08 AM
> > To: Access Developers discussion and problem solving
> > <accessd at databaseadvisors.com>
> > Subject: [AccessD] ADO Recordset Procedure markedly slower with 'newer'
> > data?
> >
> > IDK if that subject makes sense.
> >
> > But I'm fetching a bunch of data from SQL Server 2008 into an ADO
> Recordset
> > and then processing that data on the Access Client.
> >
> > When using a client side cursor, the data is all fetched locally for
> > consumption.... however if I move through my data (in years) the newer
> data
> > takes a lot longer to fetch and consume.
> >
> > I've been watching TaskMan for the network, and for the data from 2018,
> > 2019 I can see spikes of upward of 111 Mbps (13MB/s)... where the 2020
> data
> > never sees spikes quite like that, I see a much flatter network usage.
> >
> > Here are the results for 3 years, each time I ran the code I ran DBCC
> > FREEPROCCACHE to clear caches just to make sure nothing was cached or
> some
> > sort of parameter inefficiencies.
> >
> > Start: 4/28/2021 9:50:36 AM
> > 2018: 7409 results
> > 2018: 147261 sub results
> > Stop: 4/28/2021 9:50:44 AM
> >
> > Start: 4/28/2021 9:50:58 AM
> > 2019: 8395 results
> > 2019: 171517 sub results
> > Stop: 4/28/2021 9:51:07 AM
> >
> > Start: 4/28/2021 9:51:18 AM
> > 2020: 7242 results
> > 2020: 152184 sub results
> > Stop: 4/28/2021 9:52:44 AM
> >
> > As you can see 2018 and 2019 ran in seconds... where 2020 took over a
> > minute.  They have similar enough data/rows being returned so it's not
> like
> > the data in 2020 was "so much more" that consumption takes longer.
> >
> > The main thing that really sticks in my head is WHY is the network
> traffic
> > so less active during the 2020 data than the 2018/2019 data.  I'm sure
> this
> > is the overall "WHY" it takes so much longer to fetch and consume but it
> > makes ZERO sense to me.
> >
> >
> > Here's a more narrow set of data ... since the data above is an extreme
> use
> > case(an entire year, for every users data):
> >
> > Start: 4/28/2021 10:04:44 AM
> > 2018: 869 results
> > 2018: 5370 sub results
> > Stop: 4/28/2021 10:04:45 AM
> >
> > Start: 4/28/2021 10:04:48 AM
> > 2019: 1195 results
> > 2019: 4550 sub results
> > Stop: 4/28/2021 10:04:48 AM
> >
> > Start: 4/28/2021 10:04:51 AM
> > 2020: 1207 results
> > 2020: 5835 sub results
> > Stop: 4/28/2021 10:05:09 AM
> >
> >
> > You can still see that the 2020 data takes longer, with somewhat similar
> > returned results/rows.
> >
> >
> > I have a few jobs on the server to re-index/re-org stuff
> > nightly/weekly..... so I'm left scratching my head.
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > https://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > https://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


More information about the AccessD mailing list