Reduce SQL queries generated by GraphQL resolvers

[Mark90]

Summary

The workflows GraphQL resolver generates many SQL queries (300+ in the SURF orchestrator) for a query like this:

query MyQuery {
  workflows {
    page {
      name
      target
      products {
        tag
      }
    }
  }
}

This is most likely happening in Workflow.from_pydantic(obj) where the relationships on each SQLAlchemy WorkflowTable object are evaluated, and because they haven't been joinedload-ed this results in additional queries to the database. I expect similar behavior on some of the other GraphQL resolvers. The subscriptions and processes resolvers though are probably fine because they statically join other tables.

The ideal solution would be to dynamically add joinedload() options to the SQLAlchemy query based on the requested fields in the GraphQL query.

Tasks

• [ ] Reduce SQL queries performed by GraphQL resolvers
• [ ] (optional) Some kind of Strawberry extension to log how many queries took place during a GraphQL request. Perhaps this can be established with the before_cursor_execute event

Testing

Rudimentary performance tests before/after changing the code should show a noticeable improvement in the time it takes. Here's how you could do that from a Linux shell using jq and ab.

1. Convert your GraphQL query to a JSON payload

$ echo "$(jq -c -n --arg query '
{
  workflows {
    page {
      name
      target
      products {
        tag
      }
    }
  }
}' '{"query":$query}')" | tr -d '\n' > query.json

2. Check the payload (you can also edit it manually)

$ cat query.json
{"query":"{  workflows {    page {      name      target      products {        tag      }    }  }}"}
$ curl -X POST -H "Content-Type: application/json" --data @query.json https://orchestrator.dev.automation.surf.net/api/graphql
{"data": {"workflows": {"page": [{"name": "modify_note", "target": "MODIFY", "products": [{"tag": "...

3. Run apachebench (before making changes)

$ ab -n 10 -T application/json -p query.json https://orchestrator.dev.automation.surf.net/api/graphql
...

Benchmarking orchestrator.dev.automation.surf.net (be patient).....done

...

Document Path:          /api/graphql
Document Length:        1711 bytes

Concurrency Level:      1
Time taken for tests:   13.794 seconds
Complete requests:      10
Failed requests:        0
Total transferred:      20210 bytes
Total body sent:        2700
HTML transferred:       17110 bytes
Requests per second:    0.72 [#/sec] (mean)
Time per request:       1379.360 [ms] (mean)
Time per request:       1379.360 [ms] (mean, across all concurrent requests)

...

4. Repeat 3 after making changes, compare results