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