0

Something no one mentions and I find important to stress is that Access has a file locking mechanism for sharing the database on a network share which is orders of magnitude more efficient than any network database out there and therefore MySQL is no different.

The reason is the following, a file locking mechanism permits all clients of an Access database to cache somehow the database file and therefore to execute queries on it as if it were a local file. This of course means very little data is sent over the network in the case for instance of a SELECT which returns 5000 records, because the select is actually returned from the cached file.

A traditional client server model such as Mysql instead means that a query for 5000 records will involve a lot of TCP packets between server and clients to return the whole 5000 records from server to client, that will involve a high latency between the query and the completion of the return of results.

A file locking mechanism of course has its limits which usually incur when more than 5 users share a database heavily with a lot of modifications to it, in that case the locking mechanism (which in Access can be very selective up to row locking) will cause more delay than say a client server database which could theorically allow a lot more of concurrency.

I came therefore to the conclusion that if you need to share a database on a LAN with a couple of users therefore Access has few competitors, if instead you need to host a database on the internet or with tens/hundreds of users of course Mysql is better.

I also notice many other databases tried implementing the file locking mechanism but most failed in stability or weren't able to make it work as efficiently as Microsoft with Access which must have spent a good load of time and money on it. SQLite and H2 database locking mechanisms for instance are either very low performance or are highly not recommended for risks of corruption of the database:

SQLite uses POSIX advisory locks to implement locking on Unix. On Windows it uses the LockFile(), LockFileEx(), and UnlockFile() system calls. SQLite assumes that these system calls all work as advertised. If that is not the case, then database corruption can result.

http://www.sqlite.org/lockingv3.html

This database file locking mechanism uses native file system lock on the database file. No *.lock.db file is created in this case, and no background thread is started. This mechanism may not work on all systems as expected. [...] This feature is relatively new. When using it for production, please ensure your system does in fact lock files as expected.

http://h2database.com/html/advanced.html#file_locking_serialized

Can anyone shed some light on how or why is Access so efficient and whether there are any serious competing products which follow the same file locking shared on a network approach and which implement it in an efficient way?

closed as unclear what you're asking by Michael - sqlbot, Max Vernon, Mark Storey-Smith, RolandoMySQLDBA, Aaron Bertrand Jul 14 '14 at 20:18

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    I've never known a database professional who considered Access to be anything other than cumbersome, frustrating, corruption-prone product scarcely worthy of the label "database." To compare Access favorably against MySQL or even SQLite is ludicrous. There is no magic in Access. – Michael - sqlbot Jul 10 '14 at 0:44
  • I pointed out to some practical cases and explanations for when and why Access is superior to Mysql and other client-server databases. I'm waiting for a database professional to give some pragmatic answer not for gut-related opinions. – user9948 Jul 14 '14 at 14:23
2

When the own vendor says the following:

"While Microsoft Jet is consciously (and continually) updated with many quality, functional, and performance improvements, it was not intended (or architected) for the high-stress performance required by 24x7 scenarios, ACID transactions, or unlimited users, that is, scenarios where there has to be absolute data integrity or very high concurrency."

"When you need unlimited users, 24x7 support, and ACID transactions, Microsoft strongly recommends that you use Microsoft SQL Server with Internet Information Server (IIS). "

I wouldn't have many expectations for integrity and performance for multiple users over a network.

  • Indeed I wrote "...if instead you need to host a database on the internet or with tens/hundreds of users of course Mysql is better...", that was not my point. I even wrote "..A file locking mechanism of course has its limits which usually incur when more than 5 users..." – user9948 Jul 14 '14 at 14:26

Not the answer you're looking for? Browse other questions tagged or ask your own question.