Will multiple queries help with 502 Bad Gateway errors?

[MarkusEng1998]

I use pagination in my query, but I still receive a 502 Bad Gateway error. Each time it is a different record triggering the error.

The data source has 200+ projects with 220k records.

When I limit my query to 40 projects, the query runs without any issues.

However, when I query with all of the 200+ projects, I receive the 502.

If I split my query into six queries with only 40 projects, will that help?

In other words, do each of the queries run sequentially, or do they run concurrently hitting the endpoint?

Would using Function.InvokeAfter() be more effective than splitting the query up?

Thank you for any guidance.

[MarkusEng1998 · Accepted solution]

getItems()

1) start with the project list.

2) List.Generate() to paginate every 1000 records.

3) getProjectURIpaginated([projectNo])

4) append all the queries

let
    Source = (qrySource as table) => let

    #"01listGenerate" = Table.AddColumn(qrySource, "dRofusRecord", each List.Generate(() =>
[skip = 0,  top = 1000 ],
each [skip] <= rmSkip*10,   // each room has less than 10,000 items.
each [skip = [skip] + [top], 
      top = [top] ]
)),

    #"02expandList" = Table.ExpandListColumn(#"01listGenerate", "dRofusRecord"),
    #"03dRofusRecord" = Table.ExpandRecordColumn(#"02expandList", "dRofusRecord", {"skip", "top"}, {"skip", "top"}),
    #"04changeType" = Table.TransformColumnTypes(#"03dRofusRecord",{{"skip", Int64.Type}, {"top", Int64.Type}}),
    #"10getProjURI" = Table.AddColumn(#"04changeType", "dRofusRecord", each getProjectURIpaginated([projNo], "occurrences", #"105APImapOccurrences", [top], [skip])),
    #"11expandProjURI" = Table.ExpandListColumn(#"10getProjURI", "dRofusRecord"),
    #"12expandDrofusRecord" = Table.ExpandRecordColumn(#"11expandProjURI", "dRofusRecord
....

getProjectURIpaginated()

(projectNo as text, objectType as text, selectColumnTable as table, topVal as number, skipVal as number)=>
let
    #"FilterColumnTable" = Table.SelectRows(selectColumnTable, each ([ObjectType] = objectType)),
    GetWebContents =
        Web.Contents(
            "https://api-us.drofus.com/api/company/",
            [
                RelativePath= projectNo
                & "/" & objectType
                & "?$select=" & Text.Combine(#"FilterColumnTable"[dRofus.ID], ",")
                & "&$top=" & Number.ToText(topVal)
                & "&$skip=" & Number.ToText(skipVal) 
            ]
        ),            
    GetBody = Json.Document(GetWebContents)    
in
    GetBody

[v-shex-msft]

Hi @MarkusEng1998,

I think the error message may be related to you send too many requests which may trigger the limits on the API.
For this scenario, you can try to add interval to your loop to reduce the concurrent request amount and increase processing times.

HTTP 502 - Wikipedia

Regards,

Xiaoxin Sheng

[MarkusEng1998]

Thank you Xiaoxin. When I change the interval to 100 (top = 100), the getProjectURIpaginated() runs without 502 errors.

[v-shex-msft]

Hi @MarkusEng1998 ,

Can you please share some more detail information about this issue? They should help us clarify your scenario and test to troubleshoot.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng