Dependency table loads for quite long for some channels

[premun]

Example: https://product-construction-prod.wittysky-0c79e3cc.westus2.azurecontainerapps.io/channel/5173/github:dotnet:sdk/build/latest

[oleksandr-didyk]

We seem to be spending a lot of time waiting for a response from the API, I'll check if we can do something about it

[oleksandr-didyk]

Since Maestro's BarViz is behaving the same and this issue seems to be on the API side, will remove the BarViz prefix

[oleksandr-didyk]

Ran local instance of BarViz against production database and confirmed that the extended duration of the call comes from the service waiting for a response from the database for certain builds that have a large graph (like the one in the example).

To speed up the request we would need to optimize the database query we perform, which I am not sure we want to spend time on now

CC: @premun

[premun]

@oleksandr-didyk can you paste an example query so that we have that on hand whenever we decide to address this?

[oleksandr-didyk]

Query ID: 918460

(@id int)WITH traverse AS (
        SELECT
            BuildId,
            DependentBuildId,
            IsProduct,
            TimeToInclusionInMinutes,
            0 as Depth
        from BuildDependencies
        WHERE BuildId = @id
    UNION ALL
        SELECT
            BuildDependencies.BuildId,
            BuildDependencies.DependentBuildId,
            BuildDependencies.IsProduct,
            BuildDependencies.TimeToInclusionInMinutes,
            traverse.Depth + 1
        FROM BuildDependencies
        INNER JOIN traverse
        ON BuildDependencies.BuildId = traverse.DependentBuildId
        WHERE traverse.IsProduct = 1 -- The thing we previously traversed was a product dependency
            AND traverse.Depth < 10 -- Don't load all the way back because of incorrect isProduct columns
)
SELECT DISTINCT BuildId, DependentBuildId, IsProduct, TimeToInclusionInMinutes
FROM traverse

Example times

• Repo: dotnet/sdk
• Channel: .NET 10.0.1xx SDK
• BAR Build ID: 245591
• Query time: 9.2 seconds