Performance Issue using multiple Let query

67 views
Skip to first unread message

Pugazharasan Thirumalai

unread,
Jun 9, 2017, 4:45:06 AM6/9/17
to OrientDB
Hai,

We are facing performance issue when we execute the below query, When the customer count is below 3000 then the query works good but the customer count increase more then that it takes long time to execute the query. Below i explain the query clearly,

select @rid, $ex[0].count as exsting_count, $ac[0].count as active_count, $ch[0].count as churn_count, $de[0].count as defected_count from #48:1635 Let $ex = (select count(1) as count from (traverse out_store_customer from #48:1635) where in.is_deleted = false and is_active = true), $ac = (select count(1) as count from (select count(1) as visit_count from (select in('customer_has_activity')[0] as rid from (select expand(out('customer_has_activity')['is_deleted = false'][store_id = #48:1635]) from (select expand(in) from (traverse out_store_customer from #48:1635) where is_active = true)) where created_date between '2017-04-10 11:29:21' and '2017-06-09 11:29:21') group by rid) where visit_count >= 2), $ch = (select count(1) as count from (select from (select $a.size() as count from (select from (select in('customer_has_activity')[0] as rid from (select expand(out('customer_has_activity')['is_deleted = false'][store_id = #48:1635]) from (select expand(in) from (traverse out_store_customer from #48:1635) where is_active = true )) where created_date between '2017-03-11 11:29:21' and '2017-08-07 11:29:21') group by rid) let $a = (select from (select expand(out('customer_has_activity')['is_deleted = false'][store_id = #48:1635]) from $parent.$current.rid) where created_date between '2017-04-10 11:29:21' and '2017-06-09 11:29:21'))) where count = 0)), $de = (select count(1) as count from (select count from (select *,$a.size() as count from (select from (select in('customer_has_activity')[0] as rid  from (select expand(out('customer_has_activity')['is_deleted = false'][store_id =#48:1635]) from (select expand(in) from (traverse out_store_customer from #48:1635) where is_active = true )) where created_date < '2017-03-11 11:29:21') group by rid) let $a = (select from (select expand(out('customer_has_activity')['is_deleted = false'][store_id = #48:1635]) from $parent.$current.rid) where created_date between '2017-03-11 11:29:21' and '2017-06-09 11:29:21')) where count = 0)).

Actually our aim is to find the number of existing customer, active customers, churn customer and defecting customer .

#48:1635 -> Store Id
out_store_customer ->edge between store and customer 
customer_has_activity -> edge between customer and customer activity

Result :

select @rid, $ex[0].count as exsting_count, $ac[0].count as active_count, $ch[0].count as churn_count, $de[0].count as defected_count from #48:1635

Existing customer :

Let $ex = (select count(1) as count from (traverse out_store_customer from #48:1635) where in.is_deleted = false and is_active = true)

Active Customers : 

$ac = (select count(1) as count from (select count(1) as visit_count from (select in('customer_has_activity')[0] as rid from (select expand(out('customer_has_activity')['is_deleted = false'][store_id = #48:1635]) from (select expand(in) from (traverse out_store_customer from #48:1635) where is_active = true)) where created_date between '2017-04-10 11:29:21' and '2017-06-09 11:29:21') group by rid) where visit_count >= 2)

Churn Customer:

$ch = (select count(1) as count from (select from (select $a.size() as count from (select from (select in('customer_has_activity')[0] as rid from (select expand(out('customer_has_activity')['is_deleted = false'][store_id = #48:1635]) from (select expand(in) from (traverse out_store_customer from #48:1635) where is_active = true )) where created_date between '2017-03-11 11:29:21' and '2017-08-07 11:29:21') group by rid) let $a = (select from (select expand(out('customer_has_activity')['is_deleted = false'][store_id = #48:1635]) from $parent.$current.rid) where created_date between '2017-04-10 11:29:21' and '2017-06-09 11:29:21'))) where count = 0))

Defecting Customer:

$de = (select count(1) as count from (select count from (select *,$a.size() as count from (select from (select in('customer_has_activity')[0] as rid  from (select expand(out('customer_has_activity')['is_deleted = false'][store_id =#48:1635]) from (select expand(in) from (traverse out_store_customer from #48:1635) where is_active = true )) where created_date < '2017-03-11 11:29:21') group by rid) let $a = (select from (select expand(out('customer_has_activity')['is_deleted = false'][store_id = #48:1635]) from $parent.$current.rid) where created_date between '2017-03-11 11:29:21' and '2017-06-09 11:29:21')) where count = 0))

Any one please optimize the query and make it execute in fraction of second



user.w...@gmail.com

unread,
Jun 9, 2017, 4:48:28 PM6/9/17
to OrientDB
Hi,

are you using index? 
If yes which type?

Thanks.

Best regards,
Michela

Pugazharasan Thirumalai

unread,
Jun 10, 2017, 1:12:34 AM6/10/17
to OrientDB
Ya we are using index,
Type : NOTUNIQUE

Ivan Mainetti

unread,
Jun 12, 2017, 8:29:27 PM6/12/17
to OrientDB
Hai,
could you share all the involved schema (classes + properties + indices)?
thanks

Pugazharasan Thirumalai

unread,
Jun 13, 2017, 2:48:26 AM6/13/17
to OrientDB
customer 

Properties 

NameTypeLinked_TypeLinked_ClassMandatoryRead_OnlyNot_NullMinMaxCollateActions
anniversary_date
created_date
customer_unique_id
dob
email_id
gender
is_deleted
location
mobile_no
name

Indexes 

NameType PropertiesEngine Actions
customer.name_email_id_mobile_no_customer_unique_idNOTUNIQUE["name","email_id","mobile_no","customer_unique_id"]SBTREE 
customer.customer_unique_idFULLTEXT_HASH_INDEX["customer_unique_id"]SBTREE 
customer.mobile_noFULLTEXT["mobile_no"]LUCENE 
customer.created_dateNOTUNIQUE["created_date"]SBTREE 
customer.email_idFULLTEXT_HASH_INDEX["email_id"]SBTREE 
customer.name_mobile_noFULLTEXT["name","mobile_no"]LUCENE 
customer.genderFULLTEXT_HASH_INDEX["gender"]SBTREE 
customer.email_id_mobile_no_name_customer_unique_idDICTIONARY["email_id","mobile_no","name","customer_unique_id"]SBTREE 
customer.nameFULLTEXT["name"]LUCENE 


customer_activity 

Properties 

NameTypeLinked_TypeLinked_ClassMandatoryRead_OnlyNot_NullMinMaxCollateActions
created_date
email_id
is_deleted
location
mobile_no
name
service_type
store_id

Indexes 

NameType PropertiesEngine Actions
customer_activity.created_dateNOTUNIQUE["created_date"]SBTREE 
customer_activity.email_id_store_id_is_deletedNOTUNIQUE["email_id","store_id","is_deleted"]SBTREE 
  

I have added customer and customer_activity schema.

Reply all
Reply to author
Forward
0 new messages