Splitting database (1 Viewer)

nancy54

Registered User.
Local time
Today, 03:30
Joined
Jun 19, 2018
Messages
49
I manage several Access databases at work. I am considering splitting them. I understand the security benefits. But can you explain, in laymen terms, how this will increase performance.

Nancy
 

Ranman256

Well-known member
Local time
Today, 03:30
Joined
Apr 9, 2015
Messages
4,339
splitting allows you to make changes to the Front End with the code, and not affect the backend tables.

then distribute the FE to all users so they always have the most current version.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 18:30
Joined
Jan 20, 2009
Messages
12,849
Unsplit databases get corrupted if accessed simultaneously by multiple users.

(The performance of a corrupted database is very poor.;))
 

GinaWhipp

AWF VIP
Local time
Today, 03:30
Joined
Jun 21, 2011
Messages
5,901
Also... un-split databases corrupt more easily because you are pulling the entire database across the network instead of just data and not just one User but all Users.

With split database you should see an increase in speed but this is also determined by other factors, i.e. code, macros, network, Server age, etc... So no promises but you should see an increase because now you are just pulling data and not the entire file.
 

shadow9449

Registered User.
Local time
Today, 03:30
Joined
Mar 5, 2004
Messages
1,037
With split database you should see an increase in speed but this is also determined by other factors, i.e. code, macros, network, Server age, etc... So no promises but you should see an increase because now you are just pulling data and not the entire file.

Important point here about split databases: if you do not maintain a persistent database, you could see a degradation in speed. It makes a big difference. This is item 16 on CJ_LONDON's list.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:30
Joined
Feb 28, 2001
Messages
27,001
can you explain, in laymen terms, how this will increase performance.

If you are sharing a monolithic database, it has to be in a shared area on a commonly accessible server.

Assumption: For the split case, you are only sharing the back-end with the tables in it. Each user has a separate, private copy of the front-end (with all the queries, forms, reports, macros, and modules) residing on their individual workstations.

When you launch the DB app, you open a file on the user's workstation. Since that file is local, the Windows file system will lock the file locally. (No network connection involved.) Since the FE file is in an area probably created by the individual user for the purpose of using this app, the user is also the registered owner of the file and folder, and has Full Control over that particular set of file-related objects. Opening a file takes out (at least) read locks on every folder in the fully qualified and enumerated path between the root directory of the drive and the file folder where the app resides.

To open queries, forms, reports, macros, and modules from the local FE file - EVERYTHING is local and stays off the network.

To open those same entities when the entire (monolithic) DB is on the shared server, you have to take out locks on every folder from the root of the hosting drive to the app's folder of residence - but in this case, you must take out those locks via the network because the hosting drive is on the server. Therefore, you have to encounter the overhead of Distributed Lock Management for the monolithic DB.

If you now consider the data (the tables in the BE file), it costs you the same amount of overhead whether the file was monolithic or split. So the data fetch/store costs are a wash-out between split or monolithic. No significant difference. Probably the same number of locks per file for both cases WHEN CONSIDERING ONLY TABLES.

But there is NO network load and NO delay for the GUI-related and code-related elements for the split FE whereas there is a huge overhead for the monolithic case.

One more issue: Lock collisions can occur because Windows Lock Management is based on the idea that if you have the file open, you might have to notify others if someone else is diddling around in the same part of the same file. This is because every file lock includes the capability of showing "interest" in what is happening to the file.

If you have an "interest" lock (technically, a non-exclusive read), the file system STILL needs to check each lock already there to see if any lock is "exclusive" or in some other way needs special attention.

So... if you have a monolithic DB, then EVERY TOUCH by ANY USER has to traverse EVERY LOCK in that file to verify that it isn't running into an exclusive lock. BUT... if you have a split DB, then that private user copy has local locks BUT NO OTHER USER SEES THEM! They are PRIVATE. So not only do you not have to worry about lock traffic over the network just to take out a lock, you ALSO don't have to worry about lock contention on the FE file - because as a private and separate copy, ALL of the locks are YOURS and nobody else's. So no contention is even POSSIBLE.

Now, one more consideration: File permissions - which require what is called "access arbitration" as you touch EVERY FILE and EVERY FOLDER on the server side every time you open a new connection. This means a lot of network traffic to perform the arbitration. But if the FE is local, there is almost never any arbitration issue. Only the BE needs arbitration.

So: The bottom line is that splitting (and sharing as according to the stated assumption) means less network traffic for everything that can be done locally.

This doesn't take into account that unless you have Gigabit Ethernet as a corporate backbone, the file transfer from disk to memory is faster for stuff held locally vs. stuff shared over the net. And that means an GUI load, reports, and code, being local, require less disk overhead.
 
Last edited:

Users who are viewing this thread

Top Bottom