slow query

[thomas_stuempfi]

I have a very slow query, that was ok for a long time (less that a second). But now takes minutes. The query is fast until the apoc CALL.

The Business background is revising a node. ( (n)BusinessObject: PartId=BO1370, Revision:00, ... should become (nr) BusinessObject: PartId=BO1370, Revision:01, ... ) the new node should be related to the old one by the "Predecessor" relation and all related nodes except the ones that are related by "Predecessor" or "hasStatus" should also be related to the new node (nr).

the part until the first apoc Call executes very fast.

Any hint what might be going wrong.
Is it possible to create a relation with a dynamic type? e.g. (nr)-[r1:type(r)]-(c) ?

regards

Thomas

match(n:BusinessObject) where n.PartId='BO1370' and nr.Revision='00'with n 
create (nr:BusinessObject) set nr=n, nr.lastmodifyinguser='thomas', nr.Revision='01' with n,nr 
match (n)-[r]->(c) where not(type(r)='Predecessor' or type(r)='hasStatus') with n,c,r,nr 
merge (nr)-[r1:anonrel]->(c)  set r1=r with r1,n,nr,r 
CALL apoc.refactor.setType(r1, type(r)) yield input,output,error with n,nr 
CALL apoc.create.addLabels(nr, labels(n)) yield node with n,nr 
merge (n)-[r2:Predecessor]->(nr) return n,nr

[glilienfield]

I don't know if this is any better, but I did reduce the number of operations. 

match(n:BusinessObject) 
where n.PartId='BO1370' and n.Revision='00'

call apoc.create.node(['BusinessObject']+labels(n), n{.*, lastmodifyinguser:'thomas', Revision:'01' }) yield node as nr

with n,nr
merge (n)-[r2:Predecessor]->(nr) 

with n,nr
match (n)-[r]->(c) 
where not type(r) in ['Predecessor', 'hasStatus']

with n,c,r,nr 
CALL apoc.create.relationship(nr, type(r), r, c) yield rel

return n,nr

[thomas_stuempfi]

Hi glilienfield,

thank you for the reply. I like your rewrite of the query. Though it does not solve the problem.

I figured out that the merge call already made problems. Well, the database has ~100.000.000 nodes. ~10% are BusinessObjects. I try to nail down the problem with the explain and create indexes, but i really have to learn about Query Planner. For now, I am a bit lost in the explain output of the query.

[glilienfield]

Can you post the query plan diagram?

[thomas_stuempfi]

This is the original query. and Plan and Query

match(n:BusinessObject) where n.OrganizationalView='--' and n.CNr='C0' and n.SerialNr='--' and n.BO_Revision='00' and n.PartId='BO1370' with n 
create (nr) set nr=n set nr.modifydate='1673255480060', nr.lastmodifyinguser='thomas stuempfig', nr.BO_Revision='05' with n,nr 
match (n)-[r]->(c) where not(type(r)='Predecessor' or type(r)='hasStatus') with n,c,r,nr 
merge (nr)-[r1:anonrel]->(c)  set r1=r with r1,n,nr,r 
CALL apoc.refactor.setType(r1, type(r)) yield input,output,error with n,nr 
CALL apoc.create.addLabels(nr, labels(n)) yield node with n,nr 
 merge (n)-[r2:Predecessor]->(nr) return n,nr

query plan

[glilienfield]

Can you provide it for the new query?   Can you provide the ‘profile’ instead?  It has actual statistics instead of just estimates, since it executes the query. 

[thomas_stuempfi]

Hi,

i am running the query now for more than 15min with profile.
The java Server Process on OS is consuming a large amount of memory and obviously is swapping.

regards

Thomas

[thomas_stuempfi]

Hello,

i decomposed your call to find the bottleneck and found that already the first merge takes a long time. 

merge (n)-[r2:Predecessor]->(nr) 

[glilienfield]

That is very odd it would take long.  You could convert it to a create, since you know it will be new because ‘nr’ is created in the script.  You can see if this speeds it up. 

[thomas_stuempfi]

a merge 

 (f:foo)-[r:testrel]->(b:Bar) return f,r,b

is finished after a few milliseconds.

[thomas_stuempfi]

Finaly I found the solution.

I dropped the index in BusinessObject and recreated it. Since then the query is <10ms in average

Thank you for your effort