Repo query method is slow

[csabahalmagyi]

Hi devs,

I am experiencing performance issues with a rather small dataset (few hundred thousand nodes).

I have the following nodes:

(NodeA)-[:ASSOCIATED_WITH]-(Association)-[:MARKER]-(Marker)

@NodeEntity
public class NodeA{

@GraphId
Long id;

private String type;

@Relationship(type = "ASSOCIATED_WITH")
List<Association> associations;

}

@NodeEntity
public class Association{

@GraphId
Long id;

private Marker marker;

private String prop1;
private String prop2;
private String prop3;
private String prop4;
.
.
private String prop15;
}

@NodeEntity
public class Marker{

@GraphId
Long id;

private String symbol;
}

There are about 100-30.000 Associations linked to NodeA and there is one Marker per Association (the Marker node is often the same)

Running a query method that gets the associations with the markers from the db is rather slow...

@Query("
MATCH (n1:NodeA) WHERE n1.type = {type} 
WITH n1
MATCH (n1)-[r1:ASSOCIATED_WITH]-(n2:Association)-[r2:MARKER]-(n3:Marker) 
RETURN n1, r1, n2, r2, n3
")
NodeA getWithDataByType(@Param("type") String type);

Q1: What changes should I make to improve query performance?
Q2: I don't think I can use a composite index on the associations since not all 15 props are populated for every node. What other indexing options do I have?

[jennifer_reif]

Hello!

Have is the type property on the NodeA label indexed? If that's the search parameter, it should help to index that one.

Also, I think an @Relationship needs added to the Marker definition inside the Association class. Linking those within the application will match the results from the database with your application entities/relationships.

Finally, does a query structured like this improve performance? Pulling a single path might be better than using 2 separate MATCH statements.

MATCH (n1:NodeA)-[r1:ASSOCIATED_WITH]-(n2:Association)-[r2:MARKER]-(n3:Marker)
WHERE n1.type = {type} 
RETURN n1, r1, n2, r2, n3

Let me know if this helps!

Cheers,
Jennifer

[csabahalmagyi]

Hi Jennifer,

Thanks for the suggestions.

• Yes, NodeA label is indexed. (as in I am using the @Index annotation on it)
• There was no Relationship annotation on the marker, added that.
• Also changed the query structure to have one match.

After these changes I still have similar results.

An example here: Number of associations linked to NodeA: 7058, Query response time:10469ms

Any other suggestions?

Regards,
Csaba

[csabahalmagyi]

I wonder if it would help if I stored the ASSOCIATED_WITH relationship on both NodeA and the Association nodes? Or is that an overkill?

[Bert]

Can you check if the index is actually there? CALL apoc.index.list() YIELD type,name,config

From what I read on https://docs.spring.io/spring-data/neo4j/docs/5.1.4.RELEASE/reference/html/#reference:indexing:creat... just adding @Index might not be enough. But it is some time since I last used SDN.