Re: [Mifos-developer] Grameen Koota - Need Help with query optimization

Girish Naik Fri, 14 Dec 2007 00:01:56 -0800

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" <[EMAIL PROTECTED]>

Date: Fri, 14 Dec 2007 10:56:04 
To:"'Developer'" <[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]> 
             [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> 
Phone: 080-28436838, 
Mob: 9341940803 
Website: 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/marketplace

begin:vcard
fn:Girish Naik
n:Naik;Girish
org:Neev Information Technologies Private Limited;Engineering
adr:;;R.T. Nagar;Bangalore;Karnataka;560032;India
email;internet:[EMAIL PROTECTED]
title:Software Development Lead
tel;cell:+919342534024
x-mozilla-html:TRUE
url:http://neevtech.com
version:2.1
end:vcard

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

Re: [Mifos-developer] Grameen Koota - Need Help with query optimization

Reply via email to