fineract-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Adi Raju" <adi.r...@confluxtechnologies.com>
Subject RE: Query profiling
Date Wed, 14 Sep 2016 08:49:19 GMT
Hi Immanuel,

 

Across MifosX, a user’s data visibility is restricted to the office that they belong to
or the sub-offices below hierarchy.

In the reports, ‘m_office o join m_office ounder’ is generally used to show the results
for the selected office and restrict the data returned based on the user’s visibility.

In your case, in ounder join on condition you have used ‘.%’ as hierarchy, so it is doing
something unnecessary. Looks like resultant Cartesian data is becoming too big and hence the
delay.

 

Regards,

Adi

 

From: Immanuel Jeyaraj [mailto:sefierin@gmail.com] 
Sent: 09 September 2016 10:40
To: Mifos software development <mifos-developer@lists.sourceforge.net>; dev@fineract.incubator.apache.org
Subject: Query profiling

 

Dear All,

I have created a SQL following one of the table report to check why the SQLs take a long time
to run.

First query:

The office choosen is not part of any other office / region.

select 
concat(repeat("..", ((LENGTH(ounder.`hierarchy`) - LENGTH(REPLACE(ounder.`hierarchy`, '.',
'')) - 1))), ounder.`name`) as "Office",
lo.display_name as "Loan Officer", 
f.`name` as Fund, 
pl.name as Product, 
count(l.id) as '# Loans',
count(c.account_no) as '# Clients',
sum(l.principal_amount) as "Loan Amount", 
sum(l.principal_outstanding_derived) as "Principal Outstanding"
from m_office o 
join m_office ounder on ounder.hierarchy like concat(o.hierarchy, '%') and ounder.hierarchy
like concat('.', '%')
join m_client c on c.office_id = ounder.id
join m_group g on g.office_id = ounder.id
join m_loan l on l.client_id = c.id or l.group_id = g.id
join m_product_loan pl on pl.id = l.product_id
left join m_staff lo on lo.id = l.loan_officer_id
left join m_currency cur on cur.code = l.currency_code
left join m_fund f on f.id = l.fund_id
left join m_loan_arrears_aging laa on laa.loan_id = l.id
where 
o.id = 10
and (l.currency_code = "-1" or "-1" [...]

 Showing rows 0 - 2 (3 total, Query took 14.9684 sec)

Below is the profiling details:

 


Status <http://10.0.3.218/phpmyadmin/url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Fgeneral-thread-states.html&token=e4376b4e8e2f98572fdfc0f778416d88>


Time


Starting

23 µs


Waiting For Query Cache Lock

8 µs


Checking Query Cache For Query

315 µs


Checking Permissions

10 µs


Checking Permissions

4 µs


Checking Permissions

3 µs


Checking Permissions

3 µs


Checking Permissions

3 µs


Checking Permissions

3 µs


Checking Permissions

4 µs


Checking Permissions

3 µs


Checking Permissions

3 µs


Checking Permissions

5 µs


Opening Tables

135 µs


System Lock

19 µs


Waiting For Query Cache Lock

35 µs


Init

199 µs


Optimizing

76 µs


Statistics

626 µs


Preparing

100 µs


Creating Tmp Table

596 µs


Executing

8 µs


Copying To Tmp Table

15 s


Sorting Result

86 µs


Sending Data

56 µs


End

6 µs


Removing Tmp Table

309 µs


End

10 µs


Query End

8 µs


Closing Tables

27 µs


Freeing Items

55 µs


Logging Slow Query

5 µs


Logging Slow Query

3 µs


Cleaning Up

8 µs

 
Pretty decent timing. However, close to 15 sec is quite a long time.
 
In the next case:
 
>From the above query, I suspected m_office tables and its link so I modified the query
a bit. I chose an office / region with multiple office under it.
 
select 
concat(repeat("..", ((LENGTH(ounder.`hierarchy`) - LENGTH(REPLACE(ounder.`hierarchy`, '.',
'')) - 1))), ounder.`name`) as "Office",
lo.display_name as "Loan Officer", 
f.`name` as Fund, 
pl.name as Product, 
count(l.id) as '# Loans',
count(c.account_no) as '# Clients',
sum(l.principal_amount) as "Loan Amount", 
sum(l.principal_outstanding_derived) as "Principal Outstanding"
from m_office o 
join m_office ounder on ounder.hierarchy like concat(o.hierarchy, '%') and ounder.hierarchy
like concat('.', '%')
join m_client c on c.office_id = ounder.id
join m_group g on g.office_id = ounder.id
join m_loan l on l.client_id = c.id or l.group_id = g.id
join m_product_loan pl on pl.id = l.product_id
left join m_staff lo on lo.id = l.loan_officer_id
left join m_currency cur on cur.code = l.currency_code
left join m_fund f on f.id = l.fund_id
left join m_loan_arrears_aging laa on laa.loan_id = l.id
where 
o.id = 11
and (l.currency_code = "INR" or "-1"[...] 
Showing rows 0 - 10 (11 total, Query took 874.2660 sec) 

Status <http://10.0.3.218/phpmyadmin/url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Fgeneral-thread-states.html&token=e4376b4e8e2f98572fdfc0f778416d88>


Time


Starting

23 µs


Waiting For Query Cache Lock

8 µs


Checking Query Cache For Query

314 µs


Checking Permissions

9 µs


Checking Permissions

4 µs


Checking Permissions

4 µs


Checking Permissions

3 µs


Checking Permissions

3 µs


Checking Permissions

3 µs


Checking Permissions

3 µs


Checking Permissions

3 µs


Checking Permissions

3 µs


Checking Permissions

7 µs


Opening Tables

77 µs


System Lock

17 µs


Waiting For Query Cache Lock

31 µs


Init

195 µs


Optimizing

75 µs


Statistics

673 µs


Preparing

84 µs


Creating Tmp Table

766 µs


Executing

10 µs


Copying To Tmp Table

874.3 s


Sorting Result

115 µs


Sending Data

103 µs


End

8 µs


Removing Tmp Table

388 µs


End

12 µs


Query End

9 µs


Closing Tables

28 µs


Freeing Items

53 µs


Logging Slow Query

5 µs


Logging Slow Query

4 µs


Cleaning Up

9 µs

 
>From the above two profile table, post query execution, MySQL takes time to update the
temp table. This may mean that the likely issues is with the way m_office table is designed.
Including the office hierarchy structure in the same table.
 
Most of the reports are based on the queries that places m_office as the first table and then
other tables are linked. It would we better if we can split the office ID and the hierarchy
into two tables and link them using SQL.
 
Anyother thoughts are welcome.
 
-- 
Immanuel Jeyaraj
m: +91 95970 04069 / +91 94455 47572
e: irj@sefier.in <mailto:irj@sefier.in>  / sefierin@gmail.com <mailto:sefierin@gmail.com>

w: http://sefier.in

Mime
  • Unnamed multipart/related (inline, None, 0 bytes)
View raw message