Splitting databases and processing speed

[KathyL]

I've a very large Access database I support. I have about 25 users. Some tables have hundreds of thousands of records.

I've always found that keeping the database "un-split" gives the fastest processing speed. I know Microsoft recommends splitting the database to put tables in the back end and design in the front end.

I just had a reason to pull out a portion of design into a small mini-database, with links to the tables in the primary database. The result, the small DB runs terribly slow, it isn't going to be workable.



Are there some other parameters/options I should be aware of that would make this other mini-database run queries and reports faster?

Thanks!

[orange]

How do you have the 25 users working on the same large data base without getting conflicts/locks? Is this read only? You don't get any corruption errors?

[KathyL]

How do you have the 25 users working on the same large data base without getting conflicts/locks? Is this read only? You don't get any corruption errors?

Access has full record-level locking for multi-user. No difference if it were split or combined. No, obviously, it is not read only. My corruptions are very rare, often something more on a server failure level.

[pbaldy]

There are some thoughts here:

http://www.granite.ab.ca/access/performancefaq.htm

I personally would never have an un-split multi-user application.

[KathyL]

There are some thoughts here:

http://www.granite.ab.ca/access/performancefaq.htm

I personally would never have an un-split multi-user application.

Thanks for the link. Lots pertaining to older releases... I'll also look for stuff more current.

[KathyL]

Thanks for the link. Lots pertaining to older releases... I'll also look for stuff more current.

In going through that list, I found something, and have tested it. It may help reduce just a little bit of processing time. It is the Name Auto-Correct feature. Default is ON.

Microsoft says you can turn it off: http://support.microsoft.com/?kbid=200600

Makes sense to not track potential design changes of a user in a multi user environment, which are only going to fail anyway, because one has to have exclusive access to make changes. So why the default is ON, I do not know.

[pbaldy]

That feature is historically so buggy that most of us turn it off for every database anyway. I'd love them to change the default to off. I've heard the persistent connection can be very helpful too.

[KathyL]

That feature is historically so buggy that most of us turn it off for every database anyway. I'd love them to change the default to off. I've heard the persistent connection can be very helpful too.

What do you mean by "persistent connection"?

[pbaldy]

The first item on that link:

http://www.granite.ab.ca/access/perf...ldblocking.htm

[KathyL]

This morning, I finished a very long test. I decided I'd test the frontend/backend concept using my large database. For many years, I've just not bought into arguments that this was better.

I made copies, and split the database. I have some benchmark test tools I've used previously, by designing a form, and upon a button command, it will run a series of form loads and a half dozen queries. I have start/end times, and display the resulting seconds for each test.

Tested 3 ways:
1. Single DB, tables, forms and queries all in one DB. Like original.
2. Split frontend/backend. Tables on backend on server. Forms/queries in front end, but 2nd DB also on server.
3. Split frontend/backend. Tables on backend on server. Forms/queries in front end, but 2nd DB placed on local client PC.

Fastest process. by far, is still the single DB (original mode)

In comparing the front/end/backend tests, placing the front end on the client PC gave responses slightly faster. For example, server front end gave times of 12.51, 14.57, 12.67, 15.54 seconds. Times for front end on PC were 13.11, 11.12, 11.03, 11.05.

My single DB gave times of 9.6, 9.14, 9.78, and 9.2 seconds. Obviously the fastest.

Anyone care?

[byterbit]

I care, and good work.
I did it your way for 10 years, but often had corruption errors. I bought into the splits after solving horrid corruption issues on a new client,and have stayed with it for the last 4 years. However I am now getting clients screaming "too slow !" with a 60 MB 100,000 record app on a server ( with the front ends on the server too; dont ask about that)
I'm on the forum today looking into the idea of porting into SQL Server /MYSQL for the back and staying with Access for the front. It will either be linked tables or no more bound forms; that is the question.

[RuralGuy]

Even Tony Toews (Paul's link) admits the system is slower when split. He and we still recommend splitting to FE/BE. Most developers find that somewhere around 10 simultaneous users pretty much maxes out a pure Access solution. We then start moving to an SQL or some other variation server backend for both speed and dependability.