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

Wed Apr 28 17:23:36 CDT 2021

Possibly a "cover index" in SQL Server not covering a field used in that
year data?  It feels like an index problem.

On Wed, Apr 28, 2021 at 5:34 PM Ryan W <wrwehler at gmail.com> wrote:

> 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
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>

-- 
John W. Colby
Colby Consulting