# Performance problems with apoc.path.expand and apoc.run.cypher

**URL:** <https://community.neo4j.com/t/performance-problems-with-apoc-path-expand-and-apoc-run-cypher/37861>\
**Category:** Procedures & APOC\
**Tags:** apoc, performance, cypher\
**Created:** [May 5, 2021, 11:28am UTC](https://community.neo4j.com/t/performance-problems-with-apoc-path-expand-and-apoc-run-cypher/37861 "2021-05-05T11:28:52Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![clandestino\_bgd](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/clandestino_bgd/32/3838_2.png) [@clandestino\_bgd](https://community.neo4j.com/u/clandestino_bgd)\
**Post date:** [May 5, 2021, 11:28am UTC](https://community.neo4j.com/t/performance-problems-with-apoc-path-expand-and-apoc-run-cypher/37861/1 "2021-05-05T11:28:53Z")

</div>

Hello, I have raised this problem on discord, hopefully, it will get some more attention here.

My model:

```auto
Period (id, year, startDate, endDate, type)
Indices are on all fields. startDate and endDate are of type Date

Client(login, type)
Indices are on all fields

(Client)-[:HAS_SPONSOR]->(Client)

Commissionable (effectiveDate, value, type)
Indices are on effectiveDate (of type Date) and type

```

I need to calculate and create Commission|Earning (id, type, value) nodes and connect them with Period and Client:  
(Client)-[:EARNED]-\>(Commission)-[:EARNING\_FOR\_PERIOD]-\>(Period)

The simplest query, that works reasonably well:

> <https://github.com/magaton/slashco-mlm/blob/master/variable-length-rel.cypher>

Profile is [slashco-mlm/profile-var-length-rel.txt at master · magaton/slashco-mlm · GitHub](https://github.com/magaton/slashco-mlm/blob/master/profile-var-length-rel.txt)

I have 67 ambassadors only (it will be millions of them ☹ ) and it takes 9s.  
I am also trying to find a way how to make it faster.

Now, since the hierarchy can be configurable (as mentioned before) and can be, e,g. 3 or 4, instead of 2, I would like to use `apoc.path.expand`, since variable-length relationship cannot be paralelised.

> <https://github.com/magaton/slashco-mlm/blob/master/apoc-path-expand.cypher>

This one takes 14 min!?  
Profile is here:

> <https://github.com/magaton/slashco-mlm/blob/master/profile-apoc-path-expander.txt>

I profiled neo4j process with visualVM and although my heap sizes are 1g min and 4g max, the memory is never above 750mb. CPU stays reasonably low. My env: `OSX 11.2.3, JDK 11, neo4j community 4.2.4, single instance, apoc-4.2.0.2`

What could be the problem, why `apoc-path-expand` is so much slower than variable-length relationship query.

And finally, since in queries I have fragments that should come from the configuration. I have tried to isolate them in cypher queries and call them with `apoc.cypher.run`

query:

> <https://github.com/magaton/slashco-mlm/blob/master/variable-length-rel-with-cypher-run.cypher>

profile:

> <https://github.com/magaton/slashco-mlm/blob/master/profile-var-length-rel-with-cypher-run.txt>

This takes 14s, much longer than 1st query without `apoc.cypher.run`.  
What could be the reason?

Thanks in advance,  
Milan

---

<div class="post-metadata">

**Author:** ![andrew\_bowman](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/andrew_bowman/32/73_2.png) [@andrew\_bowman](https://community.neo4j.com/u/andrew_bowman)\
**Post date:** [May 6, 2021, 7:43pm UTC](https://community.neo4j.com/t/performance-problems-with-apoc-path-expand-and-apoc-run-cypher/37861/2 "2021-05-06T19:43:40Z")

</div>

Hello,

I'll need to take a closer look later, but this isn't directly due to the APOC procs, but of how the planner is planning the rest of the query.

Remember that most Cypher operations execute per row, so the more rows there are, the more work is needed.

In your first query plan, rows hit a max of 90128 rows and the mode is around 15k rows. DB hits spike at 18552115 db hits on the optional expansion.

In the apoc.cypher.run query plan, rows also spike early at 90128 rows, and the mode is at about 64k rows, with a db hit spike of 18560302 which is about the same as the last query.

The path expander plan is the worst, with a spike of 528401520 rows and two consecutive db hit spikes of around 528427384. This is due to a label scan and a hash join midway through the query.

I'll look at this in more depth later on, but understand, these rows and spikes are not directly because of the APOC calls, but around how the query was planned around them. There may be ways to optimize to deal with some of the bad planner decisions here.

---

<div class="post-metadata">

**Author:** ![clandestino\_bgd](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/clandestino_bgd/32/3838_2.png) [@clandestino\_bgd](https://community.neo4j.com/u/clandestino_bgd)\
**Post date:** [May 13, 2021, 11:12am UTC](https://community.neo4j.com/t/performance-problems-with-apoc-path-expand-and-apoc-run-cypher/37861/3 "2021-05-13T11:12:45Z")

</div>

Am I right to say that one should not use apoc procedures without tweaking execution plan if the performance is of concern?

If so, hope that this does not apply to function, meaning those should not influence planner to make bad decisions?

---

<div class="post-metadata">

**Author:** ![andrew\_bowman](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/andrew_bowman/32/73_2.png) [@andrew\_bowman](https://community.neo4j.com/u/andrew_bowman)\
**Post date:** [May 13, 2021, 6:28pm UTC](https://community.neo4j.com/t/performance-problems-with-apoc-path-expand-and-apoc-run-cypher/37861/4 "2021-05-13T18:28:05Z")

</div>

It's not a guaranteed thing, since it depends entirely on the rest of the query. It's always a good idea to recheck your plan if there are drastic changes to a query (APOC or not) and if testing reveals major timing differences.

I'll try to take a closer look later and see where we can tune this one.