Results 1 to 13 of 13
  1. #1
    devlin7 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    7

    Weird Speed issue

    Hi,



    One of our developers reported that his Access database was slow and asked us to exclude the file extensions .accdb and mdb from the AV scanning. I went a step furth and turned AV off to prove that AV wasn't causing the issue.

    We have clients using Windows 7 [32bit] with Access 2010 accessing a database [.accdb] via a mapped drive to a Windows 2008R2 share. The .accdb file has SQL links to two other servers. The database takes 25 seconds to open.

    If I copy the .accdb file to my c drive the database open in less than a second. If I copy the .accdb file to another mapped drive that is host on a server 2003 server the database opens in less than a second.
    I have tried turning off SMB2 on the Server 2008R2 server, made no difference.

    Does anyone have any idea why the database is slow to open if the .accdb file is accessed via a mapped drive to a Server 2008R2 server?


    Thanks

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    No reverse lookup to the client name on the 2008 server? Just a guess..

  3. #3
    devlin7 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    7
    Thanks for the quick reply reverse lookup is definitely working. I am sure that it is server 2008r2 related

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    W7 has some goofy (I call them goofy because I do not have them memorized) settings to maintain open connections over the network. When you say link, I am thinking via a path. Not sure what is going on there with SQL but, with the accdb file you could open the mapped drive to refresh the server connection and then open the accdb file, timing how long it takes for the accdb to open.

    What I am getting at is the accdb is probably trying to establish connections before it shows itself as fully open. If you can, first, use windows explorer to open a connection, so to speak, you may reduce the startup time for the accdb.

    If you observe a difference, then you can go after settings to maintain the connections to various network resources. Just not sure how you would stimulate the SQL connection. I am guessing it is using ODBC. Maybe you could have two accdb files that are identical except for their names. Open one and after it establishes its links, open the second. I believe something like this would illustrate the client does not have a persistent connection to the resources.

  5. #5
    devlin7 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    7
    Access to the .accbd file is opened via explorer, so the connection to the share [mapped drive] is already open.

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    No shortcuts on the desktop? plus, this is just to the FE and not to linked tables and or connections. Hence, my suggestion about opening two identical files sequentially.

    I mention this only because I have observed W7 take its sweet time to "wake up" connecting to networks. Another one is that sometimes installers choose to not index drives because they are using solid state and do not consider that network drives should be indexed because odds are they are on an array of platters.

  7. #7
    devlin7 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    7
    I installed access 2010 on a server 2008R2 box and accessed the .accdb file locally and it opened like a rocket. I then accessed the .accdb via an UNC share to the file and print server [2008r2), the database once again opened like a rocket.

    Looks like a Windows 7 thing, ruled out autotuning, firewall, AV, Remote differential compression, and LLMNR protocol.
    I know it is not the database or where it is hosted, any suggestions?

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I had to Google LLMNR... I have been trying to remember the different things I have tried with W7. I don't think the IPv6 is part of the factor. This should just be how packets are handled/managed. Maybe relevant but I am still stuck on the other stuff I mentioned.

    Maybe looking at how W7 manages the ODBC connection to SQL, if that is how you are doing it, and where the code in the DB file is trying to create the connection to hold it in memory. Somewhere in the accdb file there is a request to connect to the SQL server. Distinguishing where this is may help to troubleshoot. Is it happening at startup or is it when a user clicks a button, well after startup? Maybe there are (W7) settings associated to the ODBC driver/connection that can be looked at.

    I still don't know if there is a front end (client) accdb file connecting to a backend (Host) accdb file and, in turn, the back end file is connecting to SQL server. Maybe switching to a UNC shortcut on the desktop is worth a shot. As I strain to remember, I keep thinkin UNC vs. mapped did not have anything to do with my previous experience with this "connection symptom". That is why I did not mention UNC previously. So I am thinking maybe power settings, and some sort of "stay connected" settings on W7.

  9. #9
    devlin7 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    7
    Hi Again,

    Spent quite a bit of time on this today. When I compare the same file being accessed on A) Windows 2008R2 and B) Server 2003 then opening time appears to be 20 seconds slower. The .accdb database just seems to contain SQL links to a backend SQL server.
    Using process monitor the process looks the same on the Windows 2003 box to the Windows 2008R2 box BUT the downloading of the .accdb seems to take 15-20 seconds longer on 2008R2. Not sure if this is a file transfer issue or not, nobody has long delays accessing personal files of the same server.

  10. #10
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I hear what you are saying about personal files on the server not having the same stall issues. I am not running 2008 so I do not have an exact comparison. All I can say is, I did have similar symptoms and used the shotgun approach. I do not have issues today. Maybe this link is relevant.

    http://support.microsoft.com/kb/2976...ItMyselfAlways

  11. #11
    devlin7 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    7
    Copied the .accdb file to a real 2008R2 server [not virtualised] and it ran normally. Also tried hosting on a Real Windows 7 pc, normal access. Will try a virtualised Windows 7 host to determine if it is something to do with virtualisation. Still investigating

  12. #12
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Maybe later you can tell me how reverse lookup PTR works with virtualization.

  13. #13
    devlin7 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    7
    Thought I would close this issue. Turns out it was an issue with our Virtual enviroment specifically the way the SAN was attached to the ESX environment.

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

Similar Threads

  1. Another Weird Problem
    By ksmith in forum Access
    Replies: 16
    Last Post: 07-24-2012, 10:45 AM
  2. Weird Error
    By KevinMCB in forum Access
    Replies: 1
    Last Post: 02-16-2011, 02:23 PM
  3. Need for Speed
    By OceanaPolynom in forum Programming
    Replies: 2
    Last Post: 07-13-2010, 08:30 PM
  4. Dsum causing speed issue
    By ethoemmes in forum Queries
    Replies: 0
    Last Post: 07-06-2010, 05:22 AM
  5. Speed issue
    By hey23 in forum Queries
    Replies: 1
    Last Post: 06-24-2009, 09:16 AM

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