Prev: Missing information when filtering
Next: Remove multiple rows of Null fields in a report
From: mrPela on 7 May 2010 09:53
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?

From: Roger Carlson on 7 May 2010 11:35
It depends an awful lot on things you haven't mentioned. Indexing, table
size, the construction of the queries, etc.

My first suggestion is to check that the table is indexed properly. Any
field that participates in a join, sort, or where condition is a candidate
for indexing.

If that doesn't help, you can make your 4 subform queries into MakeTable
queries, base the subforms on the temp tables created, and run them before
you run the report. You can do this all in code (say in the click event of
a button) to make sure the queries are run before the report is opened.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


"mrPela" <u59877(a)uwe> wrote in message news:a7a66efa10fae(a)uwe...
>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?
>


From: mrPela on 7 May 2010 11:45
Thanks Roger. When you say indexing the tables, you basically mean create
relationships using the manager or simply create a primary key in the table?

Roger Carlson wrote:
>It depends an awful lot on things you haven't mentioned. Indexing, table
>size, the construction of the queries, etc.
>
>My first suggestion is to check that the table is indexed properly. Any
>field that participates in a join, sort, or where condition is a candidate
>for indexing.
>
>If that doesn't help, you can make your 4 subform queries into MakeTable
>queries, base the subforms on the temp tables created, and run them before
>you run the report. You can do this all in code (say in the click event of
>a button) to make sure the queries are run before the report is opened.
>
>>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
>[quoted text clipped - 5 lines]
>> lot
>> quicker?

From: John Spencer on 7 May 2010 12:14
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?
>
From: mrPela on 7 May 2010 12:23
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 wrote:
>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
>
>> 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?

 |  Next  |  Last
Pages: 1 2
Prev: Missing information when filtering
Next: Remove multiple rows of Null fields in a report