Forum Discussion

Veles's avatar
Veles
Advocate V
7 years ago

Incredibly poor SharePoint performance

I'm getting some incredibly poor performance with queries that reference SharePoint, both SP List and SP Folder queries.   The one I'm working on at the moment mainly has SP Folder queries.   One...
Need Help
Veles's avatar
Veles
Advocate V
7 years ago

Hi,

 

I've tried using Table.Buffer before but never had any success with it improving the query performance.

 

The below is a relatively simple query I have that is just pulling data from one file (albeit quite a large file but still not massive). This one still takes a significantly long time to evaluate given that it is essentially, open one file, promote headers, done.

 

let
    Source = Table.Buffer(FinanceSPConnection),
    #"Q3F-Budget20 Data xlsx_XXXX" = Source{[Name="Q3F-Budget20 Data.xlsx",#"Folder Path"="XXXX"]}[Content],
    #"Imported Excel" = Excel.Workbook(#"Q3F-Budget20 Data xlsx_XXXX"),
    Sheet1_Sheet = #"Imported Excel"{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"GL Account", type text}, {"Scenario", type text}, {"YearPeriod", Int64.Type}, {"WBS Ending", type text}, {"Currency", type text}, {"Period Value", type number}, {"Source", type text}, {"Ref", type text}, {"Sage COA", type text}})
in
    #"Changed Type"
Veles's avatar
Veles
Advocate V
7 years ago

I think I've narrowed down our issue.

 

One of the document libraries contains scans of compliance paperwork - tens of thousands of files. As the initial step on any query to SharePoint is to download the list of all the files this step is taking a very long time to do.

 

The query has just finished and there are 156k files which isn't a big dataset to download. However, I find the performance of extracting SharePoint lists to be incredibly poor so I think this is the real issue.

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    hi, Veles

    This usually doesn't last, It could due to the network or the server,

    It also could due to the hardware for running Power BI Desktop.

    So you could try it again in different working environments.

     

    Best Regards,

    Lin