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.
