[
http://mifosforge.jira.com/browse/MIFOS-2827?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=57507#action_57507
]
johnwoodlock commented on MIFOS-2827:
-------------------------------------
I have reviewed this query and we should certainly take most of the
simplifications on board. The queries are built dynamically in the code and
can't just be slotted in and require (a surprising amount of) code refactoring.
That said, it should be done as the functionality is a scalability concern
when searching for customers by name.
I think there's a fairly obvious fix using Raghav's work, union queries and
some changes to indexes. But, before getting into the technicalities of it,
can Kay or Emily have a look at my interpretation of the current customer
search process below and comment on whether it meets 'requirements' (or needs
adjusting) then we could move on it.
Search Process Steps - Stops if a match is found
1. Exact match on global account number?
2. Exact match on global customer number?
3. Exact match on Government Id?
... so far so good. The matching above is quick. The performance/scalability
problem comes if it gets to the next step
4. Look for a match on the customer name (or parts of it). Include any (only
once though) that match:
a) groups and centers that have a display_name that starts with the search
input
b) clients that have a first name that starts with the search input
c) clients that have a last name that starts with the search input
d) clients that have a second last name that starts with the search input
Is 4) an accurate reflection of requirements - too much... too little?
I've left out admin/loan officer aspects for simplicity.
> 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
------------------------------------------------------------------------------
Download Intel® Parallel Studio Eval
Try the new software tools for yourself. Speed compiling, find bugs
proactively, and fine-tune applications for parallel performance.
See why Intel Parallel Studio got high marks during beta.
http://p.sf.net/sfu/intel-sw-dev
_______________________________________________
Mifos-issues mailing list
Mifos-issues@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/mifos-issues
