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

[x] Reduce SQL queries performed by GraphQL resolvers
[x] Click through pages and workflows in the UI and check postgres logs for suboptimal resolvers
[x] (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.

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

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": "...

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

workfloworchestrator / orchestrator-core

Reduce SQL queries generated by GraphQL resolvers #351

Summary

Tasks

Testing