Hi Naganand,
In the local database we don't have records are for testing. Anyway,
here are my suggestions on this Query for performance.
1) If we are indexing loan_schedule.payment_date, then definitely this
will improve the performance as it is used in the where clause. If
functional index is supporting then create it (
Date_Format(payment_date) ), otherwise in the query replace
Date_Format(l.payment_date) with l.payment_date only for not suppressing
the index.
2) Account table and Loan_account table have 'Account_Id' as primary
key. So use 'Inner Join' instead of 'left join' between these two
tables.
3) As stated in the forwaded mail, indexing columns 'principal_paid,
interest_paid, misc_fees_paid won't improve performance because they are
not used in the where clause and thus won't have any impact on the query
performance.
After indexing the column loan_schede.payment_date, the following query
will perform better.
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
INNER 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-17','%Y%m%d') and a.account_type_id = 1
group by p.prd_offering_id
Thanks & Regards,
Saurabh Kumar * Developer * SunGard * Offshore Services * Divyasree
Chambers, Langford Road, Bangalore 560025 India
Tel +91-80-2222-0501 * Mobile +91-9886945575 * Fax +91-80-2222-0511 *
<http://www.sungard.com/> www.sungard.com
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Girish Naik
Sent: Friday, December 14, 2007 1:31 PM
To: [email protected]
Subject: Re: [Mifos-developer] Grameen Koota - Need Help with
queryoptimization
Hi Naganand,
I dont have a huge DB for testing but I have following things might
help:
* The columns 'principal_paid' , 'interest_paid' and
'misc_fees_paid' in table 'loan_schedule' is not indexed, I think
indexing them may help for their fast retrieval and calculation.
* In the where clause you are changing the date format in
DB, instead the date can be calculated in the code for current date and
one day before, and in query we can do a between current date and one
day before
Regards,
Girish Naik
From: "Naganand" <mailto:[EMAIL PROTECTED]> <[EMAIL PROTECTED]>
Date: Fri, 14 Dec 2007 10:56:04
To:"'Developer'" <mailto:[email protected]>
<[email protected]>
Subject: [Mifos-developer] Grameen Koota - Need Help with query
optimization
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] <mailto:[EMAIL PROTECTED]>
<mailto:[EMAIL PROTECTED]>
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
<mailto:[EMAIL PROTECTED]>
Phone: 080-28436838,
Mob: 9341940803
Website: www.grameenkoota.org <http://www.grameenkoota.org>
<http://www.grameenkoota.org>
------------------------------------------------------------------------
-
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/marketp
lace
-------------------------------------------------------------------------
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
