SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard dethfire's Avatar
    Join Date
    Aug 2000
    Posts
    2,477
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    why is access slow?

    I hear everywhere that MS Access is slow. Now I agree, I have a forum with 80k posts and I can tell you that the site tanks! But exactly why is it slow, and what makes SQL Server and mySQL so much faster? Also I heard somewhere that Access isn't a true database at all. Any ideas?
    Free Science Homework Help
    http://www.physicsforums.com

  2. #2
    SitePoint Zealot JEmLAC's Avatar
    Join Date
    Apr 2002
    Location
    Kansas City
    Posts
    156
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: why is access slow?

    Originally posted by dethfire
    I hear everywhere that MS Access is slow. Now I agree, I have a forum with 80k posts and I can tell you that the site tanks! But exactly why is it slow, and what makes SQL Server and mySQL so much faster? Also I heard somewhere that Access isn't a true database at all. Any ideas?
    Here's a partial answer. I could be mistaken on some of the technical details.

    To answer the second question first, Access absolutely is a true database. It does things like relational queries, insert/update/delete functionality on tables, which themselves can be indexed. It has a programming environment, including a forms and report generator. Compare this to, say Excel, which some folk I work with seem to want to force into a database role.

    What it is not, however, is a server database, like SQL Server or MySQL. The difference has to do with the way the database itself is structured. In a true server database, there is a separate program that serves up the data and has various optimizations that can be made to improve performance. Interactions with the database are through the server. Access, on the other hand is a file server based database system. I liken it to being a "passive" database where a server database is an "active" database. This is part of the performance bottle neck, since it is tied to the i/o ability of wherever the MDB file is located.

    The thing to remember is the focus and scope of Access as opposed to a true server-based RDBMS. I've found Access very useful for small scale applications and prototyping. It can get the design process off to a quick start.

    As an aside, I've heard the critique that this accessability (pardon the expression) has proven detrimental to the design process, since it allows any idiot with a computer to do database design. Speaking as a former idiot I find this viewpoint to be a bit elitist, but that's just me. And yes, I've definitely had to clean up some ugly-mugly database work built by folk just getting into db design, as I'm sure I've left a few frustrated developers in my wake with my older apps built in Access. Such is the learning process.

    Access doesn't scale particularly well, due to its file-server based nature and as such is not all that good for dynamic sites where several dozen (or more) users may be hitting it at the same time. I've heard of some folk who have been able to get Access to go under such conditions, but you have to be extremely judicious in your interactions with the database.

    I hope that helps. As always, if there are factual errors, I want to hear about it.
    Morning person by habit, not by nature.

  3. #3
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, that's about right, JEmLAC!

    It's also worth mentioning that one of the most fundamental differences between SQL Server and Access is that SQL Server is designed from the ground up to be incredibly scalable - handling thousands of requests a second, and being able to cope with terabytes of data. In contrast, Access was (and still is) only intended to be used as a standalone single-user desktop database. Over the years, workgroup sharing features were added, but ultimately it's still a single-user DB at heart.

    Depending on the load, Access databases can be good enough to act as the DB backend for a simple site, but as the database complexity, number of records, and hit rate increase, the Access database engine quickly becomes incapable of handling the load.

    Basically, Access databases were never designed to handle multiple simultaneous requests, and so are completely unsuitable for any serious web site!!!
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •