Maxime
Maxime

Reputation: 91

Extremely slow Excel ODBC Connection

I built a dashboard in Excel that is connected to an SQL Server. There are about 20 queries made with microsoft query, using SQL parameters to extract the least possible data. Queries are extracting between 30 lines of data up to 4,000 for only one query.

The dashboard used to be very fast. The updates were running in 2-3 seconds max for all queries. Now there is inconsistency and it can take up to 5 minutes to update the same amount of data with about the same queries. There were some minor changes to some queries, but reversing the changes does not get the workbook back to its normal speed. Even deleting all the queries except one still left the workbook extremely slow. Recreating it from scratch is not an option.

I realized that the workbook is fast if I open a connection with microsoft query in a new workbook. It looks like Microsoft Query has to be opened and connected to the database on a an Excel workbook opening to have a decent speed. This trick did not work each time.

What can I do to get the speed back to normal considering that each sql query is executing in less than a quarter of second in SQL Management Studio?

I have Excel 2016 on Windows 10.

I searched on web without finding any start of a solution.

Upvotes: 4

Views: 23489

Answers (1)

Maxime
Maxime

Reputation: 91

Finally found the answer after days of research.

It has nothing to do with the queries themselves. It is all about the connection string to the server and named pipes.

Place np: before server name such as SERVER=np:3DSERVER\3DEXPRESS

An enormous thanks to 3deers: http://www.3deers.com/blog/sql-connections-slow-windows-8-excel/.

Upvotes: 5

Related Questions