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

Wed Apr 28 16:32:29 CDT 2021

I thought it was possibly related to access or the data but obviously the client execution time for SSMS also sees this same behavior. 

Just finding it odd the data is effectively the same stuff but takes so much longer to grab. 

Sent from my iPhone

> On Apr 28, 2021, at 4:29 PM, Paul Wolstenholme <Paul.W at industrialcontrol.co.nz> wrote:
> 
> Ryan,
> 
> I'm not understanding your attachment.
> What do you think 'client execution time' is?  Is SQL Server blaming Access
> for being slow?
> 
> Paul Wolstenholme
> 
> 
>> On Thu, 29 Apr 2021 at 06:18, Ryan W <wrwehler at gmail.com> wrote:
>> 
>> 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
>>>> 
>>> 
>> --
>> 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