subject: Performance Problems With Multi-user File Based Database Applications [print this page] Performance Problems With Multi-user File Based Database Applications
Business applications are often found to use file based databases such as Microsoft Access, FoxPro and Paradox. Many make use of built in features of the platform to create the application itself. This, itself, is not an issue for a database which is only used lightly by one or two people however these applications have a habit of becoming important to a business and the number of people which require access grow.
The performance of applications built in this way degrade fast in multi-user environments and holding your data in these database formats presents a number of problems and limitations.
First, these databases where not designed to hold large amounts of data and often have defined limits as to how large a dataset may be. They also tend to build up lots of temporary data, especially when multiple users have simultaneous access, as no software on the computer hosting the database is actively maintaining the file or controlling access.
Secondly as access to the file is controlled by the operating system's IO software of both the client machine and 'server' large sections of the database become locked while a client is reading or writing to the database. In some cases if a user invokes large or complex query other clients have to wait until the parts of the database they wish to access are freed.
This problem is magnified for relatively small databases with a large number of users, as the probability of the section of the datafile you have requested are more likely to be locked.
Third, when a client executes a query it will often have to load almost all of an index or table before it can gain access to just one record. This is because the client machines themselves are reading the database file directly and so to locate just one record they must load much of the file into the local machines memory to determine where the record exists within the file.
The amount of data that must be loaded is dependent on the database platform but key parts of the datafile will become locked and interrupt other people using the database.
Finally, as it is the client's themselves which do all the processing any underpowered computers will suffer heavily and as they will maintain exclusive or shared locks on the datafile they are very likely to have a significant impact on everyone else using the application.
Applications which store their data in this way often describe themselves as Client-Server applications. However the method of accessing the database is not far removed from the method used by a stand alone application and as demand for access to the system grows the performance of the system will rapidly fall to an unusable crawl.
While the problem can temporarily alleviated by adding extra horsepower to your client machines, network and upgrading the hardware in the system hosting the database the fundamental flaw of software designed in this way mean the problem will eventually return.
Ultimately software of this type must be rewritten using a true Client-Server model where the workload is shared between the server & client and control of access to the data is managed centrally by the server(s).
