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]> 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].
> 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.
