cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

SharePoint Online slow refresh

As the title says.

I have seen this issue posted several times but never found anything that may help.

My reports pull from both a SQL db and several SharePoint lists. The SQL dbs will import 100,000s of records - typically faster than the time it takes for the SharePoint list, which pulls in 25,000 rows. I can see on the Data Refresh the size that has been downloaded for the SharePoint list, and it seems to be pulling in the data slower than 500kb/s - what is this?

 

On my Excel version of this sheet, I can use the

let
    Source = Table.Combine
    ({
        OData.Feed("mysite/_api/web/lists/GetByTitle('mylist')/Items?$filter=
        Date ge '" & DateSplit(1) & "' and Date le '" & DateSplit(2) & "'"),
        OData.Feed("mysite/_api/web/lists/GetByTitle('mylist')/Items?$filter=
        Date ge '" & DateSplit(3) & "' and Date le '" & DateSplit(4) & "'"),
        OData.Feed("mysite/_api/web/lists/GetByTitle('mylist')/Items?$filter=
        Date ge '" & DateSplit(5) & "' and Date le '" & DateSplit(6) & "'")
    }),

Where the user will pick a date and it will fill a table with the dates for the month. It essentially splits the query into three, due to the 5000 limit threshold, but it is faster than it would otherwise be.

For BI, where the expectation is that the user can pick any date (ranges), I will need to have the full data imported preferably.

There is nothing demanding in my query,

let
    Source = SharePoint.Tables("mysite", [ApiVersion = 15]),
    #"GUID" = Source{[Id="myGUID"]}[Items],

So what gives with the SQL db being so much faster than SharePoint Online? Is it because SharePoint Online is given less resources due to it not being premium?

24 REPLIES 24
Anonymous
Not applicable

There seems to be no Power Query solution with the standard SharePoint Online connector.

If you can filter and bring in less than 5000 items, you can do a Odata feed which pulls in much much faster.

As an example, I do

 

let
    Source = OData.Feed("<SharePoint Site>/_api/web/lists/GetByTitle('<List>')/Items?$filter="
            &"Date eq '" & DateSearch() & "'")
in
    Source

Where

DateSearch()

is a custom function to get the date.

This might be enough for you.

Hi DCM,

 

Thanks for this solution, but for me this is not working. It is much faster though, but I miss the necessary columns.

 

regards, Jordy

Same issue but with a .TSV stored in a sharepoint 365 documents folder.

 

Takes ages to even start drawing down data.

We've been having identical issues for a long time - if there is a way to diagnose performance issues/bottlenecks, I'd love to see where things are slowing down.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.