Query Loading Slow in Desktop linking to SharePoint Folder

[Kaycee]

Hi all,

I have forever suffered with this, hoping for a fix.  

When linking to SharePoint to pick up a file I have almost always found the connection to be uncommonly slow.  I often end up linking to the same file locally to build then amend the code to link to SharePoint to publish.  This is not a data quantity issue.  It happens with all files.  

Typically, the initial load of files is only moderately slow, but as soon as you do anything (ie. filter to a file, try to extract data) the load time is ridiculous.  No issues with loading once the file is published and refreshed online. Only an issue working from Desktop.  

Thoughts?  Thanks

[IzaLeo]

Same issue here. 
The file has 700 lines, and it takes to long

[Thejeswar]

@Kaycee , @c_cook28954 ,

I once came across a similar issue where the load of the tables from OneDrive was extremely slow. It was so slow that even after refreshing and leaving for hours, the data was not getting imported.

The Files were small only. They had less than 100 records.

When I checked further, I understood that the issue was not with the file, but with the connection. For importing multiple files, instead of reusing the existing connection in the Power BI file, the files were using different connections for each file. In this case all the files were present in the same folder.

So I would suggest that you check on these lines as well.

May be the connections are impacting one another due to all reading from the same location at the same time.

Regards,

[c_cook28954]

@Thejeswar @Kaycee 

Thanks for the comment @Thejeswar - my situation is exactly the same as yours, I have 19 files all stored in the same location that are being altered in the same ways and then appended after the alterations are complete. 

It does sound inefficient that each table is performing its own search for the file when the search was already completed on the first table, I just need to tell the table to pick a different file from that same folder. 

How did you resolve the issue?

[Thejeswar]

Hi @c_cook28954 ,

In my case, the file was using the OneDrive for Business connector and they had created separate connections for each of those files. So I reused the same connection for all the files.

But since you are using SharePoint Source, instead of connecting separately for each of the 19 files, create one SharePoint Folder Connection and locate your file from that.

Try to reuse the same connection for all the sheets

For your reference,

You will be seeing only one connection that powers all your files

In this case, I have 2 Queries that are using One Sharepoint source. Since you are connection at the sharepoint folder level, you can import the files even if they are in different folders. Just filter the right Folder Path in the connection

[c_cook28954]

@Thejeswar 

Right, I see what you're saying now - I'm fairly confident that I've already done that as I duplicated the table and only edited the file name in the advanced query. 

I've checked and can confirm that I'm only using one Sharepoint connection to store the data so that doesn't seem to be the cause of the speed issue:

[V-lianl-msft]

Hi @Kaycee ,

If the dataset is small, everything is fine. If the dataset is large, we have to keep everything simple. For example, less join or merge which could double the data and slow down the performance. 

You can also try to disable File > Options and settings > Options > CURRENT FILE > Data Load > Allow data preview to download in the background.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kaycee]

Hi Liang, 

Unfortunately the issue is before any editing is done. The query will load the list of files on OneDrive for Business with relative speed, however something as simple as filtering to a single filename takes an extended time to load. 

I've just done some further testing and realised there are thousands of files on this One Drive as it includes an extensive photo Library.  

I tested by first filtering on the required folder (which would have far less unique combinations), and then on the filename.  Performance improved substantially, although still a little slow. 

Would it be a fair assumption that because of the significant number of unique filenames, by filtering on this first performance was significantly degraded by having to check each value individually?  

If so, with now a clearer understanding of the issue - do you have any other recommendations for approach given you have to load the whole SharePoint/OneDrive location to begin?

Thanks
Kaycee

[c_cook28954]

I'm having exactly the same issue as Kaycee - I'm loading files from a Group Sharepoint and it's taking the best part of 30 minutes to load the results. 

The tables aren't large at all - maybe 30 lines for the largest - but the folder is massive (thousands of files stored). I feel like I'm on the same page as Kaycee but I don't see any way to speed up the query. 

Luckily the files aren't consistently updated but I'm thinking about changing the connection to an excel connection and locating the file through the synced folder on my PC in the hopes that this will reduce the processing time. 

It would be great to be able to local the file more efficiently (without loading all the files in order to locate one).