I have a lot of queries based on queries. These all work as desired, however they can be slower than I'd like.

Given that my company has no intention of changing to another piece of software I am, therefore, limited to whatever speed I can get out of Access.

Are there any general rules or guidelines that a more experienced person could recommend to ensure that all these queries run as quickly as possible?

Thanks in advance.

Make sure you db is normalized, your fields are of the correct data type, and have your keys and any other field that is in your Where clause frequently indexed.

Also the less fields you include in the resultset the quicker it will run. Limiting the number of functions you use can improve perfomance as well.

Removing calculated fields from the query will also allow it to run quicker

Thanks both.

I'm working through the tables, at the moment. I've 'inherited' the db and while it appears to be normalised, there have been no indexes on the tables I've checked, thus far. Could well be the major cause of my problems.

I shall check for calculations next. Can't immediately think of any, but that doesn't mean they aren't present.

I know what a pain it can be to inherit a db. Remember if you do find calculations you can always calculate them in the form or report the the data is displayed in.

The embarrassing part is that I got hold of it some time ago, I just needed to focus on other parts. Now that I've got a chance to test the reports, it's becoming glaringly obvious that something's not right.

I would also:

1) Ensure that Front End Mde on the Client side
2) Persistent connection to the Data table if Front Ends are used
3) Run msaccess.exe /decompile and recompile the source DB modules
4) Repair and compact source DB and remake FE

The expressions are the least of your worries indexing will help along with filtering data upon opening forms or reports.

Simon

Okay, displaying my ignorance here, but I'm not sure what you mean by points 1 and 3?

From my personal experience, never run applications from a server, so each user has an MDE on their PC that maps the data Tables held on the server. All the forms reports, scripts are on the client. All that the server is being asked for is the raw data. I have a "program library" which has the editable source of the application. The users get a compiled version. Every so oftern I decompile the "program" database Repair and Compact and then give this version as an MDE to the users. Only real performance problem easily overcome is the MDE hooks into a Table from the Server to create a persistent connection and therefore a record in the ldb file, Access is a damn sight faster.

Simon

If your database has been split into front-end/back-end (a way to optimize some part of processing), you can compile the front end into an MDE file which cannot be modified by its user but which is about as efficient as possible for whatever is in it. (The statement presupposes that you have already squeezed a lot of blood out of that turnip before reaching that point.)

3 refers to assuring that the code is freshly compiled before storing it into an MDE. Access does happen to have a "compile on demand" feature, which is both good and bad. Good because it runs even if you are sloppy. Bad because when it runs like this, it runs v e r y s l o w l y....

When you are nesting queries, it is possible that you have to nest for joining purposes to bring parent/child records together. It might also be that you have to filter some of your queries by content. If this is the case and if there is ANY POSSIBLE WAY to do it, filter in the lowest layer query, the one that works on tables. All selection by value should be done at that layer, leaving all other operations a smaller data set on which to operate.

Indexes are another important point. Queries can be built without an index but they run much better if they are built WITH an index. Also, if you have no indexes, you have no relationships (in the formal sense) because the ONE side of a one//many relationship MUST have an index. Again, you can write a JOIN without an index but it will not be optimal. It will have to do what is called a relation scan rather than doing an index scan. I.e. lots of overhead in reading the tables. Beware of having too many indexes on a given table. You can have up to 10, unless that has changed from my version of Access. But you would prefer to have only the indexes you need. So look to those things that are FREQUENTLY searched because of JOIN operations, or improper joins formed by WHERE clauses of the form

SELECT {bunch of fields} FROM A, B WHERE A.B = C.D ...

This is a JOIN-less join that works correctly but is slower than if you did it with the JOIN syntax. Look that up if you aren't familiar with it.

Both extremely helpful, gents.

I guess I know what the nexy couple of days are going to be spent doing.