Re: How to Imporve Report Perfomance

Discussion in 'Microsoft Access Reports' started by John Spencer, May 7, 2010.

  1. John Spencer

    John Spencer Guest

    First of all, there is no good reason to run a query before opening a report.
    The report and the sub-reports will call the queries independently. So
    executing the queries just wastes time.

    Next, I would not use sub FORMS on a report. I would use sub REPORTS on a
    report.

    Check that the fields involved in limiting the records or sorting the records
    are indexed. Also make sure the indexes for the fields used in the links
    between the main report and the sub-reports are indexed

    John Spencer
    Access MVP 2002-2005, 2007-2010
    The Hilltop Institute
    University of Maryland Baltimore County

    mrPela wrote:
    > I developed a report and it takes literally 30 minutes to open up. How do I
    > improve performance. The data set is a local table that is not on the back
    > end of the database. However there are about 4 sub forms on the report. These
    > subforms are all queries. I requery the subform queries prior to opening up
    > the report. What are some things that I can do to make this report open a lot
    > quicker?
    >
     
    John Spencer, May 7, 2010
    #1
    1. Advertisements

  2. John Spencer

    John Spencer Guest

    What do you mean by updating the query? Are you just executing a select
    query? If so, that should have minimal effect on the report's speed. The
    effect it MIGHT have would be to cause some caching of the results.

    The query will run when the report opens, so there is really no reason to
    execute the query unless it is
    == Updating a table
    == deleting records from a table
    == adding records to a table.

    One thing that sometimes can help if you have very complex queries is to dump
    the data into tables and then use the tables. This is very seldom the best
    solution but there are a few cases with very complex reports that I do this
    for performance reasons.



    John Spencer
    Access MVP 2002-2005, 2007-2010
    The Hilltop Institute
    University of Maryland Baltimore County

    mrPela wrote:
    > John - I am using subReports, I apologize. I wasn't querying the actual query
    > associated with the sub report. However, there are maybe 2 or 3 queries that
    > support the query for a sub Report. Therefore the 1 query that supports the
    > query is the one I'm updating which in theory I would think would make the
    > main query easier to open, NO???
    >
     
    John Spencer, May 7, 2010
    #2
    1. Advertisements

  3. John Spencer

    John Spencer Guest

    Making it an mde would have almost zero effect on the speed.

    Yes, running the query before hand should have NO effect on the speed of the
    report. Try it without running the query first and see if you get any change.

    As an experiment - make a copy of the report and remove the sub-reports. How
    fast is it?

    Now make a copy and remove three of the four sub-reports? How fast is it?

    Repeat for all the sub-reports and see if any specific sub-report is the
    culprit. If so, work on speeding that up.

    Sub-reports can slow down a report significantly. Without being able to
    actually see the report it is difficult to tell you what the exact problem is
    or could be.

    John Spencer
    Access MVP 2002-2005, 2007-2010
    The Hilltop Institute
    University of Maryland Baltimore County

    mrPela wrote:
    > Yes' It's a simple select query. The reason I do it is because I want to
    > limit the results to only a specific group. There are thousands of records,
    > and I only need records for a specific group of records. So in the query that
    > I'm running prior, I identify which group I want.
    >
    > are you telling me that doesn't have an affect?
    >
    > Also - my db is split (front end/back end). However it's not an .MDE. If I
    > were to make it one, how would that improve this performance issue I'm having.
    >
    >
    > mrPela wrote:
    >> John - I am using subReports, I apologize. I wasn't querying the actual query
    >> associated with the sub report. However, there are maybe 2 or 3 queries that
    >> support the query for a sub Report. Therefore the 1 query that supports the
    >> query is the one I'm updating which in theory I would think would make the
    >> main query easier to open, NO???
    >>
    >>> First of all, there is no good reason to run a query before opening a report.
    >>> The report and the sub-reports will call the queries independently. So

    >> [quoted text clipped - 18 lines]
    >>>> the report. What are some things that I can do to make this report open a lot
    >>>> quicker?

    >
     
    John Spencer, May 7, 2010
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest
    Replies:
    5
    Views:
    671
    Guest
    Apr 4, 2005
  2. Guest

    Report with Sub-Report with NO-DATA on Sub-Report

    Guest, Feb 7, 2006, in forum: Microsoft Access Reports
    Replies:
    11
    Views:
    568
    Marshall Barton
    Feb 16, 2006
  3. Guest
    Replies:
    0
    Views:
    471
    Guest
    Feb 8, 2006
  4. Gdareos

    Easy report? How to report on a record, skip, report, etc.

    Gdareos, Oct 13, 2007, in forum: Microsoft Access Reports
    Replies:
    1
    Views:
    340
    Guest
    Oct 13, 2007
  5. Roger Carlson

    Re: How to Imporve Report Perfomance

    Roger Carlson, May 7, 2010, in forum: Microsoft Access Reports
    Replies:
    1
    Views:
    261
    Roger Carlson
    May 7, 2010
Loading...

Share This Page