Results 1 to 6 of 6
  1. #1
    altemir is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    31

    Slow INSERT on a Linked SQL Table

    I have a MS Access 2010 front-end containing linked tables from an SQL Server 2008 database. Performing INSERTS on one of the linked tables is horrendously slow despite the fact that I'm adding just one or two records to the linked table from a local table. Note that I'm using the DoCmd.RunSQL method to issue the INSERT statement and the users aren't getting any server errors -- just a never-ending hourglass.

    The target linked table only contains <2000 records, although it does get a lot of transactions as I have about 20 users routinely SELECTING, UPDATING, and INSERTING on the linked table.



    Is there something inherently wrong I'm doing that is causing this INSERT to run so slow?

  2. #2
    Dal Jeanis is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    1) Are updates slow also, or only inserts?
    2) Have you tested from the frontend on the same machine as the backend - or someplace topologically close - to eliminate network effects?
    3) Do the users have a persistent link to the backend, or do they have to establish the link when the update is occurring?
    4) You said "ONE of the linked tables". is there a time when all your users are off when you can check whether there is some process or dead user that is holding on to that table?
    5) Speaking of which, check all your queries and code modules for update, inserts, and reads to that particular table and see if any of them are not letting go. Remember the mantra of the faithful programmer, "I shall explicitly destroy that which I create".

  3. #3
    altemir is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    31
    Thanks! I'll check all those possible issues. Regarding item #4, what specific words should I use to ask my Admin to do to check whether or not a user is "holding on" to the table?

  4. #4
    altemir is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    31
    Here's another clue ... I just got the following error when attempting an INSERT:

    ODBC -- insert on a linked table 'tblMyTable' failed
    [Microsoft][ODBC SQL Server Driver][SQL Server] Transaction (Process ID 119) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction (#1205)

    My question is how can you get a deadlock error on an insert? Doesn't deadlocking involve two process competing for the same record? An insert is creating a NEW record. However, there is an INSERT trigger on this table that might be causing an issue, but I would expect the trigger to run AFTER the insert statement is completed.

  5. #5
    Dal Jeanis is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    I'm not that familiar with the ins and outs of SQL Server, so I'd use the nontechnical terminology when talking to a technical person. If there are seven flavors of database locks in that particular application, you want her to check them all, plus any other similar concepts she can come up with on her own.

    On the other hand, the quick nontechnical test would be something like this - move or rename the database file. If the system won't let you move it, then something has a hold on it. So then you try to find out what.

    That would work in Access, but there are databases where not being able to move it wouldn't tell you anything about whether there was an open link/line/query to the database itself.


    NEXT SUGGESTION - I'm not sure what the SQL Server terminology is, but have you Compact-and-Repaired the backend database? In mainframe DB2, what I'm talking about is called a Reorg. Basically, the slow table is backed up and then laid back down with a non-fragmented version of itself. In a mainframe, I'd also check the options that determine how much internal free space is retained before causing a split, and what storage packs the database resided on. Make sure that there's room to add records easily without splits. That kind of thing.

    This suggestion was written before you posted comment about the database lock. It's not applicable, obviously.

  6. #6
    Dal Jeanis is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    The next thing I'd try is copying the table somewhere else, cloning the front end, then pointing the clone to the copy and see whether there's an issue. If not, then add the trigger and see if that causes it.

    But first, I'd try what the pros say on these threads -

    http://social.msdn.microsoft.com/For...nother-process
    http://www.codinghorror.com/blog/200...eadlocked.html

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

Similar Threads

  1. Insert INTO MS SQL Linked table too slow
    By green78 in forum SQL Server
    Replies: 5
    Last Post: 05-28-2015, 09:33 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