I have a report that takes quite some time to run. Would having the underlying query open and hidden speed up this time?? The reason I am asking, is that two reports use this query (one directly) and I would like to save time when printing both. Is the workable (Plan A) or must I learn that thing called patience (Plan B). Brambojr
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Strategy question
- Thread starter Brambojr
- Start date
Plan A is an interesting theory, but I honestly can't say whether it would help. My hunch is it wouldn't--that the open query would run separately from the query underlying the form.
But you may not have to go with Plan B just yet. Often, performance problems can be solved by restructuring your query, adding indexes, or both.
If you want to try adding indexes, what will help most is to index join fields on the "many"-side tables. Additional indexes will slow down data entry slightly, but unless you're doing massive numbers of insertions it probably won't hurt you. They can really speed up joins, though. (Note: If a join involves multiple fields, open the Indexes dialog and create a single index over all the fields. Single indexes on each field won't help as much.)
Restructuring your query is harder. The idea is to use a subquery that contains a certain subset of the tables in the current query. You then replace those tables in the main query with the subquery. But deciding which subset of tables will be most helpful takes intuition and knowledge of the join field values and their distributions within their tables. I can explain it better if I know some actual facts about your record source. Why don't you show me your report's record source as an SQL statement? From that I can make some guesses about what questions to ask next.
You might also want to look up "performance" in the help file, if you haven't already. Rick Sprague
But you may not have to go with Plan B just yet. Often, performance problems can be solved by restructuring your query, adding indexes, or both.
If you want to try adding indexes, what will help most is to index join fields on the "many"-side tables. Additional indexes will slow down data entry slightly, but unless you're doing massive numbers of insertions it probably won't hurt you. They can really speed up joins, though. (Note: If a join involves multiple fields, open the Indexes dialog and create a single index over all the fields. Single indexes on each field won't help as much.)
Restructuring your query is harder. The idea is to use a subquery that contains a certain subset of the tables in the current query. You then replace those tables in the main query with the subquery. But deciding which subset of tables will be most helpful takes intuition and knowledge of the join field values and their distributions within their tables. I can explain it better if I know some actual facts about your record source. Why don't you show me your report's record source as an SQL statement? From that I can make some guesses about what questions to ask next.
You might also want to look up "performance" in the help file, if you haven't already. Rick Sprague
- Status
Similar threads
- Locked
- Question
