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

[Ryan W]

SSMS Client Statistics :


https://i.imgur.com/2Gdev6l.png

So all that's changing in this query is the years... 2018, 2019, 2020....

You can see that round trip differs between all 3 years/queries, and bytes
received from server are very close between 2018 and 2020 (the problem
year)... while 2019 was a little larger in byte size..

But if you look at my original statistics in the first post from my VBA
Immediate window, 2020 takes about 6 times longer (a minute, versus 10
seconds).











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

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