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:sefie...@gmail.com] Sent: 09 September 2016 10:40 To: Mifos software development <mifos-develo...@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: i...@sefier.in <mailto:i...@sefier.in> / sefie...@gmail.com <mailto:sefie...@gmail.com> w: http://sefier.in
