title: Optimizing Cypher Query for performance
body: <p>This is a follow up question to my previous question:<a href="https://stackoverflow.com/questions/32489549/correct-way-to-create-json-from-neo4j-rest-api#">Correct way to create json from neo4j rest API</a>.</p>
  
  <p>I was able to get the JSON exactly in the format I required with the help provided to me by @Michael Hunger. Following is the code:</p>
  
  <pre><code>    MATCH (mainCap:Capability{SectorID:"PNU"})  
      OPTIONAL MATCH  (mainCap)-[r1:CapabilityAdopts_Capability]-&gt;(adoptsCap1)    
      OPTIONAL MATCH  (adoptsCap1)-[r2:KPI_Capability]-&gt;(capKPI)
      OPTIONAL MATCH  (adoptsCap1)-[r4:Capability_Capability]-&gt;(cap1)         
      OPTIONAL MATCH (cap1)&lt;-[r5:Capability_Capability]-(isPart1)
      OPTIONAL MATCH (cap1)-[r6:KPI_Capability]-&gt;(KPI1)
      OPTIONAL MATCH (cap1)-[r7:Capability_Capability]-&gt;(cap2)
      OPTIONAL MATCH (cap2)&lt;-[r8:Capability_Capability]-(isPart2)
      OPTIONAL MATCH (cap2)-[r9:KPI_Capability]-&gt;(KPI2)
      OPTIONAL MATCH (cap2)-[r10:Capability_Capability]-&gt;(cap3)
      OPTIONAL MATCH (cap3)&lt;-[r11:Capability_Capability]-(isPart3)
      OPTIONAL MATCH (cap3)-[r12:KPI_Capability]-&gt;(KPI3)
  
      OPTIONAL MATCH (mainCap)&lt;-[r3:Capability_Capability]-(isPart:Capability)                                                                                                        
      with
          mainCap as mainCap, 
          isPart as isPart,
          adoptsCap1 as adoptsCap1,
          cap1 as cap1,
          cap2 as cap2,
          cap3 as cap3,
          KPI2 as KPI2,
          isPart2 as isPart2,
          KPI1 as KPI1,
          isPart1 as isPart1,
          capKPI as capKPI,
          CASE WHEN KPI3 IS NOT NULL THEN
          COLLECT(DISTINCT
            {
              _id: KPI3.ExternalID,
              type: labels(KPI3)[0],
              name: KPI3.Name,
              description: KPI3.Description,
              sectorID: KPI3.SectorID,
              CCO: KPI3.CCO,
              CFO: KPI3.CFO,
              SVPHR: KPI3.SVPHR,
              area: KPI3.Area,
              CEO: KPI3.CEO,
              CIO: KPI3.CIO,
              COO: KPI3.COO       
            }
          )
        ELSE
          []
          END as cpbltyIsMsrdByKpiPttrn3,
  
          CASE WHEN isPart3 IS NOT NULL THEN
              COLLECT(DISTINCT
                {
                _id: isPart3.ExternalID,
                  type: labels(isPart3)[0]                        
                }
              )
            ELSE
              []
              END as isPartOfCapability3      
  
                      with
  
                      mainCap as mainCap, 
                      isPart as isPart,
                      adoptsCap1 as adoptsCap1,
                      cap1 as cap1,
                      cap2 as cap2,                   
                      KPI1 as KPI1,
                      isPart1 as isPart1,
                      capKPI as capKPI,
                      CASE WHEN cap3 IS NOT NULL THEN
                      COLLECT(DISTINCT
                        {
                        _id: cap3.ExternalID,
                          type: labels(cap3)[0],
                          name: cap3.Name,
                          description: cap3.Overview,
                          sectorID: cap3.SectorID,
                          cpbltyIsMsrdByKpiPttrn:cpbltyIsMsrdByKpiPttrn3,
                          isPartOfCapability:isPartOfCapability3
                        }
                      )
                    ELSE
                      []
                      END as consistsOfCapability3,           
  
  
                      CASE WHEN KPI2 IS NOT NULL THEN
                      COLLECT(DISTINCT
                        {
                          _id: KPI2.ExternalID,
                          type: labels(KPI2)[0],
                          name: KPI2.Name,
                          description: KPI2.Description,
                          sectorID: KPI2.SectorID,
                          CCO: KPI2.CCO,
                          CFO: KPI2.CFO,
                          SVPHR: KPI2.SVPHR,
                          area: KPI2.Area,
                          CEO: KPI2.CEO,
                          CIO: KPI2.CIO,
                          COO: KPI2.COO       
                        }
                      )
                    ELSE
                      []
                      END as cpbltyIsMsrdByKpiPttrn2,
  
                      CASE WHEN isPart2 IS NOT NULL THEN
                          COLLECT(DISTINCT
                            {
                            _id: isPart2.ExternalID,
                              type: labels(isPart2)[0]                        
                            }
                          )
                        ELSE
                          []
                          END as isPartOfCapability2              
  
                              with mainCap as mainCap,
                                  adoptsCap1 as adoptsCap1,
                                  isPart as isPart,
                                  cap1 as cap1,                               
                                  capKPI as capKPI,
                              CASE WHEN cap2 IS NOT NULL THEN
                              COLLECT(DISTINCT
                                {
                                _id: cap2.ExternalID,
                                  type: labels(cap2)[0],
                                  name: cap2.Name,
                                  description: cap2.Overview,
                                  sectorID: cap2.SectorID,
                                  cpbltyIsMsrdByKpiPttrn:cpbltyIsMsrdByKpiPttrn2,
                                  isPartOfCapability:isPartOfCapability2,
                                  consistsOfCapability:consistsOfCapability3
                                }
                              )
                            ELSE
                              []
                              END as consistsOfCapability2,                   
  
  
                              CASE WHEN KPI1 IS NOT NULL THEN
                              COLLECT(DISTINCT
                                {
                                  _id: KPI1.ExternalID,
                                  type: labels(KPI1)[0],
                                  name: KPI1.Name,
                                  description: KPI1.Description,
                                  sectorID: KPI1.SectorID,
                                  CCO: KPI1.CCO,
                                  CFO: KPI1.CFO,
                                  SVPHR: KPI1.SVPHR,
                                  area: KPI1.Area,
                                  CEO: KPI1.CEO,
                                  CIO: KPI1.CIO,
                                  COO: KPI1.COO           
                                }
                              )
                            ELSE
                              []
                              END as cpbltyIsMsrdByKpiPttrn1,
  
                              CASE WHEN isPart1 IS NOT NULL THEN
                                  COLLECT(DISTINCT
                                    {
                                    _id: isPart1.ExternalID,
                                      type: labels(isPart1)[0]                        
                                    }
                                  )
                                ELSE
                                  []
                                  END as isPartOfCapability1  
  
                                      with mainCap as mainCap,
                                      adoptsCap1 as adoptsCap1,
                                      isPart as isPart,
                                      CASE WHEN cap1 IS NOT NULL THEN
                                      COLLECT(DISTINCT
                                        {
                                        _id: cap1.ExternalID,
                                          type: labels(cap1)[0],
                                          name: cap1.Name,
                                          description: cap1.Overview,
                                          sectorID: cap1.SectorID,
                                          cpbltyIsMsrdByKpiPttrn:cpbltyIsMsrdByKpiPttrn1,
                                          isPartOfCapability:isPartOfCapability1,
                                          consistsOfCapability:consistsOfCapability2
                                        }
                                      )
                                      ELSE
                                      []
                                      END as consistsOfCapability1,
  
                                      CASE WHEN capKPI IS NOT NULL THEN
                                          COLLECT(DISTINCT
                                            {
                                              _id: capKPI.ExternalID,
                                              type: labels(capKPI)[0],
                                              name: capKPI.Name,
                                              description: capKPI.Description,
                                              sectorID: capKPI.SectorID,
                                              CCO: capKPI.CCO,
                                              CFO: capKPI.CFO,
                                              SVPHR: capKPI.SVPHR,
                                              area: capKPI.Area,
                                              CEO: capKPI.CEO,
                                              CIO: capKPI.CIO,
                                              COO: capKPI.COO 
                                            }
                                          )
                                        ELSE
                                          []
                                          END as cpbltyIsMsrdByKpiPttrn
  
          return {
                          _id:mainCap.ExternalID, 
                          type:labels(mainCap)[0],
                          name:mainCap.Name,
                          adoptsCapability: 
  
                                  CASE WHEN adoptsCap1 IS NOT NULL THEN
                                                  COLLECT(DISTINCT
                                                    {
                                                      _id: adoptsCap1.ExternalID,
                                                      type: labels(adoptsCap1)[0],
                                                      name: adoptsCap1.Name,
                                                      description: adoptsCap1.Overview,
                                                      sectorID: adoptsCap1.SectorID,
                                                      cpbltyIsMsrdByKpiPttrn:cpbltyIsMsrdByKpiPttrn,
                                                      consistsOfCapability:consistsOfCapability1                                                  
                                                    }
                                                  )
                                                  ELSE
                                                  []
                                                  END,
                          isPartOfCapability:             
                                  CASE WHEN isPart IS NOT NULL THEN
                                              COLLECT(DISTINCT
                                                {
                                                _id: isPart.ExternalID,
                                                  type: labels(isPart)[0]                     
                                                }
                                              )
                                            ELSE
                                              []
                                              END 
  
  
  
          }as `col2`
  </code></pre>
  
  <p>The above query take <strong>4149 ms</strong> to give results. I tried to optimize using distinct. Went through: 
  <a href="http://thought-bytes.blogspot.com/2013/01/optimizing-neo4j-cypher-queries.html" rel="nofollow noreferrer">http://thought-bytes.blogspot.com/2013/01/optimizing-neo4j-cypher-queries.html</a>.
  <a href="https://stackoverflow.com/questions/15146883/optimize-neo4j-cypher-query">Optimize Neo4j Cypher query</a>
  More relevant: <a href="https://stackoverflow.com/questions/23721048/optimizing-cypher-queries-with-lots-of-optional-relationships">Optimizing Cypher queries with lots of optional relationships</a></p>
  
  <p><strong>Question: Is it possible to optimize and reduce the time to around ~2 seconds further? I have an angularjs UI which has a menu in bound to data coming from neo. its is currently taking 4.4 seconds to get data from neo</strong>  Or is this maximum performance I can get out of this query?</p>
  
  <p>Following is my execution plan:
  <a href="https://i.stack.imgur.com/6dEgg.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/6dEgg.png" alt="enter image description here"></a></p>
  
  <p>Help appreciated. Thanks        </p>
  
  <p><strong>Following is the Solution:</strong></p>
  
  <p>The performance improved drastically after reformatting the query. The cardinalities were causing performance lag. <strong>From 4149ms it came down to 400ms</strong>: </p>
  
  <pre><code>    match (mainCap:Capability{SectorID:"PNU"}) 
                  OPTIONAL MATCH  (mainCap:Capability)-[r1:CapabilityAdopts_Capability]-&gt;(adoptsCap1) 
                                              OPTIONAL MATCH  (adoptsCap1)-[r2:KPI_Capability]-&gt;(capKPI)
                                              OPTIONAL MATCH  (adoptsCap1)-[r4:Capability_Capability]-&gt;(cap1) 
  
                                              with mainCap, adoptsCap1, cap1,
                                              CASE WHEN capKPI IS NOT NULL THEN
                                          COLLECT(DISTINCT
                                            {
                                              _id: capKPI.ExternalID,
                                              type: labels(capKPI)[0],
                                              name: capKPI.Name,
                                              description: capKPI.Description,
                                              sectorID: capKPI.SectorID,
                                              CCO: capKPI.CCO,
                                              CFO: capKPI.CFO,
                                              SVPHR: capKPI.SVPHR,
                                              area: capKPI.Area,
                                              CEO: capKPI.CEO,
                                              CIO: capKPI.CIO,
                                              COO: capKPI.COO 
                                            }
                                          )
                                        ELSE
                                          []
                                          END as cpbltyIsMsrdByKpiPttrn
  
  
                                          OPTIONAL MATCH (cap1)&lt;-[r5:Capability_Capability]-(isPart1)
                                          OPTIONAL MATCH (cap1)-[r6:KPI_Capability]-&gt;(KPI1)
                                          OPTIONAL MATCH (cap1)-[r7:Capability_Capability]-&gt;(cap2)
  
                                          with mainCap, adoptsCap1, cap1,cpbltyIsMsrdByKpiPttrn,cap2,
                                              CASE WHEN KPI1 IS NOT NULL THEN
                                              COLLECT(DISTINCT
                                                {
                                                  _id: KPI1.ExternalID,
                                                  type: labels(KPI1)[0],
                                                  name: KPI1.Name,
                                                  description: KPI1.Description,
                                                  sectorID: KPI1.SectorID,
                                                  CCO: KPI1.CCO,
                                                  CFO: KPI1.CFO,
                                                  SVPHR: KPI1.SVPHR,
                                                  area: KPI1.Area,
                                                  CEO: KPI1.CEO,
                                                  CIO: KPI1.CIO,
                                                  COO: KPI1.COO           
                                                }
                                              )
                                            ELSE
                                              []
                                              END as cpbltyIsMsrdByKpiPttrn1,
  
                                              CASE WHEN isPart1 IS NOT NULL THEN
                                                  COLLECT(DISTINCT
                                                    {
                                                    _id: isPart1.ExternalID,
                                                      type: labels(isPart1)[0]                        
                                                    }
                                                  )
                                                ELSE
                                                  []
                                                  END as isPartOfCapability1  
  
  
                                          OPTIONAL MATCH (cap2)&lt;-[r8:Capability_Capability]-(isPart2)
                                          OPTIONAL MATCH (cap2)-[r9:KPI_Capability]-&gt;(KPI2)
                                          OPTIONAL MATCH (cap2)-[r10:Capability_Capability]-&gt;(cap3)
  
                                          with mainCap, adoptsCap1, cap1,cpbltyIsMsrdByKpiPttrn,cap2,cpbltyIsMsrdByKpiPttrn1,isPartOfCapability1,cap3,
  
                                          CASE WHEN KPI2 IS NOT NULL THEN
                                          COLLECT(DISTINCT
                                            {
                                              _id: KPI2.ExternalID,
                                              type: labels(KPI2)[0],
                                              name: KPI2.Name,
                                              description: KPI2.Description,
                                              sectorID: KPI2.SectorID,
                                              CCO: KPI2.CCO,
                                              CFO: KPI2.CFO,
                                              SVPHR: KPI2.SVPHR,
                                              area: KPI2.Area,
                                              CEO: KPI2.CEO,
                                              CIO: KPI2.CIO,
                                              COO: KPI2.COO       
                                            }
                                          )
                                        ELSE
                                          []
                                          END as cpbltyIsMsrdByKpiPttrn2,
  
                                          CASE WHEN isPart2 IS NOT NULL THEN
                                              COLLECT(DISTINCT
                                                {
                                                _id: isPart2.ExternalID,
                                                  type: labels(isPart2)[0]                        
                                                }
                                              )
                                            ELSE
                                              []
                                              END as isPartOfCapability2
  
                                          OPTIONAL MATCH (cap3)&lt;-[r11:Capability_Capability]-(isPart3)                                                                                                                                    
                                          OPTIONAL MATCH (cap3)-[r12:KPI_Capability]-&gt;(KPI3)
  
                                          with mainCap, adoptsCap1, cap1,cpbltyIsMsrdByKpiPttrn,cap2,cpbltyIsMsrdByKpiPttrn1,isPartOfCapability1,cpbltyIsMsrdByKpiPttrn2,isPartOfCapability2,cap3,
  
                                          CASE WHEN KPI3 IS NOT NULL THEN
                                          COLLECT(DISTINCT
                                            {
                                              _id: KPI3.ExternalID,
                                              type: labels(KPI3)[0],
                                              name: KPI3.Name,
                                              description: KPI3.Description,
                                              sectorID: KPI3.SectorID,
                                              CCO: KPI3.CCO,
                                              CFO: KPI3.CFO,
                                              SVPHR: KPI3.SVPHR,
                                              area: KPI3.Area,
                                              CEO: KPI3.CEO,
                                              CIO: KPI3.CIO,
                                              COO: KPI3.COO       
                                            }
                                          )
                                        ELSE
                                          []
                                          END as cpbltyIsMsrdByKpiPttrn3,
  
                                          CASE WHEN isPart3 IS NOT NULL THEN
                                              COLLECT(DISTINCT
                                                {
                                                _id: isPart3.ExternalID,
                                                  type: labels(isPart3)[0]                        
                                                }
                                              )
                                            ELSE
                                              []
                                              END as isPartOfCapability3  
  
  
  
                                          with mainCap, adoptsCap1, cap1,cpbltyIsMsrdByKpiPttrn,cap2,cpbltyIsMsrdByKpiPttrn1,isPartOfCapability1,cpbltyIsMsrdByKpiPttrn2,isPartOfCapability2,
  
                                          CASE WHEN cap3 IS NOT NULL THEN
                                          COLLECT(DISTINCT
                                            {
                                            _id: cap3.ExternalID,
                                              type: labels(cap3)[0],
                                              name: cap3.Name,
                                              description: cap3.Overview,
                                              sectorID: cap3.SectorID,
                                              cpbltyIsMsrdByKpiPttrn:cpbltyIsMsrdByKpiPttrn3,
                                              isPartOfCapability:isPartOfCapability3
                                            }
                                          )
                                        ELSE
                                          []
                                          END as consistsOfCapability3
  
  
                                      with mainCap, adoptsCap1, cap1,cpbltyIsMsrdByKpiPttrn,cpbltyIsMsrdByKpiPttrn1,isPartOfCapability1,
  
                                      CASE WHEN cap2 IS NOT NULL THEN
                                      COLLECT(DISTINCT
                                        {
                                        _id: cap2.ExternalID,
                                          type: labels(cap2)[0],
                                          name: cap2.Name,
                                          description: cap2.Overview,
                                          sectorID: cap2.SectorID,
                                          cpbltyIsMsrdByKpiPttrn:cpbltyIsMsrdByKpiPttrn2,
                                          isPartOfCapability:isPartOfCapability2,
                                          consistsOfCapability:consistsOfCapability3
                                        }
                                      )
                                    ELSE
                                      []
                                      END as consistsOfCapability2
  
                                      with mainCap, adoptsCap1, cpbltyIsMsrdByKpiPttrn,
  
                                      CASE WHEN cap1 IS NOT NULL THEN
                                      COLLECT(DISTINCT
                                        {
                                        _id: cap1.ExternalID,
                                          type: labels(cap1)[0],
                                          name: cap1.Name,
                                          description: cap1.Overview,
                                          sectorID: cap1.SectorID,
                                          cpbltyIsMsrdByKpiPttrn:cpbltyIsMsrdByKpiPttrn1,
                                          isPartOfCapability:isPartOfCapability1,
                                          consistsOfCapability:consistsOfCapability2
                                        }
                                      )
                                      ELSE
                                      []
                                      END as consistsOfCapability1
  
  
  OPTIONAL MATCH (mainCap)&lt;-[r3:Capability_Capability]-(isPart:Capability)
  
  return {
  model:{ 
  
      _id:mainCap.ExternalID, 
      type:labels(mainCap)[0],
      name:mainCap.Name,
  
      adoptsCapability: 
  
              CASE WHEN adoptsCap1 IS NOT NULL THEN
                              COLLECT(DISTINCT
                                {
                                  _id: adoptsCap1.ExternalID,
                                  type: labels(adoptsCap1)[0],
                                  name: adoptsCap1.Name,
                                  description: adoptsCap1.Overview,
                                  sectorID: adoptsCap1.SectorID,
                                  cpbltyIsMsrdByKpiPttrn:cpbltyIsMsrdByKpiPttrn,
                                  consistsOfCapability:consistsOfCapability1
  
                                }
                              )
                              ELSE
                              []
                              END,
  
                              isPartOfCapability:             
                                  CASE WHEN isPart IS NOT NULL THEN
                                              COLLECT(DISTINCT
                                                {
                                                _id: isPart.ExternalID,
                                                  type: labels(isPart)[0]                     
                                                }
                                              )
                                            ELSE
                                              []
                                              END 
  
  
  
  
      }
  
  }
  </code></pre>