Slowness and behavioral while fetching data from OirentDB using Gremlin API

[arpitdd123]

Hello,

I am currently conducting R&D on fetching data using the Gremlin API from an OrientDB database. While evaluating fetch performance between OrientDB Studio and the Gremlin API, I noticed significant performance issues. After investigating the code in detail, I observed the following behavior:

Consider the queries below and their behavior:

Case1:
Query: SELECT * FROM Table WHERE TableName='ABC'
Traversal: graph.traversal().V().hasLabel("Table").has("TableName", "ABC");
Query generated: SELECT FROM Table WHERE TableName = :param0
Observation: The filter is passed directly to the OrientDB server for execution.

Case2:
Query: SELECT * FROM Table WHERE TableName='ABC' AND TableSize=20
Traversal 1: graph.traversal().V().hasLabel("Table").has("TableName", "ABC").has("TableSize", 20);
Query generated: SELECT FROM Table WHERE TableName = :param0 AND TableSize = :param1

Traversal 2: graph.traversal().V().hasLabel("Table").has("TableName", "ABC").and().has("TableSize", 20);
Query generated: SELECT FROM Table WHERE TableName = :param0 AND TableSize = :param1

Observation: For both the traversals, filter is pushed directly to the OrientDB server, and fetch times between OrientDB Studio and the Gremlin API are similar.

Case3:
Query: SELECT * FROM Table WHERE TableName='ABC' OR TableName='PQR'
Traversal 1: graph.traversal().V().hasLabel("Table").has("TableName", "ABC").or().has("TableName", "PQR");
Query generated: SELECT FROM V
Observation: The entire dataset is fetched and filtered in memory, causing performance differences between OrientDB Studio and Gremlin API.

Traversal 2: graph.traversal().V().hasLabel("Table").or(__.has("TableName", "ABC"), __.has("TableName", "PQR"));
Observation: The API fetches all data from class V in OrientGraph, filtering is done in memory, resulting again in performance differences.

Could someone please help me with the following questions?

1. Is there a way to push OR filters directly to the OrientDB server?
2. Can a combination of AND and OR filters be pushed directly to OrientDB via the query?
3. How can fetch operations be optimized when using Gremlin with OrientDB?
4. Also if Table is parent class and ChildTable is child class for Table, and if we fetch data using gremlin traversal case 1, gremlin does not return vertices from ChildTable, where as on orientdb studio does, why is this behavioral difference?

[tglman]

Hi,

So yes as today the gremlin query get translated to OrientDB query only in limited cases, and as today do not support or filter.

1-2. As today is not possible, but it should be possible to modify the integration with OrientDB to push down more criteria, here is the code that does that https://github.com/orientechnologies/orientdb/blob/develop/gremlin/driver/src/main/java/org/apache/tinkerpop/gremlin/orientdb/traversal/step/sideeffect/OrientGraphStep.java#L160 (for 3.2.x https://github.com/orientechnologies/orientdb-gremlin/blob/3.2.x/driver/src/main/java/org/apache/tinkerpop/gremlin/orientdb/traversal/step/sideeffect/OrientGraphStep.java#L148 ) PR are welcome to support this additional cases
3. one way to improve the performance could be execute your gremlin query server side with as a gremlin script -> Batch Scripts or a gremlin function -> Functions Docs, to do that though you need to use the linked OrientDB specific APIs.
4. This I think is because gremlin re-filter after the query for elements that match the exact label, but I could not find the place in the code, so need a bit deeper double check

I hope this helps