If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to Imporve Report Perfomance
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? |
#2
|
|||
|
|||
How to Imporve Report Perfomance
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/...UBED1=ACCESS-L "mrPela" u59877@uwe wrote in message news:a7a66efa10fae@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? |
#3
|
|||
|
|||
How to Imporve Report Perfomance
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? |
#4
|
|||
|
|||
How to Imporve Report Perfomance
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? |
#5
|
|||
|
|||
How to Imporve Report Perfomance
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? |
#6
|
|||
|
|||
How to Imporve Report Perfomance
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??? |
#7
|
|||
|
|||
How to Imporve Report Perfomance
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? |
#8
|
|||
|
|||
How to Imporve Report Perfomance
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? |
Thread Tools | |
Display Modes | |
|
|