3

I have an Excel VBA application that connects to a SQL Server 2008 R2 database through ADODB.

When I'm on the network at work, the connection is almost instantaneous. When I am at home working with a local copy of the database, the connection takes about 5 seconds. At first I thought the performance hit had to do with the queries, and even posted a question on Stackoverflow with that incorrect assumption: https://stackoverflow.com/q/10467876/138938

The connection is just as slow locally whether I specify "localhost" or a ".". It seems like my code is having a hard time resolving where exactly my localhost is. Is there something I should do with my hosts file or some other setting that I can modify to speed up my connection on my local machine?

What other thing should I look at to troubleshoot?

Thanks!

7
  • What protocols are enabled under SQL Server Configuration Manager? Try disabling Named Pipes and TCP/IP so that SQL Server must use Shared Memory. While you are local, are you also connected to your VPN? Is it possible that some kind of AD resolution is going on? May 21, 2012 at 16:43
  • Shared Memory, TCP/IP, Named Pipes and VIA were all enabled. I disabled all but Shared Memory without any luck. Do I need to bounce the server for it to take effect? I'm not connected to a VPN, but this machine has had issues. A full rebuild may be in order. May 21, 2012 at 16:55
  • Yes you need to restart the service for those changes to take effect. May 21, 2012 at 16:59
  • Also is there any way to tell Excel to use SQL Native client instead of the slow and ancient ADODB? I don't use Excel VBA so I don't know if it's possible, but if it is you should explore that and test the difference... May 21, 2012 at 17:05
  • I'm at work now, so can't test. I'll get back on this tonight. Thanks! May 21, 2012 at 19:13

3 Answers 3

5

Try enabling TCP/IP on your local instance:

  • Open SQL Server Configuration Manager (mmc.exe -> [Ctl] + [M] -> Add "SQL Server Configuration Manager" snap-in)
  • Expand "SQL Server Network Configuration"
  • Click on "Protocols for MSSQLSERVER" (or whatever your local instance name is)
  • Set "TCP/IP" to Enabled

I just ran into this problem tonight. Contrary to @Aaron Bertrand's seemingly intuitive suggestion in the comments, having TCP/IP disabled was actually causing my delay. TCP/IP is disabled by default, so this is likely a common source of frustration for others, too.

I ran ProcMon.exe while connecting to a remote server and a local instance of SQL Server. I was using the Jet/ACE ODBC TraceSQLMode to debug the delay. TraceSQLMode writes to a text file every time an ODBC command is issued.

When connected to the remote server, seven commands were written to the text file in under one second total. With the local instance, there were almost exactly two seconds between each ODBC call.

I assume that the Jet/ACE ODBC driver was attempting to connect to the localhost via TCP/IP initially with every ODBC call. Since TCP/IP was disabled, this likely failed after a two-second timeout, at which point the Jet/ACE ODBC connection appears to have fallen back on Shared Memory access.

Enabling TCP/IP eliminated the two-second timeout delay, resulting in a massive performance increase (especially when making multiple calls to the database).

1
  • I believe in mssql docker Linux tcp/ip is enabled by default?
    – Angel
    Mar 8 at 12:01
3

If this is specific to that one database (you don't mention whether you can connect to other databases like master on localhost without issues), check if its Auto Close setting is True. If at work you're connecting to an instance of the DB that is constantly connected to by others as well, then the database will remain open and ready for use and respond faster. However, at home you will be the only user, and if you're not connected Auto Close will free resources relating to the database and close it, reducing its responsiveness next time you use it. The effect of this could be quite pronounced.

This option is False by default on most editions of SQL Server, but if your database was originally created using MSDE or SQL Express then those have it set to True by default.

2
  • Thanks for the suggestion. I'll take a look at that tonight or sometime this weekend. May 25, 2012 at 18:11
  • I just checked -- Auto Close was set to False. May 29, 2012 at 2:43
0

I notice that this question was posted nine years ago, so the figures in my answer (which are current as of 2021) won't be relevant to that time. But I suspect your home machine does not have enough RAM to host your own server.

I had that problem, locally hosting MSSQL server 2012 with 8GB of RAM on a Windows 10 machine, with Core i7 10th Gen. Sometimes there were no problems, but mostly I was getting very slow query response times, and even timeouts.

This was my development machine, and I noticed that all the RAM was being consumed, just from development alone, without any database connections. So I suspected that there was not enough memory for querying the database as well.

I increased the RAM to 32GB (but 16GB would have sufficed), and problems solved.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.