locked
Slow insert into local table RRS feed

  • Question

  • I have an 32 bit Access 2010 database.  Most tables are linked to SQL server.  This is the second time, at one customer's site only, where inserting records into a local table has become very slow.  In April I moved a local table to SQL to fix that insert problem.  I would like to figure out what is going on instead of doing a modification for the new issue.  I'm trying to insert 52 records into a local table which recently started taking 30 - 40 seconds.  It does not matter which computer the program is run from.  The accde is installed on each computer.  I've also tested if from their SQL server.

    My program is an accde, using the MS Access 2010 runtime.  The table has a primary key which is an AutoNumber.  There are only 22 fields in the table.

    I've tried running as Admin, which did not help.

    I found a boolean field did not have a default.  I fixed that, did a compact & repair and tested.  It did not help.

    I've taken the data from the customer's site and tested it with my full version of Access.  The insert is normal.

    Could this be a problem with

    Jet, the runtime, Office 365 or something else?

    Thursday, June 18, 2020 12:44 AM

Answers

  • The security was not the issue.  It was recent changes to the anti virus software Bit Defender.  

    I finally got an IT person to help me.  I pointed out when I copied the program to another drive, it was fine.  When he turned off the anti virus software, adding data to a local table worked fine.  

    Thank you Daniel and Tom for your input.

    • Marked as answer by JE_VB Friday, June 19, 2020 10:58 PM
    Friday, June 19, 2020 10:58 PM

All replies

  • Complete stab in  the dark here, but I have seen in some discussions that setting the access process' cpu affinity can help, you may wish to try that.

    Task Manager->Details
    Find the msaccess.exe process and right-clcik
    Select Set Affinity
    Some state only using CPU 0 trhough 3 works, some say only using CPU 0 is the solution.

    You could try, at the startup of your db (autoexec macro):

    dbEngine.SetOption "MaxBuffers", 65535


    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Thursday, June 18, 2020 1:22 AM
  • For booleans, it's not as much a matter of default, as of NOT NULL. Nullable bit fields are poorly supported in Access. Poorly supported by common sense as well, if I may say so. Some exceptions apply, of course.

    I don't think Daniel's suggestion of running on a single CPU is going to make the difference, given that most likely this entire insert would already run on 1 thread anyway.

    As is Daniel I am grasping at straws. Here is what I might do:
    First I make sure client agrees this is billable time. We set a "not to exceed" bucket of time of say 6 hours. We start documenting all the steps and tests we perform. I would need full RDP access to run a session on one of their machines. Take sql server out of the equation by copying those 52 records to a temp local table, then run the insert. Still slow? Then we can exclude the network and ODBC drivers and SQL Server from consideration.

    My guess is that is fast. If so, the next step would be to use the latest ODBC drivers (certainly NOT the old "SQL Server" driver, but the latest "ODBC Driver 17 for SQL Server"), and also make sure SQL Server is on the latest service pack and/or CU (select @@version). This takes "MSFT already fixed it" out of the equation.
    Then I would open the linked table. Is that fast?
    And doing a "select * from theTable" in SSMS; is that fast?

    These would be my initial steps. Next steps would depend on the results obtained so far.

    -Tom. Microsoft Access MVP

    Thursday, June 18, 2020 2:13 AM
  • Daniel,

    Thank you for your reply.  CPU 0 - CPU 3 were all checked.  I unchecked 1-3 and tested.  It did not make a difference.  Next I added dbEngine.SetOption "MaxBuffers", 65535 and also changed the boolean in the local table to have a validation rule of Is Not Null.  So far nothing has helped.

    Jodi

    Thursday, June 18, 2020 5:52 PM
  • What odbc driver are you using?  As Tom mentioned, be sure you're using the latest one.

    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Thursday, June 18, 2020 8:16 PM
  • The customer's server seems to be down so I can't check the ODBC driver. 

    My program lives in the ProgramData folder. Before their server went down I decided to copy a test version of the accde to another drive.  It worked great.  The 51 records inserted in under 5 seconds.  On the C drive I add the test copy to a different folder.  It was still slow.  I right clicked on the accde to go to Security.  I noticed Users group did not have full control.  I changed that.  When I tested the program again, it was fast.  I went back to ProgramData to look at the original accde.  It said the Users group had full control.  I tested again and it worked fine.

    On the customer's workstation I tried to do the same, but struggled with having enough rights to get connected and then do what I needed.  IT was trying to give me access at the same time.  It appeared the User group had full rights, but then we lost our connection to the SQL server so I couldn't test if the problem has been resolved.

    I think the issue was a security problem, but I'm waiting for confirmation.

    Thursday, June 18, 2020 9:42 PM
  • The security was not the issue.  It was recent changes to the anti virus software Bit Defender.  

    I finally got an IT person to help me.  I pointed out when I copied the program to another drive, it was fine.  When he turned off the anti virus software, adding data to a local table worked fine.  

    Thank you Daniel and Tom for your input.

    • Marked as answer by JE_VB Friday, June 19, 2020 10:58 PM
    Friday, June 19, 2020 10:58 PM
  • Thank you for posting back with this information.  I'm sure it will prove useful to others in the future when they need to troubleshoot similar issues.

    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Saturday, June 20, 2020 2:53 PM