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

[Jim Dettman]

 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