Extremely slow gremlin traversal retrieval of vertices

[bujoralexandru]

Hello, guys,

I have a problem with the gremlin traversal in a specific use-case.
My orientDB graph database has ~5 millions vertices and edges (40% V/ 60% E).

I try to evaluate a pretty trivial gremlin traversal from my Spring Boot application

Trying to retrieve this traversal result took almost 15 minutes WITH THE .limit(100) pipe enabled.

    @GetMapping("/part/base")
    public List<Part> baseParts() {
        try (final var graph = connectionFactory.newConnection()) {
            logger.info("Started Root Parts fetching");

            List<Vertex> rootParts = graph.traversal()
                    .V().hasLabel("Part")
                    .not(inE("Component_Part"))
// .limit(100)
                    .toList();

            logger.info("Finished Root Parts fetching");
            logger.info("Started Root Parts serializing");

            final var responseBody = rootParts.stream()
                    .map(v -> Part.fromVertex(Part.class, v))
                    .collect(Collectors.toList());

            logger.info("Finished Root Parts serializing");

            return responseBody;
        }
    }

The native query returns the 4.1k results serialized by my serialization method in about 1.6 seconds.

    @GetMapping("/part/base")
    public List<Part> baseParts() {
        try (final var graph = connectionFactory.newConnection()) {
            logger.info("Started Root Parts fetching");

            return graph.querySql(
                    "SELECT * FROM Part WHERE inE('Component_Part').size() = 0"
            ).getRawResultSet()
                    .vertexStream()
                    .parallel()
                    .map(v -> Part.fromVertex(Part.class, v))
                    .collect(Collectors.toList());
        }
    }

Does anyone have a suggestion about what's going on here and why is this traversal so EXTREMELY slow?
If more information is needed, I would be more than willing to share it.

Thank you,
Alex

[misu200]

As a personal rule I find it more "safe" to do things the "Orientdb" way :

1. This means try to use something like MATCH (https://orientdb.com/docs/last/sql/SQL-Match.html ) which is better/faster than Gremlin queries .
2. Java Multi-Model API better/fast/less bugs than TinkerPop 3 interfaces.

I revert to Gremlin queries as a last resort for complicated traversals ..but somehow they always feel slow compared to OrientDB SQL
Rgds,
Mihai

[bujoralexandru]

Thank you for your time and answers!

As a conclusion to this thread, we decided to switch from OrientDB because the overhead of re-learning another query language and translating our existing ones to it would be too much. This doesn't mean we won't pay a close eye on the evolution of OrientDB, having the cool features it already has.