cancel
Showing results for 
Search instead for 
Did you mean: 

Parameterized query performance degradation

KD
Node

 

Hi, 

I have two version of the same query one parameterized and the other non- parameterized. when I run both these queries after some days of running the neo4j database, the parameterized version is about 10 times slower (screenshots attached) then after a restarting  the database both queries execute in about 20 secs.


Can someone please explain what  could be the possible cause of this behavior.

 

Parameterized query :

 

 match (s:CSI{technicalAssetUId:$technicalAssetUId})

    MATCH p=(techAsset1)-[:OFFERS|CONTAINS*2..2]->(ds:DSCOPE) WHERE (techAsset1:CSI OR techAsset1:EUC) 

    and (techAsset1=s or length(shortestPath((techAsset1)<-[:PROVIDES_TO|OFFERS|CONSUMED_BY*0..]-(s)))>0)



    call {

     with ds 

     MATCH (PTYP:PTYP)-[:QUALIFIES]->(ds) where ID(PTYP) in $scopes.qualifiers0[0].qualifiers

     MATCH (DCON:DCON)-[:QUALIFIES]->(ds) where ID(DCON) in $scopes.qualifiers0[1].qualifiers   

     return   ds as filteredDs 

     union  

     with ds 

     MATCH (DCON:DCON)-[:QUALIFIES]->(ds) where ID(DCON) in $scopes.qualifiers1[0].qualifiers 

     MATCH (PTYP:PTYP)-[:QUALIFIES]->(ds) where ID(PTYP) in $scopes.qualifiers1[1].qualifiers 

     MATCH (MSEG:MSEG)-[:QUALIFIES]->(ds) where ID(MSEG) in $scopes.qualifiers1[2].qualifiers 

     MATCH (MGEO:MGEO)-[:QUALIFIES]->(ds) where ID(MGEO) in $scopes.qualifiers1[3].qualifiers  

     return   ds as filteredDs 

     union  

     with ds

     MATCH (DCON:DCON)-[:QUALIFIES]->(ds) where ID(DCON) in $scopes.qualifiers2[0].qualifiers   return   ds as filteredDs 

     union 

     with ds 

     MATCH (MGEO:MGEO)-[:QUALIFIES]->(ds) where ID(MGEO) in $scopes.qualifiers3[0].qualifiers 

     MATCH (DCON:DCON)-[:QUALIFIES]->(ds) where ID(DCON) in $scopes.qualifiers3[1].qualifiers 

     MATCH (MSEG:MSEG)-[:QUALIFIES]->(ds) where ID(MSEG) in $scopes.qualifiers3[2].qualifiers 

     MATCH (PTYP:PTYP)-[:QUALIFIES]->(ds) where ID(PTYP) in $scopes.qualifiers3[3].qualifiers  

     return   ds as filteredDs

    }

    with  distinct filteredDs,s

    match (techAsset1)-[r1:OFFERS]->(do:DOFFERING)-[:CONTAINS]->(filteredDs)-[:OWNED_BY]->(dc:DCONTRACT)-[r2:CONSUMED_BY]-(techAsset2)

    MATCH p = (do)-[r:PROVIDES_TO]->(dc)

    WHERE  (techAsset1:CSI OR techAsset1:EUC) and techAsset2 <> s

    WITH collect(do )+ collect(dc) +collect(techAsset1)  +collect(techAsset2)  as nodes,collect(r1)+ collect(r2) +collect(r) as relationships

    UNWIND nodes AS n

    OPTIONAL MATCH (d:DAUTH)-[:AUTHORIZES]->(n)

    WITH relationships, COLLECT(DISTINCT n) AS nodes, collect(distinct {offering:n.dataOfferingUId,authority:d.dataAuthType,authoritySourceId:d.dataAuthSourceId}) as dataAuthorityList

    UNWIND relationships AS r

    WITH dataAuthorityList, nodes, COLLECT(DISTINCT r) AS relationships

    UNWIND(nodes + relationships + [dataAuthorityList]) as result

    RETURN result

 

KD_0-1663104298140.jpeg

Non parameterized query:

 

match (s:CSI{technicalAssetUId:  "dced8026-ab4b-4943-abc7-7ced099dbf52" })

    MATCH p=(techAsset1)-[:OFFERS|CONTAINS*2..2]->(ds:DSCOPE) WHERE (techAsset1:CSI OR techAsset1:EUC) 

    and (techAsset1=s or length(shortestPath((techAsset1)<-[:PROVIDES_TO|OFFERS|CONSUMED_BY*0..]-(s)))>0)



    call {

     with ds 

     MATCH (PTYP:PTYP)-[:QUALIFIES]->(ds) where ID(PTYP) in [4434]

     MATCH (DCON:DCON)-[:QUALIFIES]->(ds) where ID(DCON) in [5655]

     return   ds as filteredDs 

     union  

     with ds 

     MATCH (DCON:DCON)-[:QUALIFIES]->(ds) where ID(DCON) in [98384]

     MATCH (PTYP:PTYP)-[:QUALIFIES]->(ds) where ID(PTYP) in [77823]

     MATCH (MSEG:MSEG)-[:QUALIFIES]->(ds) where ID(MSEG) in [93773]

     MATCH (MGEO:MGEO)-[:QUALIFIES]->(ds) where ID(MGEO) in [31379]

     return   ds as filteredDs 

     union  

     with ds

     MATCH (DCON:DCON)-[:QUALIFIES]->(ds) where ID(DCON) in [78449] return   ds as filteredDs 

     union 

     with ds 

     MATCH (MGEO:MGEO)-[:QUALIFIES]->(ds) where ID(MGEO) in [123]

     MATCH (DCON:DCON)-[:QUALIFIES]->(ds) where ID(DCON) in [33423]

     MATCH (MSEG:MSEG)-[:QUALIFIES]->(ds) where ID(MSEG) in [5533]

     MATCH (PTYP:PTYP)-[:QUALIFIES]->(ds) where ID(PTYP) in  [42324]

     return   ds as filteredDs

    }

    with  distinct filteredDs,s

    match (techAsset1)-[r1:OFFERS]->(do:DOFFERING)-[:CONTAINS]->(filteredDs)-[:OWNED_BY]->(dc:DCONTRACT)-[r2:CONSUMED_BY]-(techAsset2)

    MATCH p = (do)-[r:PROVIDES_TO]->(dc)

    WHERE  (techAsset1:CSI OR techAsset1:EUC) and techAsset2 <> s

    WITH collect(do )+ collect(dc) +collect(techAsset1)  +collect(techAsset2)  as nodes,collect(r1)+ collect(r2) +collect(r) as relationships

    UNWIND nodes AS n

    OPTIONAL MATCH (d:DAUTH)-[:AUTHORIZES]->(n)

    WITH relationships, COLLECT(DISTINCT n) AS nodes, collect(distinct {offering:n.dataOfferingUId,authority:d.dataAuthType,authoritySourceId:d.dataAuthSourceId}) as dataAuthorityList

    UNWIND relationships AS r

    WITH dataAuthorityList, nodes, COLLECT(DISTINCT r) AS relationships

    UNWIND(nodes + relationships + [dataAuthorityList]) as result

    RETURN result

 

 

KD_1-1663104298149.jpeg

 

 

I experienced this  using the below clients;

Neo4j Browser version: 4.4.2

JavaScript driver; neo4j-driver  version: 4.4.6

python driver version; neo4j   version:  4.3.4

 

Please see some details for the neo4j database if that is helpful.

Neo4j Server version: 4.3.10 (enterprise)
dbms.memory.heap.initial_size=32768m
dbms.memory.heap.max_size=32768m
dbms.memory.pagecache.size=12288m

 

Thanks!

0 REPLIES 0
Nodes 2022
Nodes
NODES 2022, Neo4j Online Education Summit

On November 16 and 17 for 24 hours across all timezones, you’ll learn about best practices for beginners and experts alike.