This is a follow up question to my previous question:Correct way to create json from neo4j rest API.
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:
MATCH (mainCap:Capability{SectorID:"PNU"})
OPTIONAL MATCH (mainCap)-[r1:CapabilityAdopts_Capability]->(adoptsCap1)
OPTIONAL MATCH (adoptsCap1)-[r2:KPI_Capability]->(capKPI)
OPTIONAL MATCH (adoptsCap1)-[r4:Capability_Capability]->(cap1)
OPTIONAL MATCH (cap1)<-[r5:Capability_Capability]-(isPart1)
OPTIONAL MATCH (cap1)-[r6:KPI_Capability]->(KPI1)
OPTIONAL MATCH (cap1)-[r7:Capability_Capability]->(cap2)
OPTIONAL MATCH (cap2)<-[r8:Capability_Capability]-(isPart2)
OPTIONAL MATCH (cap2)-[r9:KPI_Capability]->(KPI2)
OPTIONAL MATCH (cap2)-[r10:Capability_Capability]->(cap3)
OPTIONAL MATCH (cap3)<-[r11:Capability_Capability]-(isPart3)
OPTIONAL MATCH (cap3)-[r12:KPI_Capability]->(KPI3)
OPTIONAL MATCH (mainCap)<-[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`
The above query take 4149 ms to give results. I tried to optimize using distinct. Went through: http://thought-bytes.blogspot.com/2013/01/optimizing-neo4j-cypher-queries.html. Optimize Neo4j Cypher query More relevant: Optimizing Cypher queries with lots of optional relationships
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 Or is this maximum performance I can get out of this query?
Following is my execution plan:
Help appreciated. Thanks
Following is the Solution:
The performance improved drastically after reformatting the query. The cardinalities were causing performance lag. From 4149ms it came down to 400ms:
match (mainCap:Capability{SectorID:"PNU"})
OPTIONAL MATCH (mainCap:Capability)-[r1:CapabilityAdopts_Capability]->(adoptsCap1)
OPTIONAL MATCH (adoptsCap1)-[r2:KPI_Capability]->(capKPI)
OPTIONAL MATCH (adoptsCap1)-[r4:Capability_Capability]->(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)<-[r5:Capability_Capability]-(isPart1)
OPTIONAL MATCH (cap1)-[r6:KPI_Capability]->(KPI1)
OPTIONAL MATCH (cap1)-[r7:Capability_Capability]->(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)<-[r8:Capability_Capability]-(isPart2)
OPTIONAL MATCH (cap2)-[r9:KPI_Capability]->(KPI2)
OPTIONAL MATCH (cap2)-[r10:Capability_Capability]->(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)<-[r11:Capability_Capability]-(isPart3)
OPTIONAL MATCH (cap3)-[r12:KPI_Capability]->(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)<-[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
}
}
mainCap as mainCapin yourWITH. If the alias variable is the same as the variable in the query you can just sayWITH mainCap, isPart, adoptsCap1, etc...– Brian UnderwoodKPI3variable in order to return the first label. If you query the transactions endpoints you can pass "REST" for the "resultDataContents " to get labels. If you can get that with your library depends on your library. See: neo4j.com/docs/stable/… – Brian Underwood