I got the below queries working faster by turning the query around:
select expand(in('SUPP_COUNTRY').in('ARTICLE_SUPP_NR')) from COUNTRY where _id
= 'GB'
...
20 item(s) found. Query executed in 0.004 sec(s).
and
select in('SUPP_COUNTRY').in('ARTICLE_SUPP_NR')[email protected]() from COUNTRY where
_id = 'GB'
..
1 item(s) found. Query executed in 0.011 sec(s).
So that is already much better (result is about 500 edges), but when counting
on more hits, things are still not fast:
orientdb {petshop}> select in('SUPP_COUNTRY').in('ARTICLE_SUPP_NR')[email protected]()
from COUNTRY where _id = 'NL'
----+-----+-----
# |@RID |in
----+-----+-----
0 |#-2:1|79918
----+-----+-----
1 item(s) found. Query executed in 1.873 sec(s).
Running a second time gives 0.558 sec, is there a warmup or something?
But anyway, 0.5 sec is still not really fast. here's the explain:
Profiled command
'{compositeIndexUsed:1,involvedIndexes:[1],fullySortedByIndex:false,indexIsUsedInOrderBy:false,current:#14:4,fetchingFromTargetElapsed:584,documentReads:1,recordReads:1,elapsed:584.91046,resultType:collection,resultSize:1}'
in 0.586000 sec(s):
{"@type":"d","@version":0,"compositeIndexUsed":1,"involvedIndexes":["country_id"],"fullySortedByIndex":false,"indexIsUsedInOrderBy":false,"current":"#14:4","fetchingFromTargetElapsed":584,"documentReads":1,"recordReads":1,"elapsed":584.91046,"resultType":"collection","resultSize":1,"@fieldTypes":"compositeIndexUsed=l,involvedIndexes=e,fetchingFromTargetElapsed=l,documentReads=l,recordReads=l,elapsed=f"}
Another use case we have is that we now also want to sort the result, which
I tried with:
select from (select expand(in('SUPP_COUNTRY').in('ARTICLE_SUPP_NR')) from
COUNTRY where _id = 'NL') order by ARTICLE_WEIGHT desc
20 item(s) found. Query executed in 2.243 sec(s).
So this is again slow, are there any hints on how a query like this should
be formed to be fast?
Thanks,
Wouter
Op maandag 2 juni 2014 14:03:12 UTC+2 schreef Wouter de Vaal:
>
> Hi,
>
> I am evaluating OrientDB for use in our product to see if our specific
> requirements fit the performance of OrientDB.
>
> In general we have tables with potential quite a lot record with per row
> possibly a few relations to other records in another class, which can have
> relations to another class etc. Simply put a classical SQL style table, but
> customizable by our users, so we cannot exactly predict how the structure
> will be, be we have some ideas.
>
> For now I have created a class ARTICLE, SUPP and COUNTRY. Goal is to find
> articles with a supplier from a specific country. The query I used was very
> fast (nice!):
>
> select * from ARTICLE where out_ARTICLE_SUPP_NR.out_SUPP_COUNTRY._id = 'GB'
> ...
> 20 item(s) found. Query executed in 0.14 sec(s).
>
> But when I want the total count (which I need to calculated the number of
> pages for this query), the count is very slow:
>
> select count(*) from article where
> out_ARTICLE_SUPP_NR.out_SUPP_COUNTRY._id = 'GB'
> ...
> 1 item(s) found. Query executed in 4.084 sec(s).
>
> Our article table has approx 90000 records, suppliers have 528 and
> countries has 9.
> I have not yet created any indexes.
>
> Is there a way to speed up the count query?
>
> Regards,
> Wouter
>
--
---
You received this message because you are subscribed to the Google Groups
"OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
