[Mifos-developer] Customer and Loan Account Search Test Results

[Raghavendr...@sungard.com]

Hi Jeff, Adam,

 

I have attached the customer search and loan account test results along with the mail.

I will consolidate and send all the scripts, data (CSV) file and their descriptions in a couple of days.

 

 

Thanks & Regards,

Raghavendra Bhandari • Software Engineer• SunGard •  Technology services • Embassy Icon,6th Floor,Infantry  Road, Bangalore 560001, Karnataka, India
•Tel +91-80-30913000 • Ext-3187 • Mobile +91-9964073693 • Fax +91-80-2222-0511 • www.sungard.com/sts  

[Jeff Brewster]

Thanks Raghav,

So the loan account search average time was .7 seconds and the customer name search average search was 61 seconds? 

 

After you rerun the Collection Sheet Entry test against the trunk war, could you please rerun these same search tests against the trunk as well.  Would be good to contrast the search times for each revision.

 

Thanks,

Jeff

[Raghavendr...@sungard.com]

Hi Jeff,

 

I have attached the customer and loan account search test results along with this mail.

 I noticed that, the results of both the tests are quite more( 5 seconds for customer search and 1 second for loan account search)than the previous test results.

 

And I will send the Collection Sheet Entry test results on tomorrow.

 

Thanks & Regards,

Raghavendra Bhandari • Software Engineer• SunGard •  Technology services • Embassy Icon,6th Floor,Infantry  Road, Bangalore 560001, Karnataka, India
•Tel +91-80-30913000 • Ext-3187 • Mobile +91-9964073693 • Fax +91-80-2222-0511 • www.sungard.com/sts  

[Jeff Brewster]

Hi Raghav,

Thanks for the reports.  Since these tests were run on your local machine, do you have confidence that no other processes were loading down system when running the tests for this test pass?

 

JohnW – is there any particular logging we might want to enable to get more clarity about the difference between 1.4 and trunk on these search tests?

 

Jeff

[John Woodlock]

Jeff,

I was a bit surprised at the client search results too.

Just to make sure I'm looking at the same searches... is it the search that has the following text above it?

"To quickly find a Client, Center,  Group, or account you can...

Search by name, system ID or account number:           "

or is there a separate client and loan search?

The above search is on the list of things I want to tackle (but wasn't sure of the priority).  In my initial investigation I saw it does a table scan on customer but was coming back in <1sec on my machine (80k customers but they'd all be in memory).  Must admit, didn't think it would deteriorate that badly with 350k customers even with 50 threads.  Raghav, do you have a reasonable amount of memory assigned to mysql (e.g 200-300mb)?

Lots of memory or  not, if it is the search I'm thinking of , it should be something that can be fixed up quite easily and retested. 

Not sure if anyone has done any work that might affect that area since 1.4 (except maybe the governmentId search bit).

John

[Parthasara...@sungard.com]

Hi John,

If we are sure it is a table scan then I guess we should resolve that first and make it an index seek. Here I believe Raghav is doing a search based on Name to retrieve customers - If this is a likely use case for clients - assuming that name will be fairly unique across a large data set we can try a non-clustered index on the column and run this test again.

 

Thanks and Regards,

Parthasarathy  T • Technical Lead • SunGard • Technology Services • Embassy Icon, #3, 6^th Floor, Infantry Road, Bangalore – 560 001, Karnataka, India • Tel +91 80 3091 3183 • Mobile +91 99450 00394  • Main +91 80 3091 3000 • Fax +91 80 2222 0511 • www.sungard.com/sts

P Think before you print

CONFIDENTIALITY: This e-mail (including any attachments) may contain confidential, proprietary and privileged information, and unauthorized disclosure or use is prohibited.  If you receive this e-mail in error, please notify the sender and delete this e-mail from your system. 

 

---

From: John Woodlock [mailto:john.w...@gmail.com]
Sent: Tuesday, March 02, 2010 7:32 AM
To: Jeff Brewster
Cc: Adam Feuer; mifos-d...@lists.sourceforge.net; Bhandari, Raghavendra
Subject: Re: [Mifos-developer] Customer and Loan Account Search Test Results

[John Woodlock]

Hi Partha,

pretty sure its table scanning.  Haven't looked into it much (did start however).  I think the sql below is likely to be the problem area (replacing ce% with 'whatever your search is'%).  As you can see its got lots of ORs on different text fields... so am not sure if a single index helps.

My first thought was to deconstruct the query into 2 or 3 queries specific queries e.g. for centers and groups do a display_name search... for client do whatever it is that this query is asking of clients (yes, still haven't got my head around it).

Feel free to have a blast though Partha.

John

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 'ce%') or customerbo0_.customer_level_id=1 and (customerbo0_.first_name like 'ce%' or customerbo0_.last_name like 'ce%'

or customerbo0_.second_last_name like 'ce%' or (customerbo0_.first_name like 'ce') and (customerbo0_.last_name like '')))

[John Woodlock]

forwarding to list and all recipients.

---------- Forwarded message ----------
From: John Woodlock <john.w...@gmail.com>
Date: Tue, Mar 2, 2010 at 8:24 PM
Subject: Re: [Mifos-developer] Customer and Loan Account Search Test Results

[John Woodlock]

Note on terminology: I shouldn't say table scan... it may be picking an index but doing the work equivalent to a full table scan.

Running EXPLAIN on that query showed it using CUSTOMER_BRANCH_SEARCH_IDX... which restricts its work somewhat but nowhere near enough.

John

[Parthasara...@sungard.com]

Hi,

 

Raghav's slow query log also showed up a similar query

 

Here is a thought..

 

why not we use union (or union all whichever returns unique rows) instead of OR like below:

 

-----------------------------------------

select customerbo0_.CUSTOMER_ID as col_0_0_
from CUSTOMER customerbo0_
where (officebo3_.SEARCH_ID like '1.1.%')
and ((customerbo0_.CUSTOMER_LEVEL_ID in (2 , 3)) and customerbo0_.CUSTOMER_LEVEL_ID=1 and (customerbo0_.FIRST_NAME like 'ce%')

 

union

 

select customerbo0_.CUSTOMER_ID as col_0_0_
from CUSTOMER customerbo0_
where (officebo3_.SEARCH_ID like '1.1.%')
and ((customerbo0_.CUSTOMER_LEVEL_ID in (2 , 3)) and customerbo0_.CUSTOMER_LEVEL_ID=1 and (customerbo0_.LAST_NAME like ce%')

 

union

 

select customerbo0_.CUSTOMER_ID as col_0_0_
from CUSTOMER customerbo0_
where (officebo3_.SEARCH_ID like '1.1.%')
and ((customerbo0_.CUSTOMER_LEVEL_ID in (2 , 3)) and customerbo0_.CUSTOMER_LEVEL_ID=1 and (customerbo0_.SECOND_LAST_NAME like ce%')

-----------------------------------------

this way we dont have to do index scans/table scans and do 3 index seeks. For large tables a smaller set of n index seeks will be much faster than 1 scan.

 

That said, in the below query do we really need the 3 left outer joins

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 personnel personnelb4_ on customerbo0_.loan_officer_id=personnelb4_.personnel_id 

and the where clause (2=1 and customerbo0_.loan_officer_id=1 or 2<>1) . If we can, we will remove these as well

-----------------------------------------

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 'ce%') or customerbo0_.customer_level_id=1 and (customerbo0_.first_name like 'ce%' or customerbo0_.last_name like 'ce%'

or customerbo0_.second_last_name like 'ce%' or (customerbo0_.first_name like 'ce') and (customerbo0_.last_name like '')))

-----------------------------------------

 

Thanks and Regards,

 

Parthasarathy  T • Technical Lead • SunGard • Technology Services • Embassy Icon, #3, 6^th Floor, Infantry Road, Bangalore – 560 001, Karnataka, India • Tel +91 80 3091 3183 • Mobile +91 99450 00394  • Main +91 80 3091 3000 • Fax +91 80 2222 0511 • www.sungard.com/sts

P Think before you print

CONFIDENTIALITY: This e-mail (including any attachments) may contain confidential, proprietary and privileged information, and unauthorized disclosure or use is prohibited.  If you receive this e-mail in error, please notify the sender and delete this e-mail from your system. 

 

From: John Woodlock [mailto:john.w...@gmail.com]

Sent: Tuesday, March 02, 2010 2:57 PM
To: Mifos software development; jbre...@grameenfoundation.org; afe...@grameenfoundation.org; Bhandari, Raghavendra
Subject: [Mifos-developer] Fwd: Customer and Loan Account Search Test Results

[John Woodlock]

Partha,

What you say sounds right.  Of course, it means digging around in the code (the query is not static but built up with whatever parameters are available).

I've created a jira issue for this.  It is currently unassigned... so if you want to you can assign yourself to it.  Or anyone else who wants to have a go.

If it is still unassigned in a week or so I'll pick it up.

I'm assuming the fix would go into D release rather than C release.

http://mifosforge.jira.com/browse/MIFOS-2763

John

------------------------------------------------------------------------------
Download Intel&#174; 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