Very low traverse performance

[PrinceKorwin]

OrientDB Version: orientdb-community-2.2.17

Java Version: 1.7

OS: Windows / Mac OS

Server: Embedded in-memory only

Data Set: about 65.000 documents, graph (Vertex, Edges not used) only Documents with LINKS & LINKLIST

Description

I try use traversing for my Data Set (not a big, this is a just sample) and got a very low traverse performance. Data Set just a hierarchical product offering for e-business.
Data schema looks like:
ProductOffer -> ProductOfferReference -> ProductOffer;
ProductOffer -> ProductOfferCategoryReference -> CategoryToProductOfferRerefence -> ProductOffer;
ProductOffer -> ProductOfferCategoryReference -> CategoryToCategoryReference -> CategoryToProductOfferRerefence -> ProductOffer;

So. I just has a "direct" relation between offering and relation via category.
Request simple:
SELECT count(*) FROM (TRAVERSE offeringsRelationships, childProductOffering, productOfferingToCategoryRelationship, offeringCategory, parentCategoryToCategoryRelationships, childProductOfferingCategory, parentCategoryToOfferingRelationships FROM (SELECT FROM ProductOffering WHERE topOffer = TRUE))

I try get Top Product offering and traverse down by all available relation.

Request waste 2-3 second!
Total ProductOffering about 5000. Relations about 60000.
Request returns about 65000 records as expected but very slow!

Database created in-memory, WAL is disabled. I not use remote connection. OrientDB started in Embedded mode.

3 second for traversing 60.000 entities in in-memory mode is very slow!
Our Production DB contains x10 records and it's looks imposible to us move from SQL + custom in-memory cache to OrientDB.

I do Proof of Concept and for now it's looks not good for out business goals.

Can you help me?

[wolf4ood]

hi @PrinceKorwin

how much does it take this sub select?

SELECT FROM ProductOffering WHERE topOffer = TRUE

[PrinceKorwin]

I has index on it:
CREATE INDEX ProductOffering_topOffer ON ProductOffering (topOffer) NOTUNIQUE

Select: SELECT FROM ProductOffering WHERE topOffer = TRUE
executes in 0.001-0.002 sec and returns about 400 records

[wolf4ood]

ok @PrinceKorwin

how many records are returned?

[wolf4ood]

Can you also post the explain of

SELECT count(*) FROM (TRAVERSE offeringsRelationships, childProductOffering, productOfferingToCategoryRelationship, offeringCategory, parentCategoryToCategoryRelationships, childProductOfferingCategory, parentCategoryToOfferingRelationships FROM (SELECT FROM ProductOffering WHERE topOffer = TRUE))

[PrinceKorwin]

EXPLAIN SELECT ....

Result:

Profiled command '
{
limit:-1,
projectionElapsed:0,
optimizationElapsed:1375,
path:,
current:#91:351,
fetchingFromTargetElapsed:6,
stack:[0],
depth:0,
elapsed:1374.6648,
resultType:collection,
resultSize:1}
' in 1.385000 sec(s):
{
"@type":"d",
"@version":0,
"limit":-1,
"projectionElapsed":0,
"optimizationElapsed":1375,
"path":"",
"current":"#91:351",
"fetchingFromTargetElapsed":6,
"stack":[],
"depth":0,
"elapsed":1374.6648,
"resultType":"collection",
"resultSize":1,
"@fieldTypes":"projectionElapsed=l,
optimizationElapsed=l,
current=x,
fetchingFromTargetElapsed=l,
stack=u,
elapsed=f"
}

[PrinceKorwin]

how many records are returned?

returns about 400 records

[wolf4ood]

can you paste also this ?

Explain SELECT * FROM (TRAVERSE offeringsRelationships, childProductOffering, productOfferingToCategoryRelationship, offeringCategory, parentCategoryToCategoryRelationships, childProductOfferingCategory, parentCategoryToOfferingRelationships FROM (SELECT FROM ProductOffering WHERE topOffer = TRUE))

[PrinceKorwin]

{
    "result": [
        {
            "@type": "d",
            "@version": 0,
            "evaluated": 64955,
            "path": "",
            "current": "#46:27150",
            "fetchingFromTargetElapsed": 1684,
            "tips": [
                "Query 'SELECT * FROM (TRAVERSE offeringsRelationships, childProductOffering, productOfferingToCategoryRelationship, offeringCategory, parentCategoryToCategoryRelationships, childProductOfferingCategory, parentCategoryToOfferingRelationships FROM (SELECT FROM ProductOffering WHERE topOffer = TRUE))' returned a result set with more than 10000 records. Check if you really need all these records, or reduce the resultset by using a LIMIT to improve both performance and used RAM"
            ],
            "documentReads": 64955,
            "stack": [
                
            ],
            "recordReads": 64955,
            "depth": 0,
            "elapsed": 1836.6239,
            "resultType": "collection",
            "resultSize": 64955,
            "@fieldTypes": "evaluated=l,current=x,fetchingFromTargetElapsed=l,documentReads=l,stack=u,recordReads=l,elapsed=f"
        }
    ],
    "warnings": [
        "Query 'SELECT * FROM (TRAVERSE offeringsRelationships, childProductOffering, productOfferingToCategoryRelationship, offeringCategory, parentCategoryToCategoryRelationships, childProductOfferingCategory, parentCategoryToOfferingRelationships FROM (SELECT FROM ProductOffering WHERE topOffer = TRUE))' returned a result set with more than 10000 records. Check if you really need all these records, or reduce the resultset by using a LIMIT to improve both performance and used RAM"
    ],
    "notification": "Query executed in 1.845 sec. Returned 1 record(s)"
}

[wolf4ood]

hi @PrinceKorwin

do you have a loading script that i can use to recreate more/less this example?

[PrinceKorwin]

Sorry. Data Set under NDA. I can't send it to you. I will think about creation script to generate the same graph structure,

[wolf4ood]

@PrinceKorwin
that would be awesome thanks