Forum Discussion
The return of the performance leak - this time with subreports
Heinzi / Tom
Can I check the details of this bug report as I'm unclear why anyone would ever do anything like this in a real life situation
If I create the report / subreport exactly as described I also get a significant performance degradation with both .pdf and .txt output files. (tested in latest Beta v2602 build 19713.20000)
However I'm unclear why you would set both the report and subreport for the Numbers table (albeit with slightly different record source) without linking the main & subreport by the 'nr' field
When I add 'nr' as the linked master/child fields, there is no degradation.
Am I missing the point here?
That's just a simplification to get "a lot of subreport rows", so that the problem can be reproduced faster. It also happens when the subreport is linked and (thus) has less rows, it just takes longer.
The real-life situation was that one of our customers tried to create hundreds of "start of the year" PDF invoices (based on a fairly complex Access report that displays usage data in a subreport). The process had to be interrupted because, after some time, the application visibly slowed to a crawl.
So, when trying to build a minimal repro example, I looked for ways to make this problem "happen faster", because both (a) creating the minimal example and (b) testing with different versions of Access get very boring very fast, if every test run takes a lot of time.
Apparently, the severity of the problem increases with the number of rows actually rendered on the subreport. Since exporting 40 reports with 1000 subrows is a lot faster then exporting 1000 reports with 40 subrows, I opted for the former.
Understood but as I say when I did link the report /subreport fields there was no degradation at all even after multiple tests.
Anyway, I alerted the Access team to your 2508+ ACCDE issue in your other thread. In my tests, it doesn't need to be loaded as a library database in 2507 to get the error.
Just trying to run the code directly in the ACCDE causes the compile error.
I see. I can reproduce your findings. However, this appears to be due to the low number of rows shown in the subreport (if you link nr to nr, you have only one row in the subreport), not due to the link itself.
I did some more tests:
- If I have only one row in the subreport, I cannot reproduce the problem.
- If I have two rows in the subreport, the problem can be reproduced, but slowly (0.2 -> 0.6 sec after 1000 iterations).
Here are some repro instructions for a linked subreport:
1. Prepare repro example as above.
2. Add a linking field to the number table that maps 10 numbers to one "main nr":
CurrentDb.Execute "ALTER TABLE numbers ADD COLUMN main_nr INTEGER" CurrentDb.Execute "UPDATE numbers SET main_nr = ((nr - 1) \ 10) + 1"3. Link the subreport control from "nr" (main report) to "main_nr" (sub report) [sic, main_nr acts as the "foreign key"]. This causes each row in the main report to have 10 subreport rows.
4. Execute the repro loop with 200 instead of 40 iterations. The time required increases from 0.2 to 0.7 seconds on my machine.
