Named Pipes. That’s the Jeopardy answer to a question that took 3Deers a month or so to answer regarding a sever slowdown in the queries from Microsoft Excel to a SQL database. Named (freaking) Pipes.
For folks that use Excel to access SQL and do great Business Intelligence Reports (like we do at 3Deers), you know that you need to have an accurate “connection string”. The connection string simply tells Excel the name, location, driver and other information to connect to SQL. We won’t go into how to build a report this way, but for developers that use this technique, a SQL connection string will look something like this:
DRIVER=SQL Server; SERVER=3DSERVER\3DEXPRESS; UID=3Deers; PWD=password; APP=Microsoft Office 2010; WSID=3Deers; DATABASE=3DData
We have used this on several clients for years to build efficient reports that look great and run very quickly. Or at least they did until recently.
One client began upgrading workstations to Windows 8 and had noticed a very peculiar problem. While reports on their older Windows 7 and XP workstations could do a query in under a second, the same report on Windows 8 was taking 43 seconds. Yes, we timed the dang thing!
Now, silly us, we assumed that the Windows 8 machines and new Excel would be faster. All the reports worked, but each query was taking a long time. With most queries taking under a second, we often had reports with multiple queries to bring in a wealth of information in one convenient dashboard. If a report had a half a dozen SQL queries and pivot tables that took two to three seconds before, now they were taking 4 to 8 minutes… Ahhh!
We have been searching SQL message boards, google and even spoke to Microsoft help desk (re-install office??? Really?). Nobody had an answer. We tried different SQL drivers. With an ODBC driver, we knocked the time to 22 seconds for a query, but that still was not even close to acceptable. We saw suggestions about using an IP address instead of a server name. No good. Changes to configurations on network drives, nothing.
Finally… finally… while researching an unrelated issue we saw an article that discussed how to access SQL related to tcp or np protocol. Ah-ha! Something different to check.
SQL allows different methods for workstations to connect. Historically, we have connected through standard tcp (not by design, that’s just what happened). Now with Windows 8, we needed to connect through “named pipes”. SQL was allowing the new stations to connect, but first timing out on other protocols until it got to the named pipes, which was 3rd in the priority list. Since other applications run on the server, we were reluctant to change the configuration but found we could force the connection to do named pipes first.
One minor change. We only needed to change SERVER=3DSERVER\3DEXPRESS to become SERVER=np:3DSERVER\3DEXPRESS. That’s it, three characters.
“SQL Connections for $500, Alex”. The answer is “Named Pipes”. Buzz… “What protocol must be prioritized when connecting Excel in Windows 8 to a SQL Server?”.
