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

[Ryan W]

Meant to send this with it:

https://i.imgur.com/TkfJUE2.png




On Wed, Apr 28, 2021 at 10:08 AM Ryan W <wrwehler at gmail.com> wrote:

> 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.
>
>
>
>
>
>