ansaurus

Question

MS Access caching of reports / query results

Answer 1

A:

Could you maybe keep the report open the entire time the database is open? Open it hidden when the database is opened.

DoCmd.OpenReport "YourReport", acViewPreview,,,acHidden

Then never close it while the database is open.

Alternatively, since you can deal with cache staleness, perhaps you could store the report's data in a local table for faster access. Since the users are aware of when new data is available, give them a command button which empties the local table and puts the latest Oracle data back in.

HansUp 2010-06-16 19:06:06

Loading some data to local tables when the file is opened could certainly help. Does Access have some sort of `OnStartup` event that fires when the user opens the file?

FrustratedWithFormsDesigner 2010-06-16 19:12:04

There a two ways you can do that: call a function from an autoexec macro; or set a form to open automatically when the database is opened --- and call your refresh cache table procedure from the form open event. I'm not so sure the cache table is sound practice, but if it makes your users' lives easier ...

HansUp 2010-06-16 19:28:02

If you use the cache table approach and this is the data for your 36K page report, you might want to keep the cache table in a separate MDB and link to it from your main application MDB. Importing and deleting large amounts of data can bloat your database; keep the bloat out of your application database.

HansUp 2010-06-16 19:31:00

@HansUp: I'm not sure I'd want to try to cache that one, but some of the others are much smaller (2-3 pages) and cachable. There's also some drop-down lists that display a column from their queries, and those can take a couple minutes to complete. If those were cached, the forms would be much more usable.

FrustratedWithFormsDesigner 2010-06-16 20:47:26

@Frustrated. OK, my concern was unfounded ... that you might gain faster response for the report at the cost of slower db startup every time.

HansUp 2010-06-16 21:00:54

@HansUp: I'd be OK with a slower start-up time. The users would probably leave it open all day, so they start it up once when they come in in the morning, and while they wait for it to cache some data they can go get their morning coffee...

FrustratedWithFormsDesigner 2010-06-16 21:05:48

@Frustrated How about using the Windows scheduler to open the db and run the cache refresh operations just before the users show up?

HansUp 2010-06-16 21:13:41

@HansUp: I think that one's a little more complicated than it needs to be: If the scheduler runs at 9:00 but someone doesn't boot their machine until 9:02, then what? Anyway, I just can't expect to have that level of control over the users' machines. I think caching the results for drop-down lists and short reports (1-5 pages) by loading them into temporary tables is probably the best way to go.

FrustratedWithFormsDesigner 2010-06-17 13:21:09

Good point, Frustrated. I didn't consider the machines would be shut down. Think I was over-eager about looking to be helpful.

HansUp 2010-06-17 13:34:59

Back in Access 2 days, when report recordsources could very easily get too complicated for Access, I not uncommonly populated temp tables to run reports from. It didn't really increase the load time of the report at all, even though it ran each time the report was requested. The reason is that the recordsource was complicated enough that it was very slow, and selecting the data into a local temp table didn't take that long, and so the time it took was no greater than with the old approach. You should benchmark and see.

David-W-Fenton 2010-06-18 21:36:12

Answer 2

+1 A:

Most server databases cache ad hoc SQL statements. That is, if you run the report, Oracle should cache the result, and if it sees the SQL statement come across the wire again, deliver the result from the cache instead of retrieving it all from scratch. I know that SQL Server does this and I assume any enterprise-level database will do the same. Along with the caching, of course, is some form of checking to insure that the cached data is still up-to-date. I don't know what level of control the DBA has over how this works, but you might look into implementing this server-side. A temp table might also be a solution.

David-W-Fenton 2010-06-16 20:39:19

Interesting... I hadn't thought of a solution on the server. I don't know what level of caching is enabled on the server. I don't know how much of the slow performance is in Oracle running the query and in Access handling the results...

FrustratedWithFormsDesigner 2010-06-16 20:50:20

The things to avoid in Access are heterogeneous joins between server and Access tables, WHERE clauses with unindexed fields, and ORDER BY or WHERE using expressions. Some of these things can work if there are criteria in the WHERE clause that limit the resultset sufficiently that not that many records have to be pulled. Sorting and Grouping in reports can be a big drain if they aren't on appropriate fields.

David-W-Fenton 2010-06-18 21:33:35

ansaurus

tags:

views:

answers:

MS Access caching of reports / query results

related questions