Hi Ram, The problem here is that the inner query (the TRAVERSE) can return a big quantity of record. For each one of these records, the outer query has to invoke the other sub-queries to calculate the LET variable assignments. The first query can just fetch the first 11 records from the TRAVERSE, the second one has to fetch all the elements from the TRAVERSE (I don't know how many, but probably thousands looking at the response time), calculate the LET for all of them, sort the results and then return the first 11.
As you can see, the difference in performance is definitely justified. There is no way for the execution planner to optimize this query, as the TRAVERSE can return any kind of records Thanks Luigi 2017-05-16 9:47 GMT+02:00 Ram Karthik <[email protected]>: > Hi, > > I have faced on fetching performance issue, > > I used below query and get quick response (0.035 sec) > > *store_customer (E) - Edge with data.* > > * select $member[0].member_count as memberList, $value[0].total as > customerValue, $a[0].created_date as last_visited_date, created_date, > in.gender as gender,in.customer_unique_id as customer_unique_id, > send_promotional, send_transaction, in as rid, in as customer_id,in.name > <http://in.name> as name,in.mobile_no as mobile_no,in.email_id as > email_id,in.dob as dob,in.anniversary_date as anniversary_date,in.location > as location, in.out('customer_has_activity')['is_deleted = > false']['store_id = #48:1752'].size() as visited_count from (traverse > out_store_customer from #48:1752 ) where out = #48:1752 and in.is_deleted = > false and is_active = true and (in.mobile_no.toUpperCase() like '%%' or > in.email_id.toUpperCase() like '%%' or in.customer_unique_id.toUpperCase() > like '%%' or in.name.toUpperCase() like '%%') LET $a = (select from (select > expand(out('customer_has_activity')['is_deleted = false']['store_id = > #48:1752']) from $parent.$current.in <http://current.in>) order by > created_date desc skip 0 limit 1), $value = (select sum(gross_value) as > total from (select expand(out('customer_has_billing')['is_deleted = > false']['is_cancel = false']) from #260:86891) where > in('store_has_billing') in (#48:1752)), $member = (select count(1) as > member_count from (select > expand(out('customer_has_membership')['is_deleted=false']['storeid = > #48:1752']) from $parent.$current.in <http://current.in>) where expiry_date > is null or sysdate() < expiry_date) skip 0 limit 11* > > But after add *order by created date* before skip, and get response very > slow (15.00 sec). > Like, > > * select $member[0].member_count as memberList, $value[0].total > as customerValue, $a[0].created_date as last_visited_date, created_date, > in.gender as gender,in.customer_unique_id as customer_unique_id, > send_promotional, send_transaction, in as rid, in as customer_id,in.name > <http://in.name> as name,in.mobile_no as mobile_no,in.email_id as > email_id,in.dob as dob,in.anniversary_date as anniversary_date,in.location > as location, in.out('customer_has_activity')['is_deleted = > false']['store_id = #48:1752'].size() as visited_count from (traverse > out_store_customer from #48:1752 ) where out = #48:1752 and in.is_deleted = > false and is_active = true and (in.mobile_no.toUpperCase() like '%%' or > in.email_id.toUpperCase() like '%%' or in.customer_unique_id.toUpperCase() > like '%%' or in.name.toUpperCase() like '%%') LET $a = (select from (select > expand(out('customer_has_activity')['is_deleted = false']['store_id = > #48:1752']) from $parent.$current.in <http://current.in>) order by > created_date desc skip 0 limit 1), $value = (select sum(gross_value) as > total from (select expand(out('customer_has_billing')['is_deleted = > false']['is_cancel = false']) from #260:86891) where > in('store_has_billing') in (#48:1752)), $member = (select count(1) as > member_count from (select > expand(out('customer_has_membership')['is_deleted=false']['storeid = > #48:1752']) from $parent.$current.in <http://current.in>) where expiry_date > is null or sysdate() < expiry_date) order by created_date descskip 0 limit > 11* > > > Please can you help me as soon as possible, what I am mistaken above query. > > > Thanks, > Ram > > -- > > --- > 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.
