Reports take too long to open

[M Skabialka]

I have a report that runs a query that has several sub queries. If I open
the final query which summarizes the info in all of the others, it takes
under 5 seconds, but when I run the report that uses this query as a control
source it takes over five minutes. There isn't anything special about the
report - it takes the data from the query and throws it on details lines,
with a header at the top. What would make a report run so slow when it's
control source query runs quickly? The same thing happens if I try to
print - it takes about 5 minutes before it goes to the printer. Several of
the reports take this long, while their control source queries run pretty
quickly on their own.

 

[Duane Hookom]

Does your report contain a Page of Pages control? How about keep together
property settings?

 

[M Skabialka]

Almost all of my reports have a Page of Pages, and often Group together
though not on this particular report. It didn't used to be as slow, though
data accumulates daily. However the query is still fast, just not the
report using that query. How does Page of pages or grouping effect this?

Thanks
Mich

 

[Duane Hookom]

Did you try removing page of pages to see if it would help?
Calculating "Pages" requires the report to completely attempt a render to
determine the value. This is greatly complicated if there are restrictions
such as keep together as well as controls/sections that can grow.

There may be alternatives that don't require subqueries etc.

 

[M Skabialka]

Removing page of pages made no difference it takes about 5 minutes to run,
vs under 10 seconds for the query to run on its own.

 

[M Skabialka]

I started to recreate the report a little at a time, first the headers, then
ran it, then the group headers, etc, and was almost done so ran the original
to compare the actual look of the report. I ran it three times in a row and
it previewed within a few seconds. Thinking that something had fixed itself
I then abandoned the new one and didn't save it. Then the old one went back
to running ungodly slow.
Another strange thing is that if I give up on waiting for the report and
press CTRL Break, sometimes the report will appear immediately, other times
it cancels.
I have decompiled and recompiled and this made no difference. This is a
five year old database that hasn't had problems until recent slowness.
First created in Access 2000, ran in XP, 2003 and now in 2007 without
converting. I tried converting the FE to 2007 but it made no difference to
the speed of the reports. In each version there is a progress bar which
says Running Query which freezes at about mid point then eventually (4 - 5
minutes later) shows the report.
I prefer programming in Access 2000 - 2003 as I am more familiar with the
interface which is why I haven't converted until now.

The report does require subqueries as it looks at 8 tables and performs
refinements to sets of data in sub-queries which are needed for the final
summary.

Could there be some sort of corruption that decompile/recompile and
converting up to 2007 wouldn't find?
Mich

 

[Duane Hookom]

I would try create a new blank MDB, import everything into it, and try the
report.