Query Effeciency Question

[ChuckRobert]

I am running a group of queries in Access 2010 to sort through records in a table into a work center roster for a given facility. For now, I am using the first query to pull the records just for a given facility. Then, referencing that query, I pull another to get Section A Shift 1 from that facility, Section A Shift 2, and Section A Shift 3, and sometimes a crosstab query to count assigned rank for each shift. These form the foundation for sub-reports on the facility roster. I am trying to speed up processing of this report, as this is taking much longer than my previous version that did the same function, but had fewer possible sections. As it is more comprehensive, this report includes some sub-reports that will not have any data, and this seems to be slowing things down. I read yesterday about coding a cancel event on sub-reports with no data, and will try that, but wanted to understand if Access queries would run faster if I saved the first query I use to pull records just for a given facility back as a temp table, and had all subsequent queries for this report reference the temp table. When subsequent queries run off the first query, does Access re-run the first query going through all records every time, or is that first query effectively only ran once? If so, I would not expect any performance advantage of referencing subsequent queries from the initial query, or a temp table. Suggestions?

 

[xenou]

I don't know if Access would save the first query for re-use. I do however also employ temp tables to speed up performance by putting an initial set of data in the temp table and running subsequent queries/reports on the temp table. Given that this does improve speed a lot, I think it's likely the case that Access doesn't have the "intelligence" to save the data itself for future use based on a query plan - that's where bigger databases come into play with more sophisticated optimizers.

 

[ChuckRobert]

xenou,<o></o>
Thanks for the feedback! After posting my initial question I noticed that the report would take a long time to load, but would. However, it will not print as an error message pops up saying "Cannot open any more tables". I find this odd, as the entire report is based on only 1 table, but many queries/sub-reports, DLookups, and about 12 crosstab queries are used to display required data in the report. I wonder what Access considers a "table" to be. Clearly something else is going on here that I need to figure out! <o></o>
Before your reply I redirected all queries to use my initial query as the base, but that did not correct the condition. <o></o>
I will implement your suggestion, and hopefully get the report to load faster, but suspect the answer to my "Cannot open any more tables" may still remain. Thanks again!<o></o>

 

[ChuckRobert]

Just FYI. I transferred all objects into a new copy of Access and the performance is much better. I also found a few issues that were not being flagged in the older copy.

 

[xenou]

Good. Access db's need a compact and repair from time to time too, if you hadn't already been doing that.
ξ