Database Speed and Front End Size (1 Viewer)

[velcrowe]

I have a database that is split with the forms, queries, and reports representing the front end and linked to tables my users cannot access on the back end. I've compacted the database and due to the number of forms, queries, and reports the front end is 55mb. I have to keep the database in MS Access but would like to increase the speed. Can I put the reports in the back-end with the tables as well so that they are linked. The forms and queries are always being used and updated so is this a viable way to increase the speed of the database without affecting the integrity of the data?

Thank you in advance

 

[boblarson]

I've compacted the database and due to the number of forms, queries, and reports the front end is 55mb.

Definitely not an unreasonable size.

I have to keep the database in MS Access but would like to increase the speed. Can I put the reports in the back-end with the tables as well so that they are linked. The forms and queries are always being used and updated so is this a viable way to increase the speed of the database without affecting the integrity of the data?

No, not a good idea. You might want a separate Reporting Frontend maybe which might help, but do not put any objects other than tables in the backend as that increases the potential for corruption and then you lose your data. Better to lose a copy of a frontend, which is easily replaced by another copy, than to lose your data and have to re-enter a lot of information.

 

[boblarson]

This might also be something to read.

 

[Banana]

1) You can't link any other access objects. The only thing that can be linked are tables.

2) I suppose you could code it in such way that the backend itself runs reports, but you have to consider the ramification. If the reports will be run quite often, then it will steal the database's "availability" away from users. There also may be issues with corruption.

3) The usual methods for improving speed** includes those steps:

a) Checking that all columns in a join, where, order by clauses are all indexed
b) Verifying that the queries are optimized
c) Ask for few as possible records from any forms; always write queries with a WHERE condition as a recordsource for form and never use tables.


** By speed, I'm assuming you mean network bandwidth and time it takes for the database to load/open forms and reports.

 

[The_Doc_Man]

Rarely do I call down my friend Banana, but here I must clarify:

a) Checking that all columns in a join, where, order by clauses are all indexed

Not all columns in a WHERE are appropriate for indexing. When the number of rows returned as part of the SELECT include a selected value for something like GENDER, where you expect about 50% of the rows to be selected by such an index, you gain very little. I agree with the JOIN and ORDER BY suggestion.

b) Verifying that the queries are optimized

c) Ask for few as possible records from any forms; always write queries with a WHERE condition as a recordsource for form and never use tables.

The first sentence in C and my caveat to A both refer to something called "cardinality" - the number of rows expected from a query. For a query of a single value against a unique index, cardinality is 1. For the Male/Female case mentioned earlier, cardinality is about 50% of the population of the table. To really optimize your queries, try to specify the fields with lowest cardinality first.

The reason this is important is that Access imposes a limit on the number of indexes you can have, and each index costs you more overhead if you do lots of updating of indexed fields or if you do lots of insertions. So you want to minimize the number of indexes and maximize them at the same time. Which usually means you must strike a happy medium. (Why you should assault a joyous fortune-teller, I'll never know... but that's the phrase they use.)

Seriously, it is a fine art to tune indexes for optimum speed in a complex system. You will probably have to tinker with it.

 

[Banana]

The_Doc_Man is correct. My post was pretty much an oversimplification, and it's not as simple as "load her up!". My apologies for implying otherwise.