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