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

[Ryan W]

Sorry I had to step away;

I re-ran each of the 2 batch queries for 2018,2019,2020 from SSMS with
client statistics on and SET NOCOUNT ON;

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


You can see all the sizes are not far off from eachother, but the
processing time and execution time on TRIAL 3 (2020) was massive compared
to 2019 and 2018 but the WAIT TIME on replies was down.....


You can see the number of Round Trips for each query was 2.



Still seems very very odd.




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

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