Cypher performance with subqueries

[Luanne Coutinho]

Hi,

Is it recommended that for queries with a large number of match conditions we attempt to filter data out in subqueries using WITH rather than one giant match? Would it perform better generally or does it not matter?

(Using 1.8.1, embedded)

Thanks
Luanne

[Michael Hunger]

The question is, are you using match to actually expand the result set by describing patterns or do you just want to make certain that some relationships are there (or not) aka FILTERING.

If the latter, you might want to use the path-expression syntax in a where clause.

WHERE a-[:FOO]->b
or WHERE NOT(a-[:FOO]->b)

it depends on what performance you currently encounter, sometimes it makes sense to do the core match upfront and then have the basic results in a with and do smaller additional matches one by one later.

In the future cypher will rewrite the matches/queries on its own but we're not there yet :)

Cheers

Michael

> --
>
>

[Luanne Coutinho]

Thanks Michael. I am having trouble with quite a few queries on a fairly small graph (they take anywhere from 1500ms to 13000ms) and they are mostly written in this way:

    start c=node:companies(id="5428")
    match company<-[:parent_company*0..]-c
    with c
    match (c)-[:defines_business_process]->bp-[:has_cycle]->cycle-[:cycle_metric]->metric-[:metric_activity]->ma-[:metric_unit]->unit
    where cycle.measureDate>={startDate} and cycle.measureDate<={endDate}
    with ma, unit, cycle
    match (ma)-[:metric_unit]->(unit)<-[:alert_for_unit]-(alert)-[:alert_for_inspection]->(inspection)-[:has_result]->(result)-[:for_inspection_result]-(alert)
    where alert.alertDate=cycle.measureDate and result.value={problem} and(inspection)<-[:metric_inspection]-(ma) and alert.fromEntityType=14
    return distinct alert.id as alertId, unit.id as unitId, unit.name as unitName

Do you see something that might be the cause for this?

The graph has 8453 nodes, 16329 properties, 27773 relations and the result from this particular query ranges from 5 to 20 rows.

Thanks
Luanne

--

[Michael Hunger]

Which version of Neo4j are you using with this?

- cause is exponential explosion along the paths #rels ^ #hops

- perhaps try to limit unlimited path matches, there's certainly a domain limit?  company<-[:parent_company*0..]-c

- use parameters everywhere: alert.fromEntityType=14

- this match is duplicate: match (ma)-[:metric_unit]->(unit)

- perhaps split this match up too: match (ma)-[:metric_unit]->(unit)<-[:alert_for_unit]-(alert)-[:alert_for_inspection]->(inspection)-[:has_result]->(result)-[:for_inspection_result]-(alert)

into

match (ma)-[:metric_unit]->(unit)<-[:alert_for_unit]-(alert)

where alert.fromEntityType=14 AND alert.alertDate=cycle.measureDate

WITH unit, alert, ma

MATCH (alter)-[:alert_for_inspection]->(inspection)-[:has_result]->(result)

WHERE (result)-[:for_inspection_result]-(alert) AND and(inspection)<-[:metric_inspection]-(ma)

- remember, that distinct kills your lazyness of the query, can you try to run it without?

HTH

Michael

--
 
 

[Luanne Coutinho]

Thank, I already did all of these a while back except alert.fromEntityType=14 (because it is constant--never changes- should it still be parameterized?)
My execution time is down to 180ms--time to leave it and focus on the others :-)

Thanks for the pointers though, at least I know I'm on the right track.

-Luanne

--
 
 

[Michael Hunger]

Good to hear, sorry that we're not there with auto-optimizing queries!

Perhaps you can devise a kind of optimization guide for the current implementation of cypher which would benefit a lot of users and publish it as a blog.

That would be something great for the new year.

Thanks a lot Luanne

Michael

--