Performance Problems With Multi-user File Based Database Applications

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).

POST COMMENT

Another $902 Million for California's High Speed Rail System Sure to Help Economic Recovery Four Steps to Convert NTFS to FAT32 without Data Loss and Formating Partition Judgment Recovery Course Loses Favor Judgment Recovery Course Overview Incorporating Christian preaching in recovery treatment Remove Data Protection - How to Get Rid of Data Protection Completely A Brief History of Datacenters Access Database Becomes Inaccessible In Compiled Project or Compiled Database Recovering Tablespace From Corrupt Oracle Database Avoid Common Mistakes While Recovering Damaged Oracle Database Advanced Zip Recovery solution available to recover your corrupted Zip files How to hard drive data recovery - Available Data Recovery Advice How to recover data from an external hard drive