Performance issue in order by

[Ram Karthik]

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]

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]

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

Attached customer list page link:  https://lh3.googleusercontent.com/-Ddph7FTkEek/WR04nhenaXI/AAAAAAAAAFg/JIpHzC-hUgAMWSENQEZZCUlTema4bBiewCLcB/s1600/customer.png

Please do favour.

Thanks in Advance.

[user.w...@gmail.com]

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]

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]

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