[
http://mifosforge.jira.com/browse/MIFOS-2827?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=57700#action_57700
]
johnwoodlock commented on MIFOS-2827:
-------------------------------------
Update: I refactored the queries and was happy with that but found that the
coding work to get those queries in was likely to be about a weeks work (more
than I had originally thought).
I wondered whether I should go ahead right now or maybe wait till after the
current release.
The reason this issue was being done was because of the poor results coming
from performance testing. (about a minute for 1M clients with 10 concurrent
users - increases with increasing customers). However, during my own tests I
noticed it was really just 'admin' users who had a real problem (they can
search on more than 1 branch depending on how high up the office hierarchy they
are) and they were not the typical users of the customer search function (they
do use it sometimes though). The performance test was being run on these
users. When it was run on a branch level user, the results were about 0.6 secs.
So, I think this should still be done but after we branch off the current
release.
> 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
