PC Review


Reply
Thread Tools Rate Thread

Re: How to Imporve Report Perfomance

 
 
John Spencer
Guest
Posts: n/a
 
      7th May 2010
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?
>

 
Reply With Quote
 
 
 
 
John Spencer
Guest
Posts: n/a
 
      7th May 2010
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???
>

 
Reply With Quote
 
 
 
 
John Spencer
Guest
Posts: n/a
 
      7th May 2010
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?

>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: How to Imporve Report Perfomance Roger Carlson Microsoft Access Reports 1 7th May 2010 04:55 PM
Perfomance problem when not connected =?Utf-8?B?UG90YXRvZQ==?= Windows XP Help 0 9th Jun 2004 07:51 PM
Perfomance Logs and Alerts James Windows XP Performance 0 16th Dec 2003 12:38 AM
How-To: Remote Perfomance Monitor on XP in aWorkgroups?? Rami Windows XP Performance 0 21st Oct 2003 05:31 AM
WIN XP Home - Slow Startup/Perfomance Brock Cole Windows XP Performance 1 4th Aug 2003 06:43 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 07:41 PM.