Topic: Application getting poor performance due to bulk of data

hi everyone,

     I am having the table called  ' sku_wise_summary_outlet_level_reports '. i have written one script using crontab which populates 8402 records for every day.
At the starting level,sku_wise_summary_outlet_level_reports table having minimum amount of records. so that rails app is running as fast.
But after 1 year, sku_wise_summary_outlet_level_reports table having nearly 3200000 records.So the performance is very slow.
I need to imporve the performance.I think the large volume of data is the cause of performence.Can u pls tell me the solution to overcome this problem.

MY RAILS APP CODE:
-------------------
@skus=Sku.all -- @skus having 27 records
@id = @regions.map { |region| region['id'] }.join(',')
@outlet_category_id = @outlet_categories.map { |outlet_category| outlet_category['id'] }.join(',')
@outlet_class_id = @outlet_classes.map { |region| outlet_class['id'] }.join(',')

for sku in @skus

pre_closing_offtake=SkuWiseSummaryOutletLevelReport.sum("order_qty",:conditions=>["region_id in (#{@id}) and outlet_category_id in (#{@outlet_category_id}) and outlet_class_id in (#{@outlet_class_id}) and sku_id=#{sku.id} and date='#{params[:date][:from].to_date}'"])

currrent_closing_offtake=SkuWiseSummaryOutletLevelReport.sum("order_qty",:conditions=>["region_id in (#{@id}) and outlet_category_id in (#{@outlet_category_id}) and outlet_class_id in (#{@outlet_class_id}) and sku_id=#{sku.id} and date='#{params[:date][:to].to_date}' "])
                 
total_offtake_order=SkuWiseSummaryOutletLevelReport.sum("order_qty",:conditions=>["region_id in (#{@id}) and outlet_category_id in (#{@outlet_category_id}) and outlet_class_id in (#{@outlet_class_id}) and sku_id=#{sku.id} and date between '#{params[:date][:from].to_date}' and '#{params[:date][:to].to_date-1}' "])       
               
total_order=SkuWiseSummaryOutletLevelReport.sum("order_qty",:conditions=>["region_id in (#{@id}) and outlet_category_id in (#{@outlet_category_id}) and outlet_class_id in (#{@outlet_class_id}) and sku_id=#{sku.id} and date between '#{params[:date][:from].to_date}' and '#{params[:date][:to].to_date}' "])

offtake=((pre_closing_offtake.to_f+total_offtake_order.to_f)-currrent_closing_offtake.to_f).to_i         

end

Rails app is taking 3 secs to fetch the records from sku_wise_summary_outlet_level_reports table for one sku.
'for loop' excecutes 27 times and refer sku_wise_summary_outlet_level_reports table as 27 * 4 = 108 seconds.

so my rails app have taken for 27 skus are = 27  * 3 seconds..

in future, sku_wise_summary_outlet_level_reports table records will be increased as twice..

-willm

Re: Application getting poor performance due to bulk of data

I would need to get a lot more intimate with your data structure and logic so as to give specific advice.  (For one you seem to be adding the value on params[:date][:from] twice...  May make sense just that I don't see the logic behind.)

Here are a few points, though, that might be worth checking:

  1. Isn't there a way to rephrase your calculation so that it requires fewer selects? I have the hunch that you can go down by at least one but I'm not positive.

  2. Do you have indices on the table? Are they the useful ones? (at least the ones on all used columns)

  3. Isn't there a way to rephrase logic so that "in" clauses could be table joins? They are usually faster.

  4. Could you, perhaps, move some of the old data into another table?  (Though, depending on the RDBMS you use, an index on date is nearly as good.)

  5. You calculate total_order but never seem to use it.

Note that there seems to be a typo in the word "currrent_closing_offtake" (three r's) but it looks consistent.

Re: Application getting poor performance due to bulk of data

You need to find out precisely what is taking a long time.  Can you identify the sql statements that are generated and determine whether or not that is the problem or whether or not the issue is ActiveRecord?  I've used a few approaches to speed up slow data model accesses in rails.. they range from speeding up the underlying DB access (by adding index) to encapsulating many operations in one transaction.

Last edited by ehud93 (2010-01-12 19:49:31)

Play postseason fantasy sports at Fantasy Postseason - Written in RoR

Re: Application getting poor performance due to bulk of data

Post stuff in code formatting on the forum.


Like these

It is easier to read. Also try to let people know what code goes where, what is in your model, controller and view. Also posting the schema to the table that is slow would be of help too.

Last edited by cherring (2010-01-12 02:18:04)

Re: Application getting poor performance due to bulk of data

Without much exageration, i say 3.2 Million records is nothing - it shouldn't show any noticable performance lags, assuming your setup is done right and you're not running the database on a shitty hosting plan

Do you have indexes set up for each of those columns you match in your queries?