Report speed

[NeilT123]

I am relatively new to access but have built a report which has 4 sub reports built into it. Each of the reports have individual queries.

Individually the sub reports all open quickly but if I run the main report it seems to take ages before the report appearing on the screen. 35 seconds for a 29 page report to be exact.

Are there any general guidelines or suggestions as to how to speed up the generation of my main report?

TIA.

 

[Hmadyson]

each of the queries should be pretty plain. Most of the calculation should be done directly on the report.

 

[dhookom]

If you have any bound "[Page] of [Pages]", try remove the [Pages].

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[NeilT123]

Thanks guys for the input. Since posting the thread I have tidied up the report.
I have checked all the queries, stripped out un necessary data and limited the queries to only report the required data.
I have checked that all the links (master/child) are indexed and am in the process of checking that all the tables are related.

Before I started, my "benchmark" report, 29 pages consistently took 43 seconds to load.
Since making the above changes it is 20 seconds.

As a result of your input I deleted the "page" of "page" field and the report is now almost instantaneous.

I just want to complete the tidy up and have 2 questions.

1. The queries have IIF, then, else statements incorporated into them. The statements are only small but there are a number of them in each query. I read somewhere that it is not a good idea to build IIF statements into queries. DO YOU AGREE WITH THIS? IS THERE A BETTER OPTION? (eg when or where statement)or IS IT NOT A PROBLEM?

2. I use the "page" of "page" facility for updating reports. IS THERE A BETTER WAY OF SETTING THIS UP OR A WORK AROUND?

Thanks again for your help and any further input you may be able to offer.

 

[dhookom]

1. I don't think IIf()s would slow down your report. I suppose it depends on the exact usage. Most of the time, I put these in the report control sources rather than the query. I like to keep the queries as "lean" as possible.

2. I don't know what you would mean by using Page "for updating reports".

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[NeilT123]

>>2. I don't know what you would mean by using Page "for updating reports".<<

Sorry that wasn't very clear. I generate paper reports which are sent to my customers. A small amount of information may change which means I need to print a new report but rather than print the whole document I only print the pages which have changed and then use the page numbering to insert the new pages into the old report.

So I need a "page X of Y" facility. But do you know if there is one which doesn't kill the loading speed?

 

[dhookom]

It sounds like you could use just [Page] without the [Pages]. Your biggest performance hit comes from the [Pages] part.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]