none
Report in ADP running very slow in Access 2010 RRS feed

  • Question

  • Hi

    I'm converting users from Access 2003 to Access 2010. The main application they use is an ADP that connects to SQL Server 2008.

    When using Access 2010, nearly all the ADP runs fine. However, there is one particular report that has gone from 30 seconds to 7 minutes. I've timed in it code - the 7 minutes is from before and after the Docmd.Openreport command.

    I've created a new ADP with Access 2010, then imported all the objects. The problem still remains. This report has approx 30 sub reports.

    I'm not in a position to convert to ACCDB at the moment, thats a project for another time. So, any suggestions on how I can speed the report up? In particular,

    • are there any database settings I should change?
    • Any suggestions on how I can where the delay is? It may be one of the sub reports, but I don't know how I would go about investigating to find which one is the issue.

    Thanks

    Simeon

    Friday, August 21, 2015 9:15 AM

Answers

  • I have seen setting the KeepTogether property to No, make huge differences in performance, so definitely turn it off wherever you can.  Where needed, first try using the group property 'Keep header and first record together on one page'.

    Subreports also can make a big difference. Remember that a subreport has to load it's data for each record of the main report. So if your main report has 1000 records, there will be an additional 1000 calls to the database for the subreport data. Sometimes you can eliminate subreports by joining the data with the main report data and put what used to be detail data in the main report into a group, and what used to be subreport data into the detail of the main report.

    Friday, August 21, 2015 12:26 PM

All replies

  • You need to isolate whether the problem is in retrieving the data for the report or the generation of the report itself. Open/run the report's data source separately. It that is slow, you'll need to track down the reason.

    I have seen differences in the way reports paginate between 2010 and earlier versions. You could try turning off keeptogether for report sections.

    Friday, August 21, 2015 10:18 AM
  • Hi Alphonse

    The data source itself responds fine. In fact its used for another report, and that reports responds promptly.

    I think your paginate idea has merit. As well as keeptogether, could report headers and group headers on the sub reports cause a problem?

    I removed a report header on one of the sub reports, and it has sped up a bit. There should be no need for a report header on any of the sub reports. I actually though that report headers on sub reports would not print.

    Thanks

    Simeon

    Friday, August 21, 2015 11:49 AM
  • I have seen setting the KeepTogether property to No, make huge differences in performance, so definitely turn it off wherever you can.  Where needed, first try using the group property 'Keep header and first record together on one page'.

    Subreports also can make a big difference. Remember that a subreport has to load it's data for each record of the main report. So if your main report has 1000 records, there will be an additional 1000 calls to the database for the subreport data. Sometimes you can eliminate subreports by joining the data with the main report data and put what used to be detail data in the main report into a group, and what used to be subreport data into the detail of the main report.

    Friday, August 21, 2015 12:26 PM
  • Thanks. I had not heard of using 'Keep header and first record together on one page' instead of grouping, thats very interesting.
    Tuesday, September 1, 2015 8:23 AM