Results 1 to 5 of 5
  1. #1
    netgrim is offline Novice
    Windows XP Access 2003
    Join Date
    May 2010
    Posts
    2

    slow performance with multiple users

    using Access 2003 (11.8204.8221) SP3


    We have a split database front-end, back-end, (45mb format 2000)
    the back-end in on a NAS on a 10/100 network
    when a single user use the database the performance are good, but when a second user connect to the database the performance of queries are really bad. Even if the second user does nothing and stays in the main menu.

    The problem is intermittent, sometimes both user have good performance, sometimes only one or neither. Sometimes compact the front-end fix the problem, sometimes compact the back-end fix the problem, and sometimes overwriting the front-end with the original backup copy.

    The same query:
    (select photo from item where id = <id>) id is the primary key, 110,000rows
    run from debug mode in vba
    when 2+ user: 4-5sec
    when only one: instantaneous.

    so when many queries are executed after a single user action, it may take 30sec to execute.

    When I monitor the network traffic using the task manager, I can see why it's slow, the simple query transfer about 30Mb, even the field I am querying is a text of about 50chars. Using a sniffer (wireshark) I can see a major part of the "item" table being transfered over the network.
    But in single-user, it's fast and only ~20k is transfered on network.

    why the behavior in single-user is different in multi-user?
    after trying most of thing I found on groups and forums
    I'm thinking to switch to a sql server back-end

    Did someone else got this problem?

  2. #2
    Datagopherdan is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Dec 2008
    Posts
    220
    One suggestion, I can think of. Have you checked to see that the fields you're querying on are indexed?

  3. #3
    netgrim is offline Novice
    Windows XP Access 2003
    Join Date
    May 2010
    Posts
    2
    the id is indexed since it's the primary key of the table

  4. #4
    Datagopherdan is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Dec 2008
    Posts
    220
    What about other fields in your table that you're using in your query?

    Quote Originally Posted by netgrim View Post
    the id is indexed since it's the primary key of the table

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    This link has several good suggestions: http://www.granite.ab.ca/access/performancefaq.htm

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Multiple users keying into one form......
    By softspoken in forum Forms
    Replies: 11
    Last Post: 04-13-2010, 02:18 PM
  2. multiple users
    By ramkitty in forum Access
    Replies: 1
    Last Post: 03-22-2010, 07:15 PM
  3. Multiple users in database at same time
    By toshea in forum Security
    Replies: 4
    Last Post: 12-07-2009, 02:22 PM
  4. How to have multiple users
    By Eric1066 in forum Access
    Replies: 5
    Last Post: 11-19-2009, 05:14 AM
  5. Multiple users accessing same database
    By 4ACE in forum Access
    Replies: 0
    Last Post: 02-28-2008, 03:10 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums