Performance: Slow queries using @cypher directive vs fast in Neo4j browser

[tbwiss]

Describe the bug Customer issue, see Trello card with the same title.

Queries written in my GraphQL schema using the @cypher directive take significantly longer to retrieve data versus the Neo4j browser. This is using latest version of the @neo4j/graphql and apollo-server packages to set up my server.

For example, the below query can take up to 3 minutes with @cypher directive, but returns in 2 seconds in the Neo4j browser:

getETODependencies(initiatives:[String!]!, today:String!):[ETO_Dependency] 
@cypher(
statement: """
MATCH (usFeatureOrDep)<-[:US_DEPENDENCY*1..2]-(dsFeatureOrDep)-[:PARENT]->(dsl4 {Type:'Development / Delivery Milestone', Organization:'Technology'})-[:PARENT]->(dsl3 {Type:'Functional Milestone'})-[:PARENT]->(dsl2 {Type:'Enterprise Milestone'})-[:PARENT]->(dsl1 {Type:'Initiative Milestone'}), (dsl4)-[:HAS_STATE]->(dsl4state:MTDState {Date_Created: $today})
WHERE dsl1.Id in $initiatives
AND dsl4.Status IN ['Implementing','Done','Portfolio Backlog','Analysis']
AND dsFeatureOrDep.Type IN ['Feature', 'Dependency']
AND NOT EXISTS((usFeatureOrDep)-[:PARENT*0..1]->(dsl4))
OPTIONAL MATCH (usl1 {Type: 'Initiative Milestone'})<-[:PARENT]-(usl2 {Type: 'Enterprise Milestone'})<-[:PARENT]-(usl3 {Type: 'Functional Milestone'})<-[:PARENT]-(usl4 {Type: 'Development / Delivery Milestone'})<-[:PARENT*0..1]-(usFeatureOrDep),
(usl4)-[:HAS_STATE]->(usl4state:MTDState {Date_Created: $today})
WHERE usl1.Id in $initiatives
AND dsl4.Id <> usl4.Id
AND usl4.Status IN ['Implementing','Done','Portfolio Backlog','Analysis']
AND usFeatureOrDep.Type IN ['Feature', 'Dependency']
RETURN DISTINCT {
Up_L1_Milestone_Id: usl1.Id,
Up_L1_Milestone_Name: usl1.Title,
Up_L2_Milestone_ID: usl2.Id,
Up_L2_Milestone_Name: usl2.Title,
Up_L2_Planned_End_Date: usl2.Planned_End,
Up_L3_Milestone_ID: usl3.Id,
Up_L3_Milestone_Name: usl3.Title,
Up_L4_Milestone_ID: usl4.Id,
Up_L4_Milestone_Name: usl4.Title,
Up_L4_Planned_End_Date: usl4.Planned_End,
Up_L4_Closed_Date: usl4.Closed_Date,
Up_L4_Date_Completed: usl4.Date_Completed,
Up_L4_Milestone_Health_Status: usl4state.MTD_Health_Status,
Up_L4_Milestone_V1Status: usl4.Status,
Up_L4_Owners: usl4.Owners,
Up_L4_Product_Suite: usl4.Product_Suite,
Up_L4_Team: usl4.Target_Team,
Up_L4_Url: usl4.Url,
Up_L4_IsCapstoneItem: usl4.IsCapstoneItem,
Up_L4_IsKeyMilestone: usl4.Reference = 'Key Milestone',
Up_Item_ID: usFeatureOrDep.Id,
Up_Item_Name: usFeatureOrDep.Title,
Up_Item_Planned_End_Date: usFeatureOrDep.Planned_End,
Up_Item_Closed_Date: usFeatureOrDep.Closed_Date,
Up_Item_Health_Status: CASE (usFeatureOrDep.Status = 'Done' OR NOT(usFeatureOrDep.Closed_Date = '')) WHEN true THEN 'Complete' ELSE 'N/A' END,
Up_Item_V1Status: usFeatureOrDep.Status,
Up_Item_Owners: usFeatureOrDep.Owners,
Up_Item_Product_Suite: usFeatureOrDep.Product_Suite,
Up_Item_Team: usFeatureOrDep.Target_Team,
Up_Item_Url: usFeatureOrDep.Url,
Up_Item_Type: usFeatureOrDep.Type,
Down_L1_Milestone_Id: dsl1.Id,
Down_L1_Milestone_Name: dsl1.Title,
Down_L2_Milestone_ID: dsl2.Id,
Down_L2_Milestone_Name: dsl2.Title,
Down_L2_Planned_End_Date: dsl2.Planned_End,
Down_L3_Milestone_ID: dsl3.Id,
Down_L3_Milestone_Name: dsl3.Title,
Down_L4_Milestone_ID: dsl4.Id,
Down_L4_Milestone_Name: dsl4.Title,
Down_L4_Planned_End_Date: dsl4.Planned_End,
Down_L4_Closed_Date: dsl4.Closed_Date,
Down_L4_Milestone_Health_Status: dsl4state.MTD_Health_Status,
Down_L4_Owners: dsl4.Owners,
Down_L4_Product_Suite: dsl4.Product_Suite,
Down_L4_Team: dsl4.Target_Team,
Down_L4_Url: dsl4.Url,
Down_L4_IsCapstoneItem: dsl4.IsCapstoneItem,
Down_L4_IsKeyMilestone: dsl4.Reference = 'Key Milestone',
Down_Item_ID: dsFeatureOrDep.Id,
Down_Item_Name: dsFeatureOrDep.Title,
Down_Item_Planned_End_Date: dsFeatureOrDep.Planned_End,
Down_Item_Closed_Date: dsFeatureOrDep.Closed_Date,
Down_Item_V1Status: dsFeatureOrDep.Status,
Down_Item_Owners: dsFeatureOrDep.Owners,
Down_Item_Product_Suite: dsFeatureOrDep.Product_Suite,
Down_Item_Team: dsFeatureOrDep.Target_Team,
Down_Item_Url: dsFeatureOrDep.Url,
Down_Item_Type: dsFeatureOrDep.Type,
Dependency_Type: CASE (usl1.Id = dsl1.Id)
WHEN true THEN 'Intra'
WHEN false THEN 'Cross'
ELSE 'External'
END
}
"""
)

Additional context It is likely to be caused by apoc.cypher.runFirstColumn.

[neo4j-team-graphql]

Many thanks for raising this bug report @tbwiss. :bug: We will now attempt to reproduce the bug based on the steps you have provided.

Please ensure that you've provided the necessary information for a minimal reproduction, including but not limited to:

• Type definitions
• Resolvers
• Query and/or Mutation (or multiple) needed to reproduce

If you have a support agreement with Neo4j, please link this GitHub issue to a new or existing Zendesk ticket.

Thanks again! :pray:

[neo4j-team-graphql]

We've been able to confirm this bug using the steps to reproduce that you provided - many thanks @tbwiss! :pray: We will now prioritise the bug and address it appropriately.

[darrellwarde]

This is now done using subqueries, so should be greatly improved.