Create operations generates a gigantic cypher query for batch inserts

[lukasbals]

Describe the bug
OGMModel.create({input: [batch]}) generates a gigantic Cypher query for batch inserts and makes the planner and runtime choke.

Type definitions
If applicable, add your type definitions to help us reproduce the bug.

To Reproduce
Steps to reproduce the behavior:

1. Run OGMModel.create({input: [batch]}) with a batch (~1000 rows)
2. See the Neo4j instance getting slow and having a 100% CPU utilization

Expected behavior
I expect the creation of a batch with around 1000 rows to be done in milliseconds.

Log

Example neo4j/graphql debug log with a batch with one item to show the generated query:

Query:
mutation ($input: [PlotCreateInput!]!) {
  createPlots(input: $input) {
    plots {
      id
      featureId
      title
      description
      country
      cadastralNumber
      area
      bbox
      name
      createdAt
      updatedAt
    }
  }
}
Variables:
{
  "input": [
    {
      "id": "AT-91234-1234/2",
      "featureId": 59494,
      "title": "1234/2",
      "description": "GNR 1234/2, KG 91234 SomeString",
      "country": "AT",
      "cadastralNumber": "91234",
      "area": 152271.7361600399,
      "bbox": [
        9.889720987,
        42.232098474,
        9.900413672,
        47.325398932
      ],
      "name": "",
      "project": {
        "connect": {
          "where": {
            "node": {
              "id": "1bfb923c-dc6b-4014-b042-2e8ec3f851c8"
            }
          }
        }
      }
    }
  ]
} +62ms
  @neo4j/graphql:execute About to execute Cypher:
Cypher:
CALL {
CREATE (this0:Plot)
SET this0.createdAt = datetime()
SET this0.updatedAt = datetime()
SET this0.id = $this0_id
SET this0.featureId = $this0_featureId
SET this0.title = $this0_title
SET this0.description = $this0_description
SET this0.country = $this0_country
SET this0.cadastralNumber = $this0_cadastralNumber
SET this0.area = $this0_area
SET this0.bbox = $this0_bbox
SET this0.name = $this0_name
WITH this0
CALL {
	WITH this0
	OPTIONAL MATCH (this0_project_connect0_node:Project)
	WHERE this0_project_connect0_node.id = $this0_project_connect0_node_id
	FOREACH(_ IN CASE this0 WHEN NULL THEN [] ELSE [1] END |
		FOREACH(_ IN CASE this0_project_connect0_node WHEN NULL THEN [] ELSE [1] END |
			MERGE (this0)<-[:BELONGS_TO]-(this0_project_connect0_node)
		)
	)
	RETURN count(*)
}
WITH this0
CALL {
	WITH this0
	MATCH (this0)<-[this0_project_Project_unique:BELONGS_TO]-(:Project)
	WITH count(this0_project_Project_unique) as c
	CALL apoc.util.validate(NOT(c <= 1), '@neo4j/graphql/RELATIONSHIP-REQUIREDPlot.project must be less than or equal to one', [0])
	RETURN c AS this0_project_Project_unique_ignored
}
RETURN this0
}
RETURN
this0 { .id, .featureId, .title, .description, .country, .cadastralNumber, .area, .bbox, .name, createdAt: apoc.date.convertFormat(toString(this0.createdAt), "iso_zoned_date_time", "iso_offset_date_time"), updatedAt: apoc.date.convertFormat(toString(this0.updatedAt), "iso_zoned_date_time", "iso_offset_date_time") } AS this0
Params:
{
  "this0_id": "AT-91234-1234/2",
  "this0_featureId": {
    "low": 59494,
    "high": 0
  },
  "this0_title": "1234/2",
  "this0_description": "GNR 1234/2, KG 91234 SomeString",
  "this0_country": "AT",
  "this0_cadastralNumber": "91234",
  "this0_area": 152271.7361600399,
  "this0_bbox": [
    9.889720987,
    42.232098474,
    9.900413672,
    47.325398932
  ],
  "this0_name": "",
  "this0_project_connect0_node_id": "1bfb923c-dc6b-4014-b042-2e8ec3f851c8"
} +4ms
  @neo4j/graphql:execute Execute successful, received 1 records +182ms

System (please complete the following information):

• OS: MacOS
• Version:

    "@neo4j/graphql": "^3.0.3",
    "@neo4j/graphql-ogm": "^3.0.3",

• Node.js version: v16.14.0
• Neo4j instance: Neo4j instance in AuraDB (1GB RAM, 1 CPU, 2GB Storage - the smallest version)

[neo4j-team-graphql]

Many thanks for raising this bug report @lukasbals. 🐛 We will now attempt to reproduce the bug based on the steps you have provided.

Please ensure that you've provided the necessary information for a minimal reproduction, including but not limited to:

• Type definitions
• Resolvers
• Query and/or Mutation (or multiple) needed to reproduce

If you have a support agreement with Neo4j, please link this GitHub issue to a new or existing Zendesk ticket.

Thanks again! 🙏

[lukasbals]

Link to a discussion on Discord on this topic: https://discord.com/channels/787399249741479977/818578492723036210/960971957778649208

[lukasbals]

@darrellwarde can we prioritise this issue since it's blocking us?

It leads to a downtime of the database as soon as you try to insert more than 500 items at once.

[darrellwarde]

Hey @lukasbals, it's on our to-do list to look at in the current batch of "performance tickets" we're currently picking up. The solution will be to use UNWIND to do these batch inserts more effectively, but there are some complexities in the design that we need to firm up. All going well, it will be addressed this quarter, but that isn't a commitment.

[lukasbals]

Thanks for the update!

[MacondoExpress]

Hi @lukasbals, we made some progress over this issue and are currently merged on dev, unfortunately, at the moment I'm writing, your mutation will not be optimized as it seems that you are using an operation in your batch connect that is excluded by this optimization.

[lukasbals]

Hi @MacondoExpress, thanks for the update - nice to see some progress here! Do you have a suggestion for a workaround to not use the connect in my batch insert?

[MacondoExpress]

Hi @lukasbals
I share my knowledge about this issue, hoping that could help you find the best solution to your case.
The Cypher generated is not optimal, but it's not always slow. There is a limit after that the performance is worsening in a non-linear way.
Usually, the limit on our local machines where this performance issue appears is around 500 Nodes/Relationships, but this is very likely dependent on several factors.
Considering the above, I will split the whole batch into separated small batches. If you're interested in keeping a single transaction, then you can dispatch the transaction in the GraphQL context, using your custom business logic, as described here:
https://neo4j.com/docs/graphql-manual/current/driver-configuration/.
I hope this will help you!