GET /v3/roles is slower than v2 user summary

[cloudmarius]

Issue

Getting the roles for a given user is slower via v3 than via v2.

Context

      "cloud_controller_v2": {
         "href": "...",
         "meta": {
            "version": "2.148.0"
         }
      },
      "cloud_controller_v3": {
         "href": "...",
         "meta": {
            "version": "3.83.0"
         }
      }

cf -v
cf version 6.51.0+2acd15650.2020-04-07

Steps to Reproduce

cf curl "/v3/roles?user_guids=:guid&per_page=500"
cf curl "/v2/users/:guid/summary"

Expected result

GET /v3/roles?user_guids=:guid&per_page=500 must not be slower than GET /v2/users/:guid/summary.

Current result

The v3 endpoint takes twice as long on average to return the results.
Eg: given a user with 135 roles

• v3 returns aprox. 130Kb in 2.6 seconds
• v2 returns aprox. 100Kb in 1.2 seconds

[cf-gitbot]

We have created an issue in Pivotal Tracker to manage this:

https://www.pivotaltracker.com/story/show/172932763

The labels on this github issue will be updated when the story is started.

[aashah]

Hey @marius-munteanu-sc,

Thanks for submitting the issue. We were easily able to reproduce the issue using a quick helper script to add a bunch of entries in the database: scripts/seed_roles.rb.

We found that v3 was making an excessive number of calls to load the same User record from the DB:

$ rg -F 'SELECT' v2-queries.log  | awk '{print $5}' | sort | uniq -c
   4 `organizations`
  80 `quota_definitions`
  83 `spaces`
  83 `users`

$ rg -F 'SELECT' v3-queries.log  | awk '{print $5}' | sort | uniq -c
   1 `count`
   1 `organizations_auditors`
   1 `organizations_billing_managers`
   1 `organizations_managers`
   1 `organizations_users`
 124 `organizations`
   1 `spaces_auditors`
   1 `spaces_developers`
   1 `spaces_managers`
 876 `spaces`
1002 `users`

We have a patch commit that we think might address some of these issues, by eager loading associated records where possible -- reducing the number of queries to ~15. That's a good start, but we have another item or two of things to explore before we push it.

Just wanted to share our findings!

• Aakash && @sethboyles

[sethboyles]

We pushed a fix for this that should reduce the number of queries to ~15. On our testing environments we saw that the response time went from ~2.5 secs to ~1.3 seconds using the script @aashah linked above.

9b8083e

[cloudmarius]

This is great news @sethboyles.
Will this be included in v3.85.x?

[jspawar]

@marius-munteanu-sc this should be included in version 1.95.0 of capi-release and version of 3.85.0 of the CC API.

Closing out for now, please reopen/open a new issue of course if the issue persists.

Thanks!