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

[Ryan W]

I don't think the new data is any larger, per se.   I was hoping the
recordset object could tell me the actual size of the data in Kb/KB or
Mb/MB but I haven't found anything that can tell me that.



Wireshark does not show any Bad TCPIP lines.... so like you said maybe it's
just more data that isn't obvious to me right at the moment.





On Wed, Apr 28, 2021 at 10: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
>