CODEWITHSUNDEEP
ruby-on-railsdatabaseruby-on-rails-3sqlitedatabase-design
n0denine
n0denine

Reputation: 341

sqlite db is snail slow, how to form a proper db

So I've begun working on a project of which i have to import a ton of records from a xls excel spreadsheet.

The spreadsheet has over 105 columns and 1750 rows of which I used rails console to script create 1 record per column/row entry.... well that came up to just north of 183,000 records and now rails is sluggish as all hell. Even just trying to load Record.last(30) in the action/controller i am trying to load takes well over 5 minutes.

This is a new issue I've run into since my venture into rails, I've never had to work with any large amount of records before.

The data from the spreadsheets are only column(dates) and row(metric). The records are a child of a Video object, so basically im just trying to keep record of a view metric by week for a Video. My problem is there are over 1700 videos and an avg of 90 records per video.

My idea for tomorrow (its late here) is to just create a dates and views attribute for record and store the array of dates inside of :dates and array of views inside :views then in the model create a method to dates.zip(views) and convert them to hashes. Then just call that method when its time to display them to view.

I'm looking for suggestions at the time on how one would properly handle and store large data... what am i doing wrong?

Also just to make this clear, there are no videos or images stored in this application. Only raw data.

Upvotes: 0

Views: 506

Answers (1)

n0denine
n0denine

Reputation: 341

After researching this I actually figured out its just lack of indexing of foreign keys for records. At times i can be calling 60 Videos with 70-130+ associated Records a piece resulting in my db sifting through all 180,000 records for each Video's foreign key 60 times.

After doing some searching I found this. Due to my limited knowledge on database design (even if this was a given) i wasnt aware of the huge difference this made.

Created a new migration AddIndexToVideos with:

class AddIndexToVideos < ActiveRecord::Migration
  def self.up
    add_index :records, :video_id, :name => 'video_id_ix'
  end

  def self.down
    remove_index :records, :video_id, :name => 'video_id_ix'
  end
end

raked it then went from loading records like this:

... Record Load (400.5ms) SELECT "records".* FROM "histories" WHERE "records"."video_id" = 6081 AND (created_at > '2012-02-27 16:12:44.164356') ORDER BY created_at ASC LIMIT 1 ... (there were a lot more) With around 400ms load time each to this:

... Record Load (0.3ms) SELECT "records".* FROM "histories" WHERE "records"."video_id" = 6084 AND (created_at > '2012-02-27 17:32:17.377215') ORDER BY created_at ASC LIMIT 1 ...

Lesson... Always index foreign keys on parent tables that are frequently used. I hope this post helps someone in the future.

Upvotes: 1

Related Questions