fineract-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Immanuel Jeyaraj <sefie...@gmail.com>
Subject Query profiling
Date Fri, 09 Sep 2016 05:09:46 GMT
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:


StatusDocumentation
<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)

StatusDocumentation
<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 / sefierin@gmail.com
w: http://sefier.in

Mime
View raw message