[
http://mifosforge.jira.com/browse/MIFOS-2827?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=57615#action_57615
]
Kay Chau commented on MIFOS-2827:
---------------------------------
Yes that looks right John
And looks like it matches what we have in our specs:
http://www.mifos.org/knowledge/functional-specifications/retrieving-information/searching-and-browsing
As always needs to be within the user's data scope
> Enhance query for customer search
> ---------------------------------
>
> Key: MIFOS-2827
> URL: http://mifosforge.jira.com/browse/MIFOS-2827
> Project: mifos
> Issue Type: Improvement
> Components: Search
> Affects Versions: Release 1.5
> Reporter: jbrewster
> Assignee: johnwoodlock
> Fix For: Shamim D
>
>
> Raghavendra has looked at the current customer search query and recommends 2
> different improvements which improved query time by 30% in his testing.
> Note the query is different for admin and branch manager.
> Original Admin query:
> ---------------------
> // Original Query from admin as a login user for customer search
> select count(customerbo0_.CUSTOMER_ID) as col_0_0_
> from CUSTOMER customerbo0_
> left outer join CUSTOMER customerbo1_ on
> customerbo0_.PARENT_CUSTOMER_ID=customerbo1_.CUSTOMER_ID
> left outer join CUSTOMER customerbo2_ on
> customerbo1_.PARENT_CUSTOMER_ID=customerbo2_.CUSTOMER_ID
> left outer join OFFICE officebo3_ on
> customerbo0_.BRANCH_ID=officebo3_.OFFICE_ID
> left outer join PERSONNEL personnelb4_ on
> customerbo0_.LOAN_OFFICER_ID=personnelb4_.PERSONNEL_ID
> where (2=1 and customerbo0_.LOAN_OFFICER_ID=1 or 2<>1) and
> (officebo3_.SEARCH_ID like '1.1.%')
> and ((customerbo0_.CUSTOMER_LEVEL_ID in (2 , 3))
> and (customerbo0_.DISPLAY_NAME like 'GF_LTestR1_Client20%')
> or customerbo0_.CUSTOMER_LEVEL_ID=1
> and (customerbo0_.FIRST_NAME like 'GF_LTestR1_Client20%'
> or customerbo0_.LAST_NAME like 'GF_LTestR1_Client20%'
> or customerbo0_.SECOND_LAST_NAME like 'GF_LTestR1_Client20%'
> or (customerbo0_.FIRST_NAME like 'GF_LTestR1_Client20')
> and (customerbo0_.LAST_NAME like '')));
> Original branch manager query:
> ------------------------------
> //Original Query from branch manager as a login user for customer search
> select count(*) as col_0_0_ from CUSTOMER customerbo0_
> left outer join CUSTOMER customerbo1_ on
> customerbo0_.PARENT_CUSTOMER_ID=customerbo1_.CUSTOMER_ID
> left outer join CUSTOMER customerbo2_ on
> customerbo1_.PARENT_CUSTOMER_ID=customerbo2_.CUSTOMER_ID
> left outer join OFFICE officebo3_ on
> customerbo0_.BRANCH_ID=officebo3_.OFFICE_ID
> left outer join PERSONNEL personnelb4_ on
> customerbo0_.LOAN_OFFICER_ID=personnelb4_.PERSONNEL_ID
> where (2=1 and customerbo0_.LOAN_OFFICER_ID=1596 or 2<>1) and
> customerbo0_.BRANCH_ID=88
> and (customerbo0_.CUSTOMER_LEVEL_ID<>1 and (customerbo0_.DISPLAY_NAME like
> 'GF_LTestR1_Client20%')
> or customerbo0_.CUSTOMER_LEVEL_ID=1 and (customerbo0_.FIRST_NAME like
> 'GF_LTestR1_Client20%'
> or customerbo0_.LAST_NAME like 'GF_LTestR1_Client20%'
> or customerbo0_.SECOND_LAST_NAME like 'GF_LTestR1_Client20%'
> or (customerbo0_.FIRST_NAME like 'GF_LTestR1_Client20') and
> (customerbo0_.LAST_NAME like '')))
> Tuned query for admin:
> //Performance of the tuned query improved 30% than the original query for
> admin(Mifos) user only
> // Tuned Query
> select count(customerbo0_.CUSTOMER_ID) as col_0_0_
> from CUSTOMER customerbo0_
> where (customerbo0_.CUSTOMER_LEVEL_ID in (1, 2 , 3))
> and ((customerbo0_.DISPLAY_NAME like 'GF_LTestR1_Client20%')
> or customerbo0_.FIRST_NAME like 'GF_LTestR1_Client20%'
> or customerbo0_.LAST_NAME like 'GF_LTestR1_Client20%'
> or customerbo0_.SECOND_LAST_NAME like 'GF_LTestR1_Client20%');
> or
> select count(customerbo0_.CUSTOMER_ID) as col_0_0_
> from CUSTOMER customerbo0_
> where (2=1 and customerbo0_.LOAN_OFFICER_ID=1 or 2<>1)
> and (customerbo0_.CUSTOMER_LEVEL_ID in (1, 2 , 3))
> and ((customerbo0_.DISPLAY_NAME like 'GF_LTestR1_Client200%')
> or customerbo0_.FIRST_NAME like 'GF_LTestR1_Client200%'
> or customerbo0_.LAST_NAME like 'GF_LTestR1_Client200%'
> or customerbo0_.SECOND_LAST_NAME like 'GF_LTestR1_Client200%');
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://mifosforge.jira.com/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
_______________________________________________
Mifos-issues mailing list
Mifos-issues@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/mifos-issues
