Database significantly slower AFTER splitting

[roland_access]

Our office database runs via a main form and table with about 100,000 records. It was unsecured and not split, so each user was opening the actual database file from their PC across the network. The computer it is hosted on has been in place for a year. All was fine and pretty smooth, access times were not a problem, even when performing multiple append queries on all the records to another table across the network.

However, recently the database was secured and encrypted using the wizard and MS FAQ etc. This all ran smoothly and is fine. On recommendation, the database was then split using the Split wizard, with the BE still on the main computer but a FE on each user's machine.

Opening the database and logging in brings up the main form as speedily as before, and navigating through the records is marginally faster by about 10%. However, anything else takes a lot longer. For example, append queries now take 2-3 times longer, as do update queries etc. Adding a table in query design results in a marked pause of a few seconds, which it didnt before. Open a form in design view, make some simple changes and save and it will take minutes to save rather than seconds.

This happens on every machine's FE, including the FE on the machine that is hosting the BE. What has gone wrong please?

 

[Mile-O]

Bear in mind that now the tables are linked between two databases, your application has to go to the other database (the BE) and retrieve the information; likewise with deletions, and appending.

 

[Autoeng]

What has gone wrong please?

Nothing has gone wrong, you are just seeing what happens in a split db. When your users were all accessing the unsplit, unsecured db you were very lucky that you had no problems (especially over the course of a year). Given time in that setup I am positive that errors and corruptions would have begun to manifest themselves. I think that you are better off with the split db even if it has time implications. Look at http://www.fmsinc.com/tpapers/faster/index.html for 168 tips to speed up your db.

 

[roland_access]

Of course, but the slowdown is very apparant, even when the FE is being used on the same PC as the BE. Also, before when running these queries, the data was being processed on one PC but was located on another, but it was still 3 times quicker than now.

I thought splitting sped up a large database, not slowed it down! What is the point of splitting a database, unless its a read only one where no large data appends or updates occur.

EDIT: We did have corruptions regularly on our old machine, until a new PC was purchased with Windows XP and a lot of RAM. Now its been running perfectly.

Im not going to revert back to non split, as if nothing else at least im harder to replace because its more complicated

 

[Autoeng]

Also, before when running these queries, the data was being processed on one PC but was located on another, but it was still 3 times quicker than now.

That's incorrect. The machine accessing the "server" where the db was located didn't do any of the processing, that was done on the "server" original. The only way processing could take place on the users machine is if the db is copied directly to it.

 

[roland_access]

So seeing as there were no corruptions or errors at all when the database was unsplit, its well worth reverting back for a speed increase of 300%. In this case, splitting a DB has been very bad.

 

[Autoeng]

Read these posts for reasons to remain with the split scenerio. Also, try some of the FMS tips to speed it up. Think about what you use your db for and why you have to do so many append and update queries. Are there other ways to do that? Instead of allowing users to create queries look into Query By Form.

http://www.access-programmers.co.uk...p?s=&threadid=7283&highlight=reasons+to+split

http://www.access-programmers.co.uk...p?s=&threadid=6733&highlight=reasons+to+split

http://www.access-programmers.co.uk...p?s=&threadid=5781&highlight=reasons+to+split

http://www.access-programmers.co.uk...?s=&threadid=17304&highlight=reasons+to+split

 

[roland_access]

The procedures that require these queries to run are quite detailed. Time and resources are not available to redesign major parts of the database unfortunately. Thanks for all your help though.