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

Bill Benson bensonforums at gmail.com
Wed Apr 28 10:55:43 CDT 2021


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
>


More information about the AccessD mailing list