Help with large database

[Rory_Small]

I am running a front end / back end setup (from end on local machine, back end on network). The network has slowed to the point where queries used in the form can take minutes to look up (but if the backend is local, it takes about 1/2 a second).

In order to keep working, we have moved the backend to one of the users machine, but that means realtime data is not available for all.

Anyone know of a way around this? (replica? something else?) I like the speed of the local machine queries, but really need the info out on the network.

Thanks,
Rory_Small

[PEB]

Hi,
Reelly there is slowing when your database is on the Server. I'm running the same type of application /front-end application/ and I experience the same type of problems!

But there is some solutions about this!

Usualy I create a temporary tables distributed on the local machines.
Then when I consider that a query is very heavy and the results of the query are very important, This query is appended to the local temporary table!

An other solution can be SQL, keys and index optimizing of your database!

For exemple I don't prefer to use text values for keys, or have multiple keys!
Index only the most important fields on which you do the most lookup!
More indexes can slow the time to append records in your tables!
Without indexes your search in the most importent fields will slow also!
When you use SQL simplify your SQL. Less tables bigger speed! This rule sometimes isn't true! It depends only on your relationship between tables!

The subqueries sometimes speed up your query sometimes not, it's question to test for every case!

If the subquery is very heavy it's better to Save the result in separate table on the local machine and then use the ready result in others queries!

Sometimes it's better to have conditions only on the indexed fields! Those conditions speed up your query! Less conditions on other non indexed fields, faster execution!

The last thing that I'm doing is introducing the info by the users on the local machines! It runs very good! Then they have to press a Save button that transfert the information on the network! So they do it for each document and so there isn't problem with actualisation of the information! The information is actualised on the Server! The volume of the database is near 1 000 000 records! And the most slow queries run for about 4 minutes! But there is a reelly big number of calculations and user defined functions!

One tip: It's better to not use frequently the user defined functions in your queries! They slow down your queries! But sometimes they are big advantage! ;)

[PEB]

Also in the documentation of MS Access I've seen some recommandations to speed up MS Access:

- the name of the database have to be less than 8 characters without the extension
-it's better that the database is stored in a folder near the Drive three
-the folder have to be also with max length of 8 characters