MS Access Efficiency, or Lack of Same
Posted on 2001-07-19
I had thought to use MS Access for our small installations, and offer MS SQL Server for the larger installations. But I'm getting some absolutely ridiculous response times with MS Access databases (accessed on a server via ADO using a client-side cursor), and am beginning to seriously doubt whether it is a practical proposition for a real-world, heavy-duty application.
Specifically, I fetch a 300-byte Access record by its primary (sequential number) key and display it. I test efficiency by stepping through the file sequentially. When I fetch one record per step, response is (barely) adequate. When I fetch two records per step, response is over a second, with a single user in development mode. I do not consider that even marginally acceptable. God help our poor users when the application is complete (with many more records accessed), and they're fighting each other for server access.
I am seriously considering discarding MS Access and going with CodeBase or Foxpro or MySQL or some other database which yields at least moderately sensible response times.
I had heard that MS Access was a dog, but this is ridiculous! Does anyone have any real-world experience to share concerning the selection of a DBMS?