Performance issue in order by

58 views
Skip to first unread message

Ram Karthik

unread,
May 17, 2017, 5:24:04 AM5/17/17
to orient-...@googlegroups.com

Hi Sir,

   I faced performance issue for given senario,
   
    Oriented version: 2.0.18

   -> Vertex has a 100000 records
  
         1.  When I fetch the data use 'order by' then response time is too late and also set index for the field.

         2.  how to search multiple property value using index by query using OR condition.

                ex : customer (V)  have a multiple properties like email_id, mobile_no, location

                      I want to search one keyword like 'ram' how to use index and get response very quickly?


      Help me as soon as possible.



Thanks,
 Ram

                 



user.w...@gmail.com

unread,
May 17, 2017, 4:37:48 PM5/17/17
to OrientDB
Hi,

Could you give an example of your structure? Or, did you have a test case to share??

Thanks in advance

Regards,
Michela

Ram Karthik

unread,
May 18, 2017, 1:58:36 AM5/18/17
to orient-...@googlegroups.com
Sure.

We developed billing and engagement application using Java.

Overview of our Structure:

customer (v) - mobile, name, customer_id, email_id, location, created_date, etc...
billing(v) - invoice_no, created_date, gross_value, is_deleted, etc..
activity(v) - created_date, is_deleted, etc..
store (v) - name, location, created_date, etc..

store_customer (E) - is_active, created_date  (This edge connected to the customer)

customer have edge of the above vertexes (billing, activity, like this)


Usecase of my scenario,

     -  I need to get all customer by a particular store and get each customer last visited date, total visited count, total gross value like this finally order by customer created date. 

    -   some of my merchant want to search particular customer by name, email_id, location, mobile_no, these case how to use compoiste index with or condition.


Below my Query, this query taken 15 sec

select from (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 desc skip 0 limit 11




Please do favour.

Thanks in Advance.

user.w...@gmail.com

unread,
May 18, 2017, 5:02:26 PM5/18/17
to OrientDB
Ok.

Let me see if I understood correctly, is your structure like this?

customer (v) - mobile, name, customer_id, email_id, location, created_date, etc...
billing(v) - invoice_no, created_date, gross_value, is_deleted, etc..
activity(v) - created_date, is_deleted, etc..
store (v) - name, location, created_date, etc..

EDGES:

customer -- customer_has_billing --> billing
customer -- customer_has_activity --> acitivity
store -- store_has_billing --> billing
store -- store_has_customer --> customer

which relationship 'customer_has_membership' is?

Thanks in advance.

Regards,
Michela

Ram Karthik

unread,
May 19, 2017, 1:23:34 AM5/19/17
to OrientDB
Hi,

   Sorry for not given clear information.

   - We mainly developed salon and spa software, so each store given to membership card to the customer, that's why i get which customer have a member in a particular store.

   membership (v) - membership_date, expiry_date, is_deleted, etc..

Edge:
  customer -> customer_has_membership  -> membership 


Thanks

user.w...@gmail.com

unread,
May 19, 2017, 6:11:45 PM5/19/17
to OrientDB
Hi,

do you have a test case to share? So in this way I can made some tests with a full and complete test case.

Hi,

do you have a test case to share? So in this way I can made some tests with a complete test case and full of data.

If it's possible, of course.

Thanks in advance.

Regards,
Michela
Reply all
Reply to author
Forward
0 new messages