Deleting Records Before Packaging Causing Slow Reports

[Coy Howe]

This one seems bizarre!

We have a database consisting of a main table and 12 - 15 "sub" tables,
which are connected via cascading relationships. The database performs
many complex calculations and displays the results in both forms and
reports. In the process of developing the database we "entered" 12
sample clients. Before packaging the database we remove the sample
clients by deleting them from the main table, which cascades the
deletions to the other tables.

The problem is this:

The reports we use to display the calculations and appropriate results
run in a reasonable time (8 to 10 seconds to open in display format)
before we delete the sample records and package the program. However,
when the sample records are deleted and the database is packaged, the
reports take 2 to 3 minutes to open. We have tried dozens of
combinations and the common factor which seems to slow the reports is
the deleting of the sample records. Packaging the software with the
sample records left in will allow the reports to run normally; however,
we obviously don't want the samples available to the end user.

In addition, we have two big reports (one is 40 pages and the other is
105). Both reports are a series of one and two-page sub reports
"stacked" onto a main report. Is there a better way to do this? We
also have a small occurrence of the bigger reports printing a blank page
or two when displayed in preview format. Any ideas?

Also, we use a series of "nested" queries to calculate and display
complex formulas. Is there a better way to do this?

Thank you! Coy
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Allen Browne]

If you have only tested with 12 sample clients and your users have hundreds
or thousands of clients, then the problem may be that the structure and the
stacked queries are inadequate.

Before you jump to that conclusion, though, be sure to follow through all
the issues listed in Tony Toew's excellent Access FAQ at:
http://www.granite.ab.ca/access/performancefaq.htm
The combination of issues like Name AutoCorect and subdatasheets can make an
order of magnitude difference.

Another factor is that Access the JET query optimizer compiles your queries
with a plan that makes sense at the time it was created. If the real data is
radically different, the queries might be using completely the wrong
approach. After you have a real-world scenario, try opening the queries in
design view and making a change (such as replacing a field with itself) so
as to force them to recompile.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Coy Howe" <cf*@olypen.com> wrote in message news:42**********@127.0.0.1...

This one seems bizarre!

We have a database consisting of a main table and 12 - 15 "sub" tables,
which are connected via cascading relationships. The database performs
many complex calculations and displays the results in both forms and
reports. In the process of developing the database we "entered" 12
sample clients. Before packaging the database we remove the sample
clients by deleting them from the main table, which cascades the
deletions to the other tables.

The problem is this:

The reports we use to display the calculations and appropriate results
run in a reasonable time (8 to 10 seconds to open in display format)
before we delete the sample records and package the program. However,
when the sample records are deleted and the database is packaged, the
reports take 2 to 3 minutes to open. We have tried dozens of
combinations and the common factor which seems to slow the reports is
the deleting of the sample records. Packaging the software with the
sample records left in will allow the reports to run normally; however,
we obviously don't want the samples available to the end user.

In addition, we have two big reports (one is 40 pages and the other is
105). Both reports are a series of one and two-page sub reports
"stacked" onto a main report. Is there a better way to do this? We
also have a small occurrence of the bigger reports printing a blank page
or two when displayed in preview format. Any ideas?

Also, we use a series of "nested" queries to calculate and display
complex formulas. Is there a better way to do this?

Thank you! Coy