Excessive Report Run Time

[rjgriffin46]

As a follow on to my prior post, I was able to develop a report that compares data from our Kids Summer Feeding program between 2 years. However the report takes over 4 minutes to run.

The primary table containing the data is "Site Weekly Counts". There is 1 record for each week of the program for each Site containing 11 fields relating to quantities of meals delivered and served. A typical running of the report is done off the Reports Menu which can be opened from either the Main Menu or the Site Weekly Counts form. On the Reports Menu, the first program to compare is the Selected Program near the top of the menu. The report weeks to include for that program are selected next. Below, next to the Year to Year Comparison button that runs the report, Program 2 and its weeks are selected. Right now there is data from last year (2017) and a bit of test data for 2018.

The method I ended up using was to first determine if a site had data for each program and then add 2 "IIf"expressions in a query for each of the 11 quantity fields representing each of the 2 programs to be compared. The IIf expressions add the source value if the year matches. This is done through the following queries:

1. Yrly Compare Query Prog1 Sites and Yrly Compare Query Prog2 Site are summary queries that provide a target for the next group of queries DLookup. They are a list of the site names that have data in each program along with the program year.
2. The following queries are input one to the next in the order listed and result in a record source for the report:
   
   1. Yrly Compare Query All Data Sub1 is the initial query that gathers all the data from the Sites, Site Weekly Counts and Citys County tables. It has criteria to select only the weeks indicated in the Reports Menu for each program. It also has the DLookup expressions to both the Yrly Compare Query Progn Site queries filling in the year if there is a match.
   2. Yrly Compare Query All Data Detail adds criteria to select sites based on which of the 5 Sites to Include options is selected. This has to do with whether a site participated in both programs or just one using the DLookup results from the prior query.
   3. Yrly Compare Query All Data adds criteria to limit to the County selected if the first Counties Option is selected in the Reports Menu. And it has the 2 sets of IIf expressions to add the value from the source field if the record's year matches the Program year. Here's an example: "Prog1Deliv: IIf([Year]=[Prog1Yr],[Delivered],0)".
   4. Yrly Compare Query All Data Input to Make Tble Qry creates a temporary table (Yrly Compare Details Rpt Input) that is the record source for the report and the sub report that has the totals.

The report then takes the two sets expression fields created in query 3 and summarizes them by Meal Type and Site. The Program 1 fields are in a row under the Program 2 fields row. Text Box fields in the report then subtract the Program2 fields from the Program 1 fields for the Change row.

I timed the report run locally on my PC at around 4.5 minutes. The queries on their own run quickly as they normally do. So it's the report that takes the excess time. I tried and compared doing the summaries in the query vs. the report but then the queries took a long time.

The database is attached but you will need to re-link the tables.

I'd appreciate any thoughts as to how this report might be done to improve the run time.

Thanks.

[isladogs]

I've had a quick look at your report and whilst it was very slow to load, it took less than a minute on my 6 year old PC for option 1 & 8 seconds for option 5.

I never use embedded macros so can't assist with changes to your code.
This is also true for many of the most experienced developers here
I would recommend changing to using VBA partly because it will significantly increase the number of people able to assist

However some observations:
1. The lengthy delay is almost certainly due to the huge number of calculations required on both report & subreport
Using VBA you could create a procedure to do all the calculations when the form button is clicked then use that to populate your report controls
That should be faster

2. Are all the calculations done individually or can any be done based on other calculations. If the latter , again it would be faster as part of the work is already done

3. Your report & subreport both use the same record source. Why not just have a single report?

4. Your embedded macro states you are making a temporary table first. This is poor design & should be avoided where possible.
Repeatedly making new tables will cause database bloat & instability

If you need to store your calculations in a table, your table should only be 'temporary' in the sense that its data is temporary. The table should be permanent and populated as required

Good luck with your project

[rjgriffin46]

Thanks so much for taking the time to look at the database. A few followup questions:

1. I should have suggested running the report from the Reports Menu for weeks 1-5 and selecting Summer Feeding 2017 as program 2, same weeks 1-5. When I run that either on my laptop (a 2010 Dell i5) or desktop (an old but Xeon processor) I get the same 4+ minute run time for options 1-4 and about a minute for option 5.
   
   Did you run the report this way from the menu or otherwise?
2. A few observations:
   
   1. When I run each of the queries separately they display their datasheet right away, even the last make table query. Yet when I run the make table query to actually create the temporary table then it takes the 4-5 minutes.
   2. If I just run the report directly from the Objects panel, not from the menu, with the temporary table already created, it comes up right away.
   3. I also made a copy of the make table query as a standard select query and that displays the datasheet right away. But when I run the report using that query as the record source it takes the 4-5 minutes.
   
   
3. The only Macro employed is that to run the make table query and then open the report. I've not yet learned to code in VBA.
4. There aren't a lot of mathematical formula calculations. The bulk of the query are the IIf expressions, 24 in total, like "Prog1CPaid: IIf([Year]=[Prog1Yr],[C-Paid For],0)" to put the source value in the appropriate "bucket" for either program 1 or 2.
5. I made the sub report separate because I thought it was easier for me to do than add those totals to the main report. And this was another reason to use a make table query to create a temporary table since I believed it could be used by the sub report without having to re-run any queries.
6. In your 4th point are you saying that by deleting and re-creating the temporary table each time the report is run bloats the database? And if so, would it be better to run a delete query that deletes all the records in the temporary table and change the make table query to an append query? Would this be a faster run time?

Again, thanks for your time and thoughts!

[davegri]

I think your report is too "far away" from the tables.
The report's recordsource is a table created by a query
which has its recordsource a query
which has its recordsource a query
which has its recordsource a query
whose recorsource is finally the tables.

Maybe this is pertinent:
When the report is run, access has to juggle these queries attempting to guarantee that the latest information is presented up the line to the next query.
Perhaps you could devise a recordsource query for the report that is "closer" to the tables, maybe going directly to the tables.
In addition, if this query were a totals query, much of the summing could take place in the query instead of the report.

Secondly, I see no need for the temporary table. The report should work fine with the query as the recordsource.

[isladogs]

I think davegri looked at your database more closely than I did but his comments fit with my general feelings.
The issue is the way you are deriving all your calculated values

If davegri is correct that no temp table is needed that will definitely speed things up.
But IF it is needed, using append and delete queries is definitely preferable to a make table query

[rjgriffin46]

Thanks to both for your thoughts. Just a few more questions and comments:

1. Consolidating Queries
   
   1. I will pursue this. I think I ended up with the 1st 3 just because each represented a step in figuring how to do something and I just wanted to preserve that before working on the next step. It looks like they could be consolidated and there are some unneeded fields I can remove.Summing in the Query vs the Report.
   2. As I'd indicated before, my recollection is that it didn't make any difference if I summed in the query or the report. And as far as I can see the query could sum at the Meal level for each program for each site but the report would still have to do the site summing for each program. And the change calculations would still need to be done in the report. I will look at this again to see if it helps.
   3. Right now, I made a copy of the Make Table query and changed to a select query. It opens right away. I changed the report record source to this query and it takes all the time. One would conclude from this that perhaps doing as much of the summing as possible in the query would improve the performance....
   
   
2. Eliminating the Make Table or Append Table Query
   The temp table is not necessary. As I stated I decided on that so that both the report and sub-report could use the same record source and so avoid re-running the query. If I use the same query as a record source for both the report an sub-report will the query have to run separately for each and if so wouldn't that be more time consuming, especially if I add summing to the query?

Thanks again for your interest and comments. Depending on further responses I can mark this as closed.

[isladogs]

Why not make the question redundant by merging the report and subreport?

[rjgriffin46]

I have removed the sub-report and it takes the same amount of time to run.

What I find ironic and perhaps someone can explain is that I have a copy of the Make Table query that I changed back to a Select query. When I just open the Select query it comes right up. But then when I open the report using this as the report record source, the report takes the 5 minutes to render (display). This makes me think that it's the report that takes so much time, not the query.

Alternatively, it takes 5 minutes to run the Make Table query that creates the temp file. Then if I change the report record source back to the temp file the report comes up instantly. This makes me think its the making of the temp table that takes the time, not the report.

What I don't understand is why the report takes so long to run using the Select query as a record source if the query comes right up when opened alone.

Any explanation?

Thanks

[davegri]

I think it's ultimately in the report. Dozens of calculations repeated 26 times. I don't see any way around that.
I made the report recordsource "Yrly Compare Query All Data", which is not dependent on previous queries.
Then just open the report and it takes forever. A couple of parameter requests pop up - just hit enter.

[rjgriffin46]

Yes, I had the same experience as I'd indicated. Have you any idea how the report does the calculations so fast when using the temp table (after its been created by separately running the Make Table query) as the record source?

[davegri]

We get the same conflicting test results. I still suspect the report as the roadblock. CPU bound from the myriad calculations.

[rjgriffin46]

So then an alternate approach utilizing VBA is the best chance for a faster running report...? If so, can you point me to any sample code that might be adaptable for this purpose?

Thanks again for your interest and responses.