Hi Luca,
I have deployed a new instance on Ubuntu 14.04 and redo tests, the
performance is stable and good enough, so I guess that's an OrientDB issue
on Windows Server 2012 R2 platform.
Here is the test result on 1.3M contacts, the query is done for an account
with 300k contacts
Especially, the query only uses an single index on a field in ORDER BY. The
same query, if I have an index on Account property, the query engine will
take it instead of EmailAddress index, and that's really a nightmare for my
situation. However, it makes sense because OrientDB uses physical link, so
there is no need an index on FK field, isn't it?
I have also tried the same tests on OrientDB v2.1.17, the results are
slower v2.1.8 and v2.1.9 about 100 ms per query, not sure why !?
If possible, could you explain a bit about the rules of query engine to
pick up index for a query?
My Best,
Hung Tran
On Monday, May 16, 2016 at 6:46:04 PM UTC+7, l.garulli wrote:
>
> Could you please try the time of the following queries and post results?
>
> (1)
>
> SELECT @rid, EmailAddress FROM (
> SELECT expand( out('HasContacts') ) FROM #20:0
> )
> WHERE OwnedBy.EntityInfo.State <> 200 AND EntityInfo.State = 0
> ORDER BY EmailAddress ASC SKIP 100 LIMIT 100
>
> (2)
>
> SELECT @rid, EmailAddress FROM (
> SELECT expand( out('HasContacts') ) FROM #20:0
> ) ORDER BY EmailAddress ASC SKIP 100 LIMIT 100
>
> (3)
>
> SELECT @rid, EmailAddress FROM (
> SELECT expand( out('HasContacts') ) FROM #20:0
> ) LIMIT 100
>
>
> Best Regards,
>
> Luca Garulli
> Founder & CEO
> OrientDB <http://orientdb.com/>
>
>
> On 12 May 2016 at 17:51, Hung Tran <[email protected] <javascript:>>
> wrote:
>
>> Hi Luca,
>>
>> The OwnedBy is a Link property, and the EntityInfo is an embedded
>> property. I have a index on OwnedBy, but I could not create index on
>> property of an object-embedded property (I guess that's a limitation, maybe
>> I need to break it down to properties).
>>
>> If you found anything, please let me know, thank you very much!
>>
>> My Best,
>> Hung Tran
>>
>> On Thursday, May 12, 2016 at 6:56:06 PM UTC+7, l.garulli wrote:
>>>
>>> Another question, are OwnedBy and EntityInfo embedded properties in
>>> Contact?
>>>
>>>
>>> Best Regards,
>>>
>>> Luca Garulli
>>> Founder & CEO
>>> OrientDB <http://orientdb.com/>
>>>
>>>
>>> On 12 May 2016 at 10:50, Hung Tran <[email protected]> wrote:
>>>
>>>> Hi Lugi,
>>>>
>>>> I am sorry about the title, I means the SQL engine fetches more data
>>>> internally.
>>>>
>>>> No, the account #20:0 has 120000 contacts while #20:1 only has 100000
>>>> contacts.
>>>>
>>>>
>>>>
>>>> I will try your suggestion and give a feedback soon.
>>>>
>>>> My Best,
>>>> Hung Tran
>>>>
>>>> On Thursday, May 12, 2016 at 3:24:21 PM UTC+7, l.garulli wrote:
>>>>>
>>>>> Hi Hung,
>>>>> Your title is misleading: "The query execution is unstable, sometimes
>>>>> fetch more data". Why is fetching more data? Did you receive more than
>>>>> 100
>>>>> records?
>>>>>
>>>>> About the reason of the difference in timing, it looks like the record
>>>>> #20:1 has much more Contacts, right? This query is slow because it uses
>>>>> the
>>>>> index only for the order by, not for the lookup:
>>>>>
>>>>>
>>>>> * "documentAnalyzedCompatibleClass": 110866,*
>>>>> If you have 10M of contacts, it scans all the clusters to evaluate all
>>>>> the following conditions:
>>>>>
>>>>>
>>>>> * (OwnedBy.EntityInfo.State <> 200) AND (Account = #20:0) AND
>>>>> (EntityInfo.State = 0)*
>>>>> If you had a graph structure, you could start from the Account and
>>>>> traverse the connected contacts. So the following query:
>>>>>
>>>>> SELECT @rid, EmailAddress
>>>>> FROM Contact
>>>>> WHERE (OwnedBy.EntityInfo.State <> 200) AND (Account = #20:0) AND
>>>>> (EntityInfo.State = 0)
>>>>> ORDER BY EmailAddress ASC skip 100 limit 100
>>>>>
>>>>> Would be something like:
>>>>>
>>>>> SELECT @rid, EmailAddress FROM (
>>>>> SELECT expand( out('HasContacts') ) FROM #20:0
>>>>> )
>>>>> WHERE OwnedBy.EntityInfo.State <> 200 AND EntityInfo.State = 0
>>>>> ORDER BY EmailAddress ASC SKIP 100 LIMIT 100
>>>>>
>>>>>
>>>>>
>>>>> Best Regards,
>>>>>
>>>>> Luca Garulli
>>>>> Founder & CEO
>>>>> OrientDB <http://orientdb.com/>
>>>>>
>>>>>
>>>>> On 11 May 2016 at 19:41, Hung Tran <[email protected]> wrote:
>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> I am using OrientDB 2.1.8 on Windows 2012 server platform.
>>>>>>
>>>>>> My SQL is
>>>>>>
>>>>>>
>>>>>> SELECT @rid, EmailAddress FROM Contact WHERE (OwnedBy.EntityInfo.
>>>>>> State <> 200) AND (Account = #20:0) AND (EntityInfo.State = 0) ORDER
>>>>>> BY EmailAddress ASC skip 100 limit 100
>>>>>>
>>>>>>
>>>>>> When I do a filter with Account = #20:0 which contains about 120000
>>>>>> contacts, the EXPLAIN is
>>>>>>
>>>>>> {
>>>>>> "result": [
>>>>>> {
>>>>>> "@type": "d",
>>>>>> "@version": 0,
>>>>>> "fullySortedByIndex": true,
>>>>>> "documentReads": 200,
>>>>>> "current": "#41:11015",
>>>>>> "documentAnalyzedCompatibleClass": 200,
>>>>>> "recordReads": 200,
>>>>>> "involvedIndexes": [
>>>>>> "IX_Contact_EmailAddress"
>>>>>> ],
>>>>>> "fetchingFromTargetElapsed": 250,
>>>>>> "indexIsUsedInOrderBy": true,
>>>>>> "evaluated": 200,
>>>>>> "elapsed": 251.53171,
>>>>>> "resultType": "collection",
>>>>>> "resultSize": 100,
>>>>>> "@fieldTypes":
>>>>>> "documentReads=l,current=x,documentAnalyzedCompatibleClass=l,recordReads=l,involvedIndexes=e,fetchingFromTargetElapsed=l,evaluated=l,elapsed=f"
>>>>>> }
>>>>>> ],
>>>>>> "notification": "Query executed in 0.575 sec. Returned 1
>>>>>> record(s)"
>>>>>> }
>>>>>>
>>>>>> there are only 200 records evaluated and took 0.575 sec.
>>>>>>
>>>>>> When I change my filter to Account = #20:1, the EXPLAIN is
>>>>>>
>>>>>> {
>>>>>> "result": [
>>>>>> {
>>>>>> "@type": "d",
>>>>>> "@version": 0,
>>>>>> "fullySortedByIndex": true,
>>>>>> "documentReads": 110866,
>>>>>> "current": "#41:111016",
>>>>>> "documentAnalyzedCompatibleClass": 110866,
>>>>>> "recordReads": 110866,
>>>>>> "involvedIndexes": [
>>>>>> "IX_Contact_EmailAddress"
>>>>>> ],
>>>>>> "fetchingFromTargetElapsed": 5517,
>>>>>> "indexIsUsedInOrderBy": true,
>>>>>> "evaluated": 110866,
>>>>>> "elapsed": 5514.2456,
>>>>>> "resultType": "collection",
>>>>>> "resultSize": 100,
>>>>>> "@fieldTypes":
>>>>>> "documentReads=l,current=x,documentAnalyzedCompatibleClass=l,recordReads=l,involvedIndexes=e,fetchingFromTargetElapsed=l,evaluated=l,elapsed=f"
>>>>>> }
>>>>>> ],
>>>>>> "notification": "Query executed in 5.835 sec. Returned 1
>>>>>> record(s)"
>>>>>> }
>>>>>>
>>>>>> There are 110866 records evaluated and took 5.835 sec.
>>>>>>
>>>>>> I could not understand what caused a big different here, is it a bug?
>>>>>> Any explanation will be very appreciated!
>>>>>>
>>>>>> My Best,
>>>>>> Hung Tran
>>>>>>
>>>>>>
>>>>>> --
>>>>>>
>>>>>> ---
>>>>>> 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.
>>>>>>
>>>>>
>>>>> --
>>>>
>>>> ---
>>>> 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.
>>>>
>>>
>>> --
>>
>> ---
>> 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] <javascript:>.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>
--
---
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.
