There are Eager operators in your plan, so those again are sabotaging the periodic commit...it's trying to walk through the query across all rows at the same time (one operation across all rows, then the next operation across all rows, etc) which is blowing up your heap.
We need to adjust the query to avoid those Eagers and let the periodic commit do its job.
One way to do this is breaking up each of the segment with a subquery. You need to be running Neo4j 4.1.x or higher to do this:
USING PERIODIC COMMIT LOAD CSV WITH HEADERS FROM 'file:///mydata.csv' AS row
MERGE (f:Frame {id: row.id })
WITH f, row
CALL {
WITH f, row
WITH f, row
WHERE row.b0_x0 IS NOT NULL
MERGE (b0:Box { code:(row.b0_x0 + ',' + row.b0_y0 + ',' + row.b0_x1 + ',' + row.b0_y1) })
MERGE (f)-[r0:HAS_BOX]->(b0)
RETURN count(*) as count
}
WITH f, row
CALL {
WITH f, row
WITH f, row
WHERE row.b1_x0 IS NOT NULL
MERGE (b1:Box { code:(row.b1_x0 + ',' + row.b1_y0 + ',' + row.b1_x1 + ',' + row.b1_y1) })
MERGE (f)-[r1:HAS_BOX]->(b1)
RETURN count(*) as count
}
RETURN count(*)
The reason for the repeated WITH f, row lines in the subquery calls is due to a limitation right now where we cannot use a WHERE clause on the importing (first) WITH clause of the subquery, so we need to add a second one for this purpose.
Each subquery has to return something, and by returning a count(*) aggregation that means we can return 1 row even if the WHERE clause filtered out all the rows of the subquery a few lines earlier. This ensures the query can continue executing afterwards.
