Query performance / execution time

[chris23]

Hi folks,

I'am working on a query to get courses which have a link to a certain user. But courses should also get ignored if there is a connection from the user to the course following another different path. I tried one query which doesnt impact running time to much but that query only works in one scenario because I did not specify the path/path length enough so even if there are courses which should be valid it seems to go backwards again using the bodyPart Relationship. So I altered the query to specify the path that should be found more. But as a consequence the running time of the query got >10x worse. I am not quite sure why and how i can improve the performance of my query, maybe somebody can help? (I am refering to line 3 not exists....)

Fast not working correctly version:

MATCH (m:Person {name:'Max Musterman'})-[y:is]->(:type)-[z:is_related_to]->(:courseType)<-[x:belongs_to]-(c:course)
        where (point.distance(point({longitude:toFloat(c.longitude), latitude:toFloat(c.latitude)}),point({longitude:8.6820917,latitude:50.1106444})) < 10000000
        or c.online = "True" ) and not exists((m)-[:has_injury]->(:bodyPart)-[*]-(c))
        with distinct c 
        CALL { 
        WITH c 
        MATCH (m:Person {name:'Max Musterman'})-[y:is]->(:type)-[z:is_related_to]->(:courseType)<-[x:belongs_to]-(c:course) 
        with duration.inDays(y.creation, y.expiration).days as dur, c, y, z, x, case when duration.inDays(datetime(), y.expiration).days + 1 <= 0 then 0 
        else duration.inDays(datetime(), y.expiration).days + 1 end as rDur 
        return ((y.weight + z.weight + x.weight) / 3) * (toFloat(rDur) / dur) as x, c as n order by x descending 
        LIMIT 1 
        } 
        RETURN x, n 
        Order by x descending 
        LIMIT 20

Slow correctly working version:

MATCH (m:Person {name:'Max Musterman'})-[y:is]->(:type)-[z:is_related_to]->(:courseType)<-[x:belongs_to]-(c:course)
        where (point.distance(point({longitude:toFloat(c.longitude), latitude:toFloat(c.latitude)}),point({longitude:8.6820917,latitude:50.1106444})) < 10000000
        or c.online = "True" ) and not exists((m)-[:has_injury]->(:bodyPart)-[*]-()<--(:courseType)<-[]-(c))
        with distinct c 
        CALL { 
        WITH c 
        MATCH (m:Person {name:'Max Musterman'})-[y:is]->(:type)-[z:is_related_to]->(:courseType)<-[x:belongs_to]-(c:course) 
        with duration.inDays(y.creation, y.expiration).days as dur, c, y, z, x, case when duration.inDays(datetime(), y.expiration).days + 1 <= 0 then 0 
        else duration.inDays(datetime(), y.expiration).days + 1 end as rDur 
        return ((y.weight + z.weight + x.weight) / 3) * (toFloat(rDur) / dur) as x, c as n order by x descending 
        LIMIT 1 
        } 
        RETURN x, n 
        Order by x descending 
        LIMIT 20

[jalakoo]

Hi @chris23, I haven't looked deeply yet between the 2 queries, but have you already tried running EXPLAIN or PROFILE before the MATCH keyword to see if either output gives you insight into what might be happening?

More info here on the PROFILE option, and a thread regarding the EXPLAIN option.