Hi everyone,
I have a situation as follows: 60,000,000 nodes under the label Service from which I would like to create new nodes and relationships. I have the following query:
// Service
CALL apoc.periodic.iterate(
"
MATCH (s:Service)
RETURN id(s) AS service_id
",
"
MATCH (s:Sevice)
WHERE id(s) = service_id
MERGE (sl:SLA{id: s.id})
ON CREATE SET
sl.respTimePrio4Nonoff = s.respTimePrio4Nonoff,
sl.respTimePrio4Office = s.respTimePrio4Office,
sl.repTimePrio3Office = s.repTimePrio3Office,
sl.respTimePrio1Nonoff = s.respTimePrio1Nonoff,
sl.respTimePrio1Office = s.respTimePrio1Office,
sl.compbaseValue = s.compbaseValue,
sl.repTimePrio2Nonoff = s.repTimePrio2Nonoff,
sl.repTimePrio4Nonoff = s.repTimePrio4Nonoff,
sl.repTimePrio2Office = s.repTimePrio2Office,
sl.respTimePrio2Nonoff = s.respTimePrio2Nonoff,
sl.respTimeOnsPrio1Office = s.respTimeOnsPrio1Office,
sl.respTimePrio3Office = s.respTimePrio3Office,
sl.repTimePrio1Nonoff = s.repTimePrio1Nonoff,
sl.repTimePrio3Nonoff = s.repTimePrio3Nonoff,
sl.respTimePrio3Nonoff = s.respTimePrio3Nonoff,
sl.repTimePrio4Office = s.repTimePrio4Office,
sl.respTimePrio2Office = s.respTimePrio2Office,
sl.repTimePrio1Office = s.repTimePrio1Office
ON MATCH SET
sl.respTimePrio4Nonoff = s.respTimePrio4Nonoff,
sl.respTimePrio4Office = s.respTimePrio4Office,
sl.repTimePrio3Office = s.repTimePrio3Office,
sl.respTimePrio1Nonoff = s.respTimePrio1Nonoff,
sl.respTimePrio1Office = s.respTimePrio1Office,
sl.compbaseValue = s.compbaseValue,
sl.repTimePrio2Nonoff = s.repTimePrio2Nonoff,
sl.repTimePrio4Nonoff = s.repTimePrio4Nonoff,
sl.repTimePrio2Office = s.repTimePrio2Office,
sl.respTimePrio2Nonoff = s.respTimePrio2Nonoff,
sl.respTimeOnsPrio1Office = s.respTimeOnsPrio1Office,
sl.respTimePrio3Office = s.respTimePrio3Office,
sl.repTimePrio1Nonoff = s.repTimePrio1Nonoff,
sl.repTimePrio3Nonoff = s.repTimePrio3Nonoff,
sl.respTimePrio3Nonoff = s.respTimePrio3Nonoff,
sl.repTimePrio4Office = s.repTimePrio4Office,
sl.respTimePrio2Office = s.respTimePrio2Office,
sl.repTimePrio1Office = s.repTimePrio1Office
MERGE (s)-[:FULFILLS]->(sl)
WITH s
CALL{
WITH s
WITH s
WHERE s.billProfileId IS NOT NULL
MERGE (bp:BillProfile{id: s.billProfileId})
MERGE (s)-[:IS_CHARGED_ON]->(bp)
}
CALL{
WITH s
WITH s
WHERE s.contractId IS NOT NULL
MERGE (c:Contract{id: s.contractId})
MERGE (s)-[:IS_INCLUDED_IN]->(c)
}
CALL{
WITH s
WITH s
WHERE s.productId IS NOT NULL
MERGE (p:Product{id: s.productId})
MERGE (s)-[:IS_OFFERED_FOR]->(p)
}
CALL{
WITH s
WITH s
WHERE s.subProductId IS NOT NULL
MERGE (p1:Product{id: s.subProductId})
MERGE (s)-[:IS_OFFERED_FOR]->(p1)
}
CALL{
WITH s
WITH s
WHERE s.agoProductId IS NOT NULL
MERGE (e:ExternalID:AGO{value: row.agoProductId})
ON CREATE SET e.type = 'Product ID'
ON MATCH SET e.type = 'Product ID'
MERGE (s)-[:HAS_EXTERNAL_ID]->(e)
}
CALL{
WITH s
WITH s
WHERE s.cldBusinessGroupId IS NOT NULL
MERGE (e1:ExternalID:CLD{value: s.cldBusinessGroupId})
ON CREATE SET e1.type = 'Business Group ID'
ON MATCH SET e1.type = 'Business Group ID'
MERGE (s)-[:HAS_EXTERNAL_ID]->(e1)
}
CALL{
WITH s
WITH s
WHERE s.ippSpId IS NOT NULL
MERGE (e2:ExternalID:IPP{value: s.ippSpId})
ON CREATE SET e2.type = 'Sp ID'
ON MATCH SET e2.type = 'Sp ID'
MERGE (s)-[:HAS_EXTERNAL_ID]->(e2)
}
CALL{
WITH s
WITH s
WHERE s.mrkEntitlementId IS NOT NULL
MERGE (e3:ExternalID:MRK{value: s.mrkEntitlementId})
ON CREATE SET e3.type = 'Entitlement ID'
ON MATCH SET e3.type = 'Entitlement ID'
MERGE (s)-[:HAS_EXTERNAL_ID]->(e3)
}
CALL{
WITH s
WITH s
WHERE s.samEquipmentId IS NOT NULL
MERGE (e4:ExternalID:SAM{value: s.samEquipmentId})
ON CREATE SET e4.type = 'Equipment ID'
ON MATCH SET e4.type = 'Equipment ID'
MERGE (s)-[:HAS_EXTERNAL_ID]->(e4)
}
CALL{
WITH s
WITH s
WHERE s.symContractId IS NOT NULL
MERGE (e5:ExternalID:SYM{value: s.symContractId})
ON CREATE SET e5.type = 'Contract ID'
ON MATCH SET e5.type = 'Contract ID'
MERGE (s)-[:HAS_EXTERNAL_ID]->(e5)
}
CALL{
WITH s
WITH s
WHERE s.tisKsapId IS NOT NULL
MERGE (e6:ExternalID:TIS{value: s.tisKsapId})
ON CREATE SET e6.type = 'Ksap ID'
ON MATCH SET e6.type = 'Ksap ID'
MERGE (s)-[:HAS_EXTERNAL_ID]->(e6)
}
CALL{
WITH s
WITH s
WHERE s.oitInstanceId IS NOT NULL
MERGE (e7:ExternalID:OIT{value: s.oitInstanceId})
ON CREATE SET e7.type = 'Instance ID'
ON MATCH SET e7.type = 'Instance ID'
MERGE (s)-[:HAS_EXTERNAL_ID]->(e7)
}
REMOVE s.respTimePrio4Nonoff, s.respTimePrio4Office, s.repTimePrio3Office, s.respTimePrio1Nonoff, s.respTimePrio1Office, s.compbaseValue, s.repTimePrio2Nonoff, s.repTimePrio4Nonoff, s.repTimePrio2Office, s.respTimePrio2Nonoff, s.respTimeOnsPrio1Office, s.respTimePrio3Office, s.repTimePrio1Nonoff, s.repTimePrio3Nonoff, s.respTimePrio3Nonoff, s.repTimePrio4Office, s.respTimePrio2Office, s.repTimePrio1Office, s.billProfileId, s.contractId, s.productId, s.subProductId, s.agoProductId, s.cldBusinessGroupId, s.ippSpId, s.mrkEntitlementId, s.samEquipmentId, s.symContractId, s.tisKsapId, s.oitInstanceId
",
{batchSize:10000, parallel:false}
);
This query fails due to an OOM error. I have tried different versions of the query, like separating the queries into two different queries or creating first only the nodes and then the relationships but without achieving a good performance/finish of the queries. I am using Neo4j 4.4.4 with 130GB of RAM in cluster mode. The properties used to Merge are all indexed. Is there any smart way to make this query performant without separating it into 10 different queries which would mean traversing the Service nodes non-efficiently? Thanks!
