Performance Issue

[Ram Karthik]

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 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) 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) 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 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) 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) 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

[Luigi Dell'Aquila]

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

--

---
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 orient-database+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.