[
http://mifosforge.jira.com/browse/MIFOS-2827?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kay Chau resolved MIFOS-2827.
-----------------------------
Resolution: Invalid
Fix Version/s: (was: Release E - Unscheduled)
We're doing other things with Search, will open a new improvement for a more
serious search engine
> 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: Release E
>
>
> 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
------------------------------------------------------------------------------
Nokia and AT&T present the 2010 Calling All Innovators-North America contest
Create new apps & games for the Nokia N8 for consumers in U.S. and Canada
$10 million total in prizes - $4M cash, 500 devices, nearly $6M in marketing
Develop with Nokia Qt SDK, Web Runtime, or Java and Publish to Ovi Store
http://p.sf.net/sfu/nokia-dev2dev
_______________________________________________
Mifos-issues mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/mifos-issues
