none
Excel Power Query (aka Get and Transform) 10x slower after Excel (365) update RRS feed

  • Question

  • I have been using Excel Data Get and Transform (i.e. Power Query) for some time to query data from our data warehouse Excel BI server using the SSAS wizard.

    A number of such queries that pulled more than 500,000 records (up to 3 or 4 million rows) began to fail to complete right around August 1 2018.

    After building a minimal test query (QueryTest) tweaked to just barely return 450k records in about an hour in "failure mode" we have one primary hypothesis: Office/Excel Version updates seem to have be the cause.

    If we use earlier versions of MS Office 365 (e.g. circa v 1705 build 8201.2200 click to run) then QueryTest returns all 450k records in about 6 minutes. If we use the current, updated versions of Excel (v 1803 build 9126.2295), the test runs sometimes don't finish before they time out in an hour, or take substantially longer than 1/2 hour. (See table of nominal 6 minute runs.)

    In the fast, earlier versions of Excel, the Data tab of the Excel ribbon provides separate menu items for "Show Queries" and "Show Connections" and each command opens a different panel.

    In the "broken," later versions there is only one item for "Show Queries and Connections" and the panel that opens has a Query and Connection tab incorporated.

    I have run many, many tests. The same results occur regardless of which laptop runs the query, regardless of which user account is used, regardless of whether the laptop is connected directly to the corporate LAN/WAN or via VPN and, generally, regardless of geographic location. In some locations QueryTest does less badly, presumably because of better network performance or latency.

    We also use Excel on Citrix where the problem does not exist, but it appears that our Citrix version of Excel is one of the "older" versions with the split Query and Connections menus.

    We have tested with TrendMicro (our antivirus program) removed without any change in the relative performance difference.

    My guess is that security and or access protocols have changed in some way resulting in slower processing or packaging of results back from the server, but that is not even a scientific WAG.

    So, does anybody know what may have happened in the updates that could be causing this, and more importantly, how to configure server and/client query to regain the speed?

    Test Results:

    Test Name       Records Seconds Records/Sec Complete
    Old version 1    454,781    140  3,248.44   yes
    Old version 2    454,781    137  3,319.57   yes
    Old version 3    454,781    141  3,225.40   yes
    New version 1    101,284    360  281.34     No
    New version 2    94,836     360  263.43     No
    New version 3    107,236    360  297.88     No
    Thursday, November 15, 2018 3:14 PM

Answers