report load time

[shiner]

I have several reports in my db that take close to 30 seconds to open. At first I thought it was my machine, a PIII 450 - so I changed machines to an 850 (I know, still really old, but the best my office has) - still it takes just as long to load. However, it is only on the first time that the report is opened... Subsiquent openings of the same form are almost instantanious. What causes this? The forms were originally based on queries that I created, but I tried to speed them up by inserting the SQL statement in the record source line - via the query builder feature. The funny part is that when I ran them that way they were SUPER fast, but that was because it was the second+ time I had opened the report during that session, so it really did not matter. I rebooted the machine to test my speed theory and was dissappointed to see that it had returned to its original load time even after just using the SQL statement.

So, do you have any ideas? is there a way to run this process as a background item without impacting db performance or data input?

Thanks! I would just like to speed this up. It is difficult to explain to my users why they have to wait!!! I am sure you all can relate!

 

[Fornatian]

Saved queries are pre-optimised by the JET db engine so therefore open quicker than direct SQL recordsources which are interpreted at every run time.

If your entire db is housed on a slow network this will cause your entire db to run slow. split the front end and back end if not already done.

Remove all non-essential controls, pictures, lines.

Compact the db if not already done.

That's me done.

 

[Rich]

You might also try removing calculated fields from the query too, if you have any that is

 

[shiner]

Thanks guys!

First, Fornation - what do you mean by split the front and back end? I assume you are speaking of the tables as the back end? How does one do that? I compress the db on close, and only have 3 graphics in the entire db.

Second, Rich - when the form is loading, I see the word calculating in the bottom status bar. However, I do not know of any calculated fields - but it sits there for some time with that in the status bar. Any ideas on how to isolate that and possibly get rid of it?

 

[Fornatian]

read help on 'splitting a database'

how many records are you opening in this report? it may take a while because of the number of records.

where does the report pull it's records from? direct from a table or from a query? if a query, check it's source tables, they may be queries which have calculated columns.

lastly, check and see if any VBA is slowing the opening of the report by applying formatting or performing calculation behind the scenes.

it is common sense but it's good to remember that the more formatting and calculating *required* will render a longer opening time

 

[Pat Hartman]

If you use page x of y, try changing to just page x. The former forces Access to totally format a report before it can open the first page. This can be quite slow if there is a lot of data involved.

 

[CadMonkey]

You Geniui, you've just saved me hours of time waiting for report to load, just by saving the select statement for data source of report as query and then referencing that, even with calculated *must have* fields!

Thankyou!