DataBase Engine Performance

Microsoft has written a whitepaper comparing the performance of its database products:

The bottom line is that Access (used in the construction of MammoBase) delivers the same kind of performance as FoxPro or SQL Server where there is a small number of users (i.e. no more than 50 concurrent users).

MammoBase uses Access 2000.  The whitepaper is comparing Access 97 to other Microsoft products, but many of the still conclusions apply.

Click here to return to the MammoBase home page.

Click here to download the entire whitepaper in zipped, Microsoft Word format (159 kb).  If you need help in dealing with zipped files, click here.

If you do not wish to read the entire whitepaper, you may skim the excerpts below (emphasis added).

Early versions of the Microsoft Jet database engine were not optimized for multiuser performance with large data sets. The emphasis in Microsoft Access versions 1.0 and 2.0 was more on ease of use, not on engine performance. However, the 3.0 version of the Microsoft Jet database engine, which appeared in Microsoft Access 95, was completely rewritten as a 32-bit multithreaded process. The Microsoft Jet team incorporated much of the performance work, including Rushmore technology, that made the FoxPro engine fast. Additional performance work was done in the 3.5 version of Microsoft Jet, which ships with Microsoft Access 97 and Microsoft Office 97.

Both the FoxPro and the Microsoft Jet engines have been engineered to provide excellent performance with a range of database sizes. In many situations, there might not be a significant performance difference between them.

In querying situations, in particular, the difference between the two engines may be small, due to the fact that the Microsoft Jet engine was rewritten to use many of the performance techniques originally found in the FoxPro engine.

Customers often ask for a general rule of thumb as to when they should move from using the Microsoft Jet or FoxPro engines to the SQL Server engine. This is a difficult question to answer.

With proper tuning, the current version of Microsoft Access can certainly handle 50 concurrent users and 100 megabytes of data, whereas previous versions could not.

With a small number of users, good hardware, and proper indexes, there is no reason to expect a significant performance difference between the FoxPro, Microsoft Jet, and SQL Server engines in many types of applications. The more users in the system and the less capable the client machines and LAN infrastructure, the more likely SQL Server would outperform the Microsoft Jet and FoxPro engines. One reason SQL Server may outperform those engines is that its processing power resides on a high-end machine that is not restricted by bottlenecks such as slow network infrastructure (that is, NIC cards, slow protocol, and transport).

Click here to return to the MammoBase home page.

MSDE (Microsoft Database Engine) has been introduced with Access 2000.  It is essentially a "SQL-Lite" -- but optimized for 5 concurrent users.

Click here to return to the MammoBase home page.