Forum Discussion
GET request to SharePoint folder is slow
I am using the February 2020 release of PBI Desktop. All data loads into my report as Import / Scheduled Refresh. My report sources data from an Excel file that exists in a folder on SharePoint Online (as well as an on-prem SQL Server). My Power Query steps involve at least 1 merge / append between that Excel file and my SQL Server data. As such, my on-prem (enterprise) gateway is configured to allow user's cloud data sources to refresh through my gateway cluster. The refresh of my report both in the service and in PBI Desktop is very slow (~17 min). So, to identify the slowest steps, in PBI Desktop, I have used the Session Diagnostics feature to trace all of the steps in a full refresh. To help differentiate query steps, I have disabled parallel loading of tables. (There doesn't seem to be any performance improvement between enabling or disabling this option.) In sorting on [Exclusive Duration] in the Diagnostics summary table, I have found that my slowest step is a GET request to my SharePoint site. (I don't think the gateway configuration affects this.) It takes about 3 minutes. Is this length of time typical for a GET request to a SharePoint Online site? How can I reduce this time?
1 Reply
Hi Anonymous ,
That really depends on numerous factors to be honest. For example. how big is your excel file? If that is hundreds of megabytes big then that will be slow (as it is downloaded in the background). Another issue can be the throtteling, based on how you connect to the excel file. Are you using the Sharepoint Folder connector? Or the Excel connector pointing to the Excel file? Other issue might be authentication?
Basically, it is very hard to pinpoint the problem exactly based on your description, but I hope some of the above suggestions might point you in the right direction?
