locked
Slow performance linking tables to back-end SQL Server RRS feed

  • Question

  • I've seen several performance discussions in these forums, but would appreciate some specifics about what is going on.

    I have an Access 2010 front-end with linked tables to a SQL Server 2008 R2 database (running on the same system as the front-end).  The linked tables uses a (DSN-less) Connect property of  "ODBC;Description=Backend Table;DRIVER=SQL Server;SERVER=(local);DATABASE=TestDB;Trusted_Conection=yes;" and a SourceTableName property set to the name of the table in the back-end.

    When I open the table for the first time from the navigation pane, it takes 18 or so seconds to open.  It might be a little faster for SQL Server authenticated tables, but only by a couple seconds.  There are only four columns and 4 rows in this table.  Is this the expected performance?

    Once the first table is opened, the other linked tables open quickly.

    If I restart the front-end, open a linked table, and then open a form referencing a different linked table, I will incur a 18 second wait for the table and then another 18 seconds for the form.  This performance is unacceptable especially when we add subforms or combo boxes referencing yet other linked tables to the form.

    How can I work around these delays when opening a form?  It seems like Access isn't recognizing that it already has a connection.  I've avoided joins, disabled sub data sheets, the tables all have a primary key.

     

    Monday, April 25, 2011 6:28 AM

Answers

  • « Driver=SQL Server » is not a valid specification for a valid ODBC SQL-Server driver; the correct syntax would be « Driver={SQL Server}; »; with the two {}.  Also, even if you have wrote it correctly, this is the oldest ODBC driver that you can find.  I would suggest to use the latest ODBC driver available on your machine; which would be the SQL Server Native Client 10.0 ODBC Driver: « Driver={SQL Server Native Client 10.0}; »

    Furthermore, with (local) as the server address, there is no specification if either the Shared Memory, the Named Pipe or the TCP/IP protocols must be used.  I would suggest to verify to either specify the protocol or verify in the SQL-Server Configuration utility that all these three protocols are enabled.

    Your 20 seconds or so looks like a time out; so my guess is that Access first try with an invalid (disabled) protocol and then switch to another to make the connection.  The opening of the connection and the display of the first table should be nearly instantaneous.

    • Marked as answer by BAH Humbug Tuesday, April 26, 2011 5:42 AM
    Monday, April 25, 2011 6:31 PM

All replies

  • At the end of the day such type of delays are not typical or normal. Keep in mind that moving from Access to sql server will often result in slowdowns.

     

    You also do not mention if this application is multi-user or if more than one person has the application open at a given time? In other words, do these delays occur with only one user in the application, or many users?

     

    Also, I would test a form on a table with one record, or perhaps opening a form with a where clause to restrict the form open to ONE record. In other words, it makes little sense to have this great sql server, and then throw up an Access form attached to the whole table.

     

    I mean, can you imagine an instant teller machine download every account then THEN asking you for what account? Or how about a search engine downloading the whole internet into your web browser and THEN asking what you want to search for? Or then forcing you to use ctrl-f? In fact the same deadly ctrl-f is available in Access forms.

     

    So, the suggestion here is to ask the user what they want FIRST and THEN open up a form to the one record as opposed to having the form pull down huge numbers of records and the asking the user to search that data.

     

    As noted, you first want to determine if the delays are due to too many records being loaded into the form, or just that the form loads are slow regardless of table sizes.

     

    Also, you title is confusing since you mention the term "linking" and not linked tables. I would assume the linking process is a one time thing, done once and from that point on the bound forms would and should load near instant as long as  you restrict records loaded into those forms.

     

    I suppose the one last issue would be to ask what type of connection here? Is this a local network or some type of connection over the internet (over the internet is 100 TIMES slower then your typical office network and thus a 3 second delay can turn into a 300 second delay).

     

    Albert D. Kallal  (Access MVP)

    Edmonton, Alberta Canada

    Monday, April 25, 2011 6:04 PM
  • « Driver=SQL Server » is not a valid specification for a valid ODBC SQL-Server driver; the correct syntax would be « Driver={SQL Server}; »; with the two {}.  Also, even if you have wrote it correctly, this is the oldest ODBC driver that you can find.  I would suggest to use the latest ODBC driver available on your machine; which would be the SQL Server Native Client 10.0 ODBC Driver: « Driver={SQL Server Native Client 10.0}; »

    Furthermore, with (local) as the server address, there is no specification if either the Shared Memory, the Named Pipe or the TCP/IP protocols must be used.  I would suggest to verify to either specify the protocol or verify in the SQL-Server Configuration utility that all these three protocols are enabled.

    Your 20 seconds or so looks like a time out; so my guess is that Access first try with an invalid (disabled) protocol and then switch to another to make the connection.  The opening of the connection and the display of the first table should be nearly instantaneous.

    • Marked as answer by BAH Humbug Tuesday, April 26, 2011 5:42 AM
    Monday, April 25, 2011 6:31 PM
  • Thanks Sylvain!  Enabling the Named Pipes and TCP/IP protocols solved the problem.  I'll look into using the native client driver.
    Tuesday, April 26, 2011 5:44 AM
  • Thank you Sylvain this fixed my problem after 8 hours of searching.
    Thursday, January 17, 2013 10:48 PM
  • I vaguely remembered enabling Named Pipes and TCP/IP protocols a few years back. Got a new computer and was racking my brain where to do that. You provided the answer.....Thank you so much!
    Sunday, February 16, 2020 7:23 PM