List.Generate performance

[Laura_Dannisoe]

Hello community! 

I have a function which calls an API and then loops until all data are retrieved. Therefore, I am using List.Generate, however I am experiencing it to be extremely slow. The number of rows i approx 300.000 and it can take more than 10 minutes to retreive!

I have come across the List.Buffer or Table.Buffer which apperarently should enhance the performance, but I don't know where to include it in my query. 

Can anyone help? 🙂

@ImkeF maybe?

Query:

let

  Kilde = (EndPoint as text, optional Changedate as text, optional PositionOfResultRecordField)=>

let

    //EndPoint = "GetCustomersByLastChange",

    //PositionOfResultRecordField = 1,

    changedate = if Changedate = null then "" else Changedate,

    positionOfResultRecordField = if PositionOfResultRecordField is null then 1 else PositionOfResultRecordField,

    firstwebcall = SinglePageMB(EndPoint, changedate),

    StartRecord = firstwebcall[results]{0},

    Pagestoskip_ = List.Count(Record.Field(StartRecord,Record.FieldNames(StartRecord){positionOfResultRecordField})),

    NumberOfPages = Number.RoundUp(StartRecord[entriesleft]/Pagestoskip_),

    listGenerate = List.Generate(

        () => [result=StartRecord, resumekey=StartRecord[resumekey],pagestoskip=0,counter = 0],

        each [counter] <= NumberOfPages,

        //each [counter] < 3,

        each [

            result = Resumekey_function(EndPoint,[resumekey], changedate){0},

            counter=[counter]+1,

 //           pagestoskip = [pagestoskip]+Pagestoskip_,

            resumekey = try result[resumekey] otherwise null

        ]

    ),

    #"Konverteret til tabel" = Table.FromList(listGenerate, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in

    #"Konverteret til tabel"

in

  Kilde

[mahoneypat]

Please see this approach as an alternative.  Also, are you able to increase the number of records returned with each web call?

Power BI - Tales From The Front - REST APIs - YouTube

Pat

[Laura_Dannisoe]

Also the approach is using GET to my understanding right? Would prefer POST.

[Laura_Dannisoe]

Hi 

I have tried to watch the alternative in the video, but I am not quite sure how to implement that in my solution. Yes I am increasing the number of records. The limit is different for each endpoint in the API. 

I thought that Buffer could decrease the performance time as it takes around 1,5 hours to refresh the report... 

[ImkeF]

Hi @Laura_Dannisoe ,
a buffer is useful to stop re-evaluating tables or lists that will be referenced multiple times in iterators (like List.Generate, -.Accumulate, -.Transform or Table.AddColumn).
But in your iterator here (List.Generate), there is no direct list or table input (just records or scalars). Therefore you cannot use it here.

Does the function you're calling ("Resumekey_function") reference a table or a list? Then you might want to buffer that in there.

There are other things to watch out for when optimizing performance: Speed/Performance aspects – The BIccountant maybe you find a hint in there.

[Laura_Dannisoe]

Hi Imke

Thank you for your answer. 

The resumekey function looks like below - is there anything here I could buffer?

let
Kilde = (EndPoint as text, Resumekey as text, optional Changedate) =>
let
changedate = if Changedate = null then "" else Changedate,
url = "XXXXXXXXXXXXXX",
body = [contract=MB_Agreement,apikey=MB_Key,username=MB_User,password=MB_Password,commands={[command=EndPoint,changedate=changedate,resumekey=Resumekey]}],
data = Json.Document(Web.Contents(url,[Headers=[#"Content-Type"="application/json"], Content=Json.FromValue(body), IsRetry=true
]))[results]

in data
in
Kilde

It is just very frustrating that it takes 1,5 hours to refresh and 15 minutes to retrieve data from one table, so I thought there must be a way to handle this faster.

[ImkeF]

Hi @Laura_Dannisoe ,
no, cannot spot a table or a list to buffer in here.
Did you experience the performance degradation just today? Is it in dataflows in a Europe region?

Then this might just be a temporary problem: Slow Dataflow Refresh - Microsoft Power BI Community

Otherwise: How many other queries are there in your file/dataflow?

[Laura_Dannisoe]

Hi Imke 

No I have experienced this ever since I did the API calls. Some dataflows only have 1 query while others have 5 queries. 

I have tried to optimize the performance by reducing the columns in each table, but that did not improve anything, so I am quite sure it is the function calling the API which is extremely slow. When I call the API in another system it is not that slow.