Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Performance with many users Access/MSDE (Access 2000)

    According to Microsoft the MSDE is optimised for a maximum of five concurrent users. Access performance seems to degrade after ca. 10 users.
    How bad is the performance degradation in an MSDE when it has for example 10 users, 15 users and how does that compare with an Access backend at the same level of activity?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Performance with many users Access/MSDE (Access 2000)

    I'll try to answer all your MSDE questions here.

    Memo fields seem more reliable with MSDE, as MSDE is really SQL Server, and it's ability to handle crashes seems more robust. There are still issues with memo (text in SQL Server, thus confusing) fields and being able to sort or perform other functions with them. We avoid using memo fields where we can, and the latest version of SQL Server (2000) lets us have varchar fields as large as 8000 characters. Note however that the MSDE that ships with Access 2000 is SQL Server 7.

    In the area of performance, for 1 or 2 users, and depending on how it is used, the MSDE may be faster. However if you have more than 2 users and you try to use ODBC links as you would with a full SQL Server install, it gets very sluggish. The reason is that ODBC typically opens at least two SQL connections for each user, and the performance throttling seems to kick in at 5 connections. On the other hand, if you take the ADP route, performance should hold up through at least 5 users pretty well. but development is often more work, and you loose the functionallity that we've grown to know and love in Access MDB files.

    The version that ships with Access XP is SQL Server 2000, and it uses a different mechanism for throttling performance that seems to work pretty well up to 5 users, but we haven't tested it beyond that. Hopefully someone else can comment on larger numbers of users. My hunch is that by the time you get to 10 or 15 users the performance will be on a par or even worse than using a Jet-based back-end. With the tricks we use in putting the front-end on a workstation, and other issues, you can make an MDB database work for 50 to 100 users as long as it isn't a transaction based system. On the other hand, we recommend SQL Server for most applications where there are more than about 10 users. It provides many functions that simply aren't available in Access, is more reliable, and provides better performance as it does many of the chores that would otherwise require moving large amounts of data across the network.

    I hope this gives you a better feel for the situation, and want to invite others to share their experience and opinions.
    Wendell

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Performance with many users Access/MSDE (Access 2000)

    Thanks - the larger text field possibility looks interesting. Currently the largest memo field is on the order of 700 chars and only ca 1% of records actually require a memo field most are around the 200 char length,

Posting Permissions

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