Results 1 to 7 of 7

max Mbps of typical query

  1. #1
    fishhead is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    76

    max Mbps of typical query

    I've just upgraded my network and am still noticing MSAccess going into "not responding" status when i run my queries.


    When i check the Ethernet status on my workstation i can see the non responding status corresponds with the RECEIVE size of the graph exceeding 100 kbps.

    Am i correct in suspecting the request to the backend tables is returning a result that is maxing out the capacity of our network to send the required data back to the workstation? If so what is the maximum data MSAccess can reasonably return without going into "not responding" status?

    I'm not sure how to go about making the queries more efficient as the data returning back is what is required (no other data coming back along with the required data for instance).

    Any ideas on how to fix this problem is greatly appreciated.

    thanks,

  2. #2
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,310
    Most applications will use the full bandwidth available to them at any given time. I don't think Access is special in this behaviour.

    Where and how is your data stored relative to your front end database, and how many records are in the underlying tables, and how are you querying them ?

    (Questions questions )
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    fishhead is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    76
    The backend data is on our server and each workstation has the application that pulls the data over the network.
    A couple of the underlying tables have a max of 20k records in them but most don't have anywhere near that many.

    They queries are run on the front end applications via macros i've set up.

    hope that clarifies things a little

  4. #4
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,310
    I assume the server is local and not on a WAN somewhere the other side of the world ?

    So probably down to the queries or how your data is organised.
    Do the queries include aggregate functions? (Totals max mins etc)?
    Are you joining tables on fields without indexes or primary keys set?

    20k records is nothing in terms of numbers to stress the database.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    fishhead is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2015
    Posts
    76
    yes the server is local (100' cable away)

    not sure what you mean by aggregate functions - some of the queries may have totals in them. I would have to check.
    All the joining tables have primary keys - this I have checked.

    - possibly the compacting of the backend dbases is a problem. Is there a way in Access to schedule an automatic compacting say at 3am each evening?
    just a thought.

  6. #6
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,310
    Do you have a persistent connection open to your backend from the front end?
    This can help with speed issues as the constant "reconnecting" can make this appear slow.

    Have a read here http://www.fmsinc.com/microsoftacces...ddatabase.html for some additional tips.

    To your question - there is no simple way to do that, it would involve a batch file and some ninja moves to ensure nobody was accidently left in the database overnight.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,082
    also look at this link about improving performance

    https://www.access-programmers.co.uk...d.php?t=291269

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

Similar Threads

  1. typical query on grouping
    By johnbest in forum Access
    Replies: 2
    Last Post: 03-13-2012, 10:36 PM

Posting Permissions

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