CODEWITHSUNDEEP
rubyperformanceactiverecordsqlite
svart
svart

Reputation: 108

SQLite3 Activerecord is too slow

I've been using the activerecord adapter for sqlite (without rails), and when I try to insert anything it seems to be too slow. For instance when I do ~250 inserts it could easily take more than 5 minutes! My schema is the following:

create_table :courses do |t|
    t.string :title
    t.integer :ethmmy_id
end

add_index :courses, :ethmmy_id

create_table :announcements do |t|
    t.string :title
    t.string :author
    t.string :body
    t.string :uhash
    t.belongs_to :courses
    t.timestamps null: false
end

add_index :announcements, :courses_id

The ActiveRecord models I use are the following:

class Course < ActiveRecord::Base
    validates :ethmmy_id, uniqueness: true
    has_many :announcements
end

class Announcement < ActiveRecord::Base
    validates :uhash, uniqueness: true
    belongs_to :course
end

I've tried modifying the PRAGMAS like setting the journal in memory or setting synchronous I/O off but there doesn't seem to be much of a difference. The data is fetched from a web crawler but that is not the bottleneck, as the crawler is pretty fast by itself.

More spefically I've noticed that it freezes every 10 or so inserts to write to the db, but it seems to be to slow. I've tried adding the creations into a single transaction like this:

ActiveRecord::Base.transaction do
    Course.all.each do |course|
         Announcement.create(....)
    end
end

But still there is no performance gain.

I've even tried to have the whole db in more just for the sake of testing, and the whole process still took about 5 minutes for just 250 inserts. The debug log shows that the SQL queries are only about 0.1-0.2 ms each, and at certain inserts (the same ones every time) the whole thing seems to freeze there for some seconds.

UPDATE: After using ruby-prof to find where most of the time is spent, I found out that 80% and more of the time is spent on IO.select. What is this method and what calls it?

Upvotes: 2

Views: 514

Answers (1)

Albin
Albin

Reputation: 3012

Have you considered changing to mysql instead of sqlite?

Anyway, one way of making the inserts quicker is to do them in raw sql instead of through activerecord. Each time you call create there are almost 10 callbacks that are triggered and stuff like that.

Createing one single sql query to insert a whole bunch of data can look like this:

base_sql = 'INSERT INTO announcements (`title`, `author`, `body`, `uhash`, `course_id`) VALUES '
announcements = []
Course.all.each do |course|
  announcements << [title, author, body, uhash, course.id]
end

values_sql = announcements.map { |announcement| "(#{announcement.join(', ')})" }.join(', ')
ActiveRecord::Base.connection.execute(base_sql + values_sql)

you will of course have to substitute what you place in the announcements array with real values.

I use something similar in one of my projects and we create 38 000 records in less than one minute on my laptop.

Since one of the main performance advantages is that no callbacks are triggered you can insert data that are illegal according to your activerecord validations. This is important to remember and take in to account.

The uniqueness validation might be enforced by your database however so that might still be enforced.

Upvotes: 1

Related Questions