CODEWITHSUNDEEP
neo4jcypherdatabase-performance
Aerodynamika
Aerodynamika

Reputation: 8413

How to make WITH Neo4J Cypher 2 query more efficient?

I had a query of this kind, which would basically find a specific node "Statement", find all the nodes connected to it with an :OF relation, find all their connections to one another, as well all the relations of the node "Statement" to other nodes and the node "Statement" itself:

MATCH (s:Statement{uid:"e63cf470-ade4-11e3-bc66-2d7f9b2c7878"}), 
c1-[:OF]->s<-[:OF]-c2, c1-[to:TO]->c2 
WITH DISTINCT to, c1, c2, s 
MATCH c1-[by:BY]->u, c2-[at:AT]->ctx 
WHERE to.statement="e63cf470-ade4-11e3-bc66-2d7f9b2c7878" 
AND by.statement="e63cf470-ade4-11e3-bc66-2d7f9b2c7878" 
AND at.statement="e63cf470-ade4-11e3-bc66-2d7f9b2c7878" 
DELETE s,rel,to,by,at;

This worked OK for when there was 3 nodes connected to the node "Statement", but when there's a 100, it crashes the database.

I tried playing around passing different nodes and relationships with a WITH, but it didn't help.

The closest to a solution that I could get was to set up automatic indexing on relationship properties and then execute the deletion with two queries:

 MATCH (s:Statement{uid:"e63cf470-ade4-11e3-bc66-2d7f9b2c7878"}), 
 s-[by:BY]->u, s-[in:IN]->ctx, c-[of:OF]->s DELETE by,in,of,s; 


 START rel=relationship:relationship_auto_index
 (statement="e63cf470-ade4-11e3-bc66-2d7f9b2c7878") 
 DELETE rel;

2 Questions:

1) I know that the first query took too long because there were too many iterations. How to avoid that?

2) Do you know how to combine the two faster queries above into one so that it works fast and preferably without using the relationship index and START clause?

Thank you!

Upvotes: 0

Views: 85

Answers (1)

Michael Hunger
Michael Hunger

Reputation: 41676

For this statement

You must not separate the condition on to from the match. Then Cypher will find all matches first and only filter after it is done with that.

MATCH (s:Statement{uid:"e63cf470-ade4-11e3-bc66-2d7f9b2c7878"}), 
c1-[:OF]->s<-[:OF]-c2, c1-[to:TO]->c2 
WHERE to.statement="e63cf470-ade4-11e3-bc66-2d7f9b2c7878" 
WITH DISTINCT to, c1, c2, s 
MATCH c1-[by:BY]->u, c2-[at:AT]->ctx 
WHERE by.statement="e63cf470-ade4-11e3-bc66-2d7f9b2c7878" 
AND at.statement="e63cf470-ade4-11e3-bc66-2d7f9b2c7878" 
DELETE s,rel,to,by,at;

Also I'm not sure if this c1-[:OF]->s<-[:OF]-c2, c1-[to:TO]->c2 doesn't span up a cross-product.

Just do this:

MATCH (s:Statement{uid:"e63cf470-ade4-11e3-bc66-2d7f9b2c7878"}), 
c1-[:OF]->s<-[:OF]-c2, c1-[to:TO]->c2 
WHERE to.statement="e63cf470-ade4-11e3-bc66-2d7f9b2c7878" 
RETURN count(*),count(distinct c1), count(distinct c2), count(distinct to)

to see some numbers.

You also don't seem to use (u) and (ctx) in the result? So might be an option to convert that into a condition. (Have to try), then you might even be able to leave of the with (if the cardinality with distinct is not much smaller than without.

....
WHERE c2-[:AT {at.statement:"e63cf470-ade4-11e3-bc66-2d7f9b2c7878"}]->()
AND c1-[:BY {statement:"e63cf470-ade4-11e3-bc66-2d7f9b2c7878"}]->()
DELETE s,rel,to,b

HTH

Would love to get the dataset to try it out.

Upvotes: 1

Related Questions