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