Hi all,
Need help on optimizing a query!
Grameen Koota generates a eod report on the amount collected across all its
branches as loan repayment to calculate the repayment rate and loan
portfolio after the days outstanding.
Currently GK is retrieving this data from "loan_schedule" table in mifos
which is a table with huge data.
The query attached with this mail is being used to get the data mentioned
above.
This query currently takes more than 12 min to run to the details on our 44
branches database. Currently the no. of records in this table are 15532000
records in the table.
Can any body suggest us how do we optimize the query or is there any other
table to retrieve the data faster? Or if this is the minimal time?
Sorry if the data provided is not clear enough!
Need to run this everyday!
Regards,
Naganand
AGM [Information Technology Department]
Grameen Koota,
Avalahalli, Anjanapura Post
Bangalore- 560062
Email: [EMAIL PROTECTED]
[EMAIL PROTECTED]
Phone: 080-28436838,
Mob: 9341940803
Website: www.grameenkoota.org
Select p.prd_offering_short_name as prdname,
sum(l.principal_paid)/100000 as Actualprincipal,
sum(l.interest_paid)/100000 as InterestIncome,
sum(l.misc_fees_paid)/100000 as EF
from account a
left join loan_account la on a.account_id = la.account_id
left join loan_schedule l on la.account_id = l.account_id
left join prd_offering p on la.prd_offering_id = p.prd_offering_id
WHERE
date_format(l.payment_date,'%Y%m%d') = date_format('2007-12-11','%Y%m%d') and
a.account_type_id = 1
group by p.prd_offering_id-------------------------------------------------------------------------
SF.Net email is sponsored by:
Check out the new SourceForge.net Marketplace.
It's the best place to buy or sell services
for just about anything Open Source.
http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace
