1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

How to prove the limitations of using MS Access as a database?

Discussion in 'HTML, Graphics & Programming' started by ultim, 22 Oct 2009.

  1. ultim

    Hitman

    Joined: 6 Jan 2006

    Posts: 983

    Location: Midlands

    Hi,

    One of our client has an old application built using Classic ASP and Access back-end.

    Lately they are having alot of problems with the application i.e. pages not loading, really slow.

    Never had any issues with it before and I suspect it is due to the database (being Access) and the size of it. It is around 200mb.

    Is there an easy way to find out if the database is limiting the application?

    Thanks.
     
  2. ~J~

    Sgarrista

    Joined: 20 Oct 2003

    Posts: 7,558

    Location: London

    I don't think you should 'prove' it, I think they should ultimately accept that you're knowledgeable enough to advise correctly.

    Rather than waste time, could you not quickly knock up a mirror of the database in SQL and just change the connection from Access to SQL and show them the difference?
     
  3. DanF

    Mobster

    Joined: 16 Feb 2004

    Posts: 3,840

    Location: London

    Depending on how they are using it one of the main issues is it can start to struggle with anything more than about 10 concurrent connections.

    Have a look on this page which has links to MS KB's on Access

    what-are-the-limitations-of-ms-access

    ie
    "ASP also supports using the Microsoft Jet database engine as a valid data source. The Access ODBC Driver and Microsoft OLE DB Provider for Microsoft Jet are not intended to be used with high-stress, high-concurrency, 24x7 server applications, such as web, commerce, transactional, messaging servers, and so on."

    Oh and some more info, ie what happens if Access errors? you need to restart iis apparently (if your using it for web projects)
    Also it locks the db for all reads when it does a write and can only support one write at a time apparently.

    more access info
     
    Last edited: 22 Oct 2009
  4. ultim

    Hitman

    Joined: 6 Jan 2006

    Posts: 983

    Location: Midlands

    Thanks for the replies.

    As it was working fine when it was launched, i suspect its due to the size of the database. Would you classify 200mb Access database as big?
     
  5. Dj_Jestar

    Caporegime

    Joined: 18 Oct 2002

    Posts: 28,997

    Location: Back in East London

    Tell them to open a table with more than 65500 rows.
     
  6. ~J~

    Sgarrista

    Joined: 20 Oct 2003

    Posts: 7,558

    Location: London

    I think as DanF said, it's probably not really the size it's the number of concurrent users trying to access the database at any one point.

    200Mb I'd say is relatively small for an Access database, considering that, IIRK, Access has a 2Gb limit on the MDB files.

    Could be a combination of bad coding, but I'd hazard a guess towards the number of users trying to connect to it at once.
     
  7. ~J~

    Sgarrista

    Joined: 20 Oct 2003

    Posts: 7,558

    Location: London

  8. Dj_Jestar

    Caporegime

    Joined: 18 Oct 2002

    Posts: 28,997

    Location: Back in East London

    Last time I did it, it exploded. :/

    Just show them that link.. 2GB max size limit. That's not a lot.
     
  9. Dolph

    Man of Honour

    Joined: 17 Oct 2002

    Posts: 48,419

    Location: Plymouth

    It is when you are only using 200mb.

    While Access does have its issues, the problem here, as has been said, is likely to be the number of concurrent users undertaking transactions, rather than the size.

    You could look at MS SQL server Express as an alternative, which is better than Access and probably more suitable for the task at hand without requiring huge changes to the application itself.
     
  10. suarve

    Mobster

    Joined: 8 Oct 2005

    Posts: 4,190

    Location: Midlands, UK

    Absolutely not, in fact in my experience Access can handle that database size fine. I really started to see it slow down to a grind when I was using a database a tad under the 2GB limit - the efefct was worsened when people tirted to access this over a local network.
     
  11. Engram

    Hitman

    Joined: 6 Jul 2004

    Posts: 978

    Location: England

    Depends what you call "easy" but I would add some code to the pages that showed how long it takes to query the database, and how long it takes to render the page.

    The output might look like this:

    Code:
    Database query: 500ms
    Page render: 20ms
    Total: 520ms
    If it really is the database that's slowing it down then you should have some evidence.

    As others have suggested, you could also try replicating a couple of tables in SQL Server and then run the tests again.

    When it comes to performance stuff you really need proper scientific results. Saying that Access is cr*p isn't really enough. (It is cr*p though ;) ).