Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Location
    US
    Posts
    343

    Question Unanswered: Report's performance

    Hello all,
    I've a report which is based on a query. When I run a query, i get the results back pretty quickly (around 3, 4 sec.) The data set is about 6000 records. Now, when I run my report which comprises of 3 groups, it takes around a minute. I have also noticed that the status bar fills up quickly (indicating that the data is fetched) but then the control simply sits there for a minute before the report is displayed.

    Anyone knows a specific reason for that and any advice how I can make things run faster? May be an new idea about my report. The report is simply. Its just that I have to show group totals at the three levels.

    Thanks in advance

  2. #2
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Are you joining records from multiple tables into one query and then applaying criteria in that one query? That slows it down somewhat since Access has to pull all those records, join then and then apply the cirteria.

    If that is the case, create one query for the table (or minimum number of tables) that you are applying criteria to and then join then in on final query. Base your report on that final query.

    I used to have one big query that took minutes to generate a report - it has criteria and calculations. Breaking it into several intermediate queries and performing as much of the calculations in the report brought that time to under a minute.

    If you jave a lot of joins to do, split out those where you can apply criteria to first and then join them back.

    Just my 2 cents worth. Hope it helps!
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  3. #3
    Join Date
    Oct 2003
    Location
    US
    Posts
    343
    Thanks for your reply but the issue here is that the query is running fine. Its just that its taking alot more time than usual to display it on the report.

  4. #4
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Do you have any aggregate functions like DLookup in your report? Are you doing any other calculations in your report?
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  5. #5
    Join Date
    Oct 2003
    Location
    US
    Posts
    343
    No I have no other calculations in my report except for group totals. I tried reducing the number of group and now I'm down to 30 sec. instead of 60. but its still way more than 3 or 4 sec that its underlying query takes to run.

  6. #6
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    You mentioned 6000 records. In many circumstances, a query doesn't pull the entire dataset at once unless it has to when you view it on screen. To see if there is more to be pulled, press the "last record" button at the bottom. This forces the query to pull the entire dataset so that it can display the last page. The report has to pull the entire dataset at once and process each page so that it has an accurate count of total pages.

    And the status bar is bogus - don't believe it. I have a number of reports that are "dogs" due to sluggish ODBC connections and oddly enough, the status bar zips up to full just as fast no matter how long the report actually takes to process (sometimes in the minutes).


    The report is simply. Its just that I have to show group totals at the three levels.
    If I understand correctly, your report is a summary report only of the 3 groups? If that is so, create a "Totals" query and use that on your report. This will conduct all of the processing in the query before running the report.

    Good luck
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  7. #7
    Join Date
    Oct 2003
    Location
    US
    Posts
    343
    Thanks tcace. Your suggestion is valuable. I was also thinking on the same lines that to show 6000 records, Access must only have to pull all those records but also formats them too to display correctly (the group totals as well as grand totals). I'll do some mre testing and will post it here. It may help some one else too.

    And I agree, the status bar is just crap... Any idea how to just completely remove it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •