Performance Tuning while MATCH with ORDER BY

[OSI-Califronia]

I have following setup in orientDB

Schema:

Relationships:

Group(G)---ASSIGNED-->VEHICLE(V)
COUPLED(C)---ASSIGNED-->VEHICLE(V)
VEHICLE(V)---ASSIGNED-->BOX(B)

PORTAL(P)---LINKED-->GROUP(G)
PORTAL(P)---LINKED-->COUPLED(G)
PORTAL(P)---LINKED-->VEHICLE(G)
PORTAL(P)---LINKED-->BOX(G)

All relationships contain a temporal range with 'assignedFrom' and 'assignedUntil' as property fields which should be respect by every query.

Query:

We try to load a paginated list of vehicles with it's associations to direct other nodes by respecting the edge temporal range (validity)

SELECT DISTINCT vehicles.id, set(portalToVehicle.name), set(groups):{id, name} as groups, set(boxes):{id, externalId} as boxes
FROM (
MATCH
{class: PORTAL, where: (id in [127])}
.inE('LINKED'){as: portalToVehicle, where: (assignedFrom < sysdate() and assignedUntil > sysdate())}
.outV(){class: VEHICLE, as: vehicles},
{as:vehicles}
.outE('ASSIGNED'){as: vehicleToBox, where: (assignedFrom < sysdate() and assignedUntil > sysdate())}
.inV(){class: BOX, as: boxes}
.outE('LINKED'){as: boxToPortal, where: (assignedFrom < sysdate() and assignedUntil > sysdate())}
.inV(){class: PORTAL, where: (id in [127])},
{as:vehicles}
.inE('ASSIGNED'){optional: true, as: vehicleToGroup, where: (assignedFrom < sysdate() and assignedUntil > sysdate())}
.outV(){optional: true, class: GROUP, as: groups}
.outE('LINKED'){optional: true, as: groupToPortal, where: (assignedFrom < sysdate() and assignedUntil > sysdate())}
.inV(){optional: true, class: PORTAL, where: (id in [127])},
{as:vehicles}
.inE('ASSIGNED'){optional: true, as: vehicleToCoupled, where: (assignedFrom < sysdate() and assignedUntil > sysdate())}
.outV(){optional: true, class: COUPLED, as: coupled }
.outE('LINKED'){optional: true, as: coupledToPortal, where: (assignedFrom < sysdate() and assignedUntil > sysdate())}
.inV(){optional: true, class: PORTAL, where: (id in [127])}
RETURN vehicles, boxes, groups, coupled, portalToVehicle
)
-- (Optional) WHERE clause
GROUP BY vehicles
ORDER BY portalToVehicle.name
LIMIT 10

Counts:
150T LINKED
220T ASSIGNED
954T BOX, VEHICLE, GROUP, COUPLED

Graph Traversal:
PORTAL
--[LINKED]--VEHICLE
--[ASSIGNED]--BOX
--[LINKED]--PORTAL
--[ASSIGNED]--GROUP
--[LINKED]--PORTAL
--[ASSIGNED]--COUPLED
--[LINKED]--PORTAL

Execution Time takes up to 10 seconds. We try to bring it down to under 200 ms.

Does Anybody have an idea how to speed up this query?

[tglman]

Hi,

Could you run the query with "profile ..." and post somewhere the result json ?