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

Wed Apr 28 16:34:07 CDT 2021

oh, and that client execution time is simply when I executed it. Not elapsed time. Sorry for the confusion.  Look further down in the stats for actual time for query to run

Sent from my iPhone

> On Apr 28, 2021, at 4:32 PM, Ryan W <wrwehler at gmail.com> wrote:
> 
> 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