Using Unwind and Dumping Data in neo4j - Query Optimization

I think your main problem lies in how you merge and match the nodes. Ideally, you always want to have a unique identifier for nodes. I can see that Friend node has a property id, which I will assume is unique for every Friend and Target.

First, you want to create a unique constraint on that property:

CREATE CONSTRAINT ON (f:Friend) ASSERT f.id IS UNIQUE;
CREATE CONSTRAINT ON (f:Target) ASSERT f.id IS UNIQUE;

You want something similar for Location nodes as well. seems like you store both location value and the lowercase value of location, so any of them should be unique for each node.

CREATE CONSTRAINT ON (l:Location) ASSERT l.id IS UNIQUE;

Now you can optimize your query like this:

"""UNWIND [{id: "1235" , uid : "0"}] as user

    UNWIND """+ l +""" as c

    OPTIONAL MATCH (n:Target {id : c.id})

    OPTIONAL MATCH (m:Friend {id : c.id})

    WITH coalesce(n, m) as node,user,c // returns first non-null value

    CALL apoc.do.when(node is null,
    "MERGE (n:Friend {id:c.id}) 
     ON CREATE SET n+= {name:c.name, profile: c.profile, 
           location:c.location, uid : user.uid} 
     RETURN n", '', {c:c,user:user})
    YIELD value
    with coalesce(node, value.n) as y,user,c
    MERGE (u:Target {id: user.id , uid : user.uid})
    """+create_rel+"""

    foreach (sc in c.cityn | 
        merge(cn:Location {location:sc.location}) 
        ON CREATE SET cn.loc_lower = sc.loc_lower 
        merge (y)-[:`located_at`]-(cn))

    """

1. You should avoid running multiple write queries (that can touch the same nodes and relationships) concurrently, as that could cause intermittent TransientErrors, as you have seen. (However, queries that cause transient errors can be retried.)

2. You should be passing user and l to your query as parameters, so that the Cypher planner will only need to compile the query once, and to make the query less prone to Cypher-injection attacks. (Also, there is no need to UNWIND a list that will always have just a single map -- you could have directly used the map via WITH {id: "1235" , uid : "0"} AS user. But, as I mentioned, you should just pass the user map as a parameter so you can efficiently change the user without forcing a recompilation.)

3. To avoid recompilation, you also need to need to make the create_rel string a constant string (so, it might as well be directly in your main query string). Again, you should also pass any variables needed by that as parameters.

4. You should create indexes (or uniqueness constraints) on :Target(id) and :Friend(id), to speed up your MATCH and MERGE clauses.

5. (a) MERGE (u:Target {id: user.id , uid : user.uid}) only needs to be executed once, not per c value. So, it should be executed before the UNWIND.

   (b) Also, it is not strictly necessary for this query to create u, since nothing in the query uses it. So, instead of running this identical MERGE clause once per thread, you should consider taking it out and running it a separate standalone query.

Here is a query that combines suggestions #2 and #5a (but you will have to take care of the others yourself), along with some refactoring using pattern comprehension to avoid unnecessary DB hits:

MERGE (u:Target {id: $user.id, uid: $user.uid})
WITH u
UNWIND $l as c
WITH u, c, [(n:Target {id : c.id})-[*0]-()|n] AS nodeList
WITH u, c, CASE WHEN SIZE(nodeList) = 0 THEN [(n:Friend {id : c.id})-[*0]-()|n] ELSE nodeList END AS nodeList
CALL apoc.do.when(SIZE(nodeList) = 0, 'MERGE (n:Friend {id: c.id, name: c.name, profile: c.profile, location: c.location, uid: user.uid}) RETURN n', 'RETURN nodeList[0] AS n', {c:c,user:$user,nodeList:nodeList}) YIELD value
WITH u, c, value.n AS node
FOREACH (sc IN c.cityn | MERGE (cn:Location {location: sc.location, loc_lower: sc.loc_lower}) MERGE (node)-[:located_at]-(cn))

// Put your parameterized create_rel code here