Results 1 to 6 of 6
  1. #1
    green78 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2013
    Posts
    2

    Insert INTO MS SQL Linked table too slow

    Hi,

    In short I'm having the following situation:


    MS-Access 2010, 32-bit having linked tables to MS-SQL Server 2008 R2 64-bit. The MS-SQL server is on a remote location. The MDB file is stored locally on my PC. When connecting via ODBC from MS-Access to the SQL server there is a latency of about 50 ms (because of the distance) and this is causing me a big pain.

    I have to perform an insert of data from a local MS-Access table INTO a linked table on the SQL server. For the purpose in MS-Access I have structured such insert statement:

    Code:
    INSERT INTO SQLTable.... SELECT... from [MSaccesstable]
    I run a trace on the MS-SQL server and it shows that MS-Access is passing the following insert statements line by line:
    Code:
    exec sp_executesql N'INSERT INTO  dbo.SQLTAble (Field1, Field2....)  VALUES (@P1, @P2 .....)
    exec sp_executesql N'INSERT INTO  dbo.SQLTAble (Field1, Field2....)  VALUES (@P1, @P2 .....)
    etc.... for all 1000 inserts
    Every line of the above code is executed as a separate transaction and thus having a latency of 50 ms - every insert is happening with 50 ms delay after the previous. This way for 1000 inserts I have to wait about 5 minutes.

    My question is: Is there a way to force MS Access to pass these INSERTs into batch opeation thus avoiding the wait time after every single INSERT?

    Thank you!

  2. #2
    altemir is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    31
    I am experiencing exactly the same problem, but I don't think it's due to a lack of a bulk insert as the number of records I'm adding is small. What I have noticed is that I do encounter deadlocks ever so often. Do you think that could be an issue for you?

  3. #3
    green78 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2013
    Posts
    2
    Quote Originally Posted by altemir View Post
    I am experiencing exactly the same problem, but I don't think it's due to a lack of a bulk insert as the number of records I'm adding is small. What I have noticed is that I do encounter deadlocks ever so often. Do you think that could be an issue for you?
    Hi altemir,

    Actually I'm pretty sure it is the latency that is slowing down the inserts. I have a replica of the SQL database on a local server which is in the LAN and it is very fast. Also the SQL profiler trace on the remote server shows that each INSERT is executed approximately 50 ms after the previous one which proves that the slowness is caused by the fact that MS-Access sends the first INSERT statement and awaits a response that it is processed successfully and only then passes the next one - and this happens after 50 ms - the exact value of the latency shown in the Windows resource monitor.

    I tried switching on implicit_transactions on the ODBC connection but this didn't help either. I'm running out of ideas how to force MS-Access to execute these inserts at least into batches of 10 - this will speed up 9 times the operation time. I will try a few more tricks but I think there should be a more sophisticated way to do this.

  4. #4
    altemir is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    31
    Did you ever get a solution to this? I have a table trigger that I have isolated as the thing preventing the insert. In fact, it seems to be causing deadlocks, which I'm not sure why since these are new records. I think the fact that the ODBC connection is not performing a bulk insert like you say could also be the source (or at least a contributor) to my problem.

  5. #5
    thesame is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2015
    Posts
    1
    Hi,

    anyone get the solution?

    Please....

  6. #6
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    You could try to use a pass through query in access, so you can use T-SQL to make a bulk insert into SQL server:

    https://msdn.microsoft.com/en-us/library/ms188365.aspx

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

Similar Threads

  1. Replies: 8
    Last Post: 03-20-2013, 11:39 AM
  2. Replies: 3
    Last Post: 06-29-2012, 08:58 AM
  3. linked tables - slow
    By wowiwi in forum Access
    Replies: 5
    Last Post: 10-01-2011, 12:17 PM
  4. Slow linked table
    By Okidoo in forum Programming
    Replies: 0
    Last Post: 05-19-2011, 03:02 PM
  5. Replies: 1
    Last Post: 01-22-2011, 12:23 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
  •  
Other Forums: Microsoft Office Forums