Faster/bulk activerecord creation

Question

My application is storing location data from GPS inputs. When importing a GPX file, a user can have from 500 - 10,000 GPS datapoints. Right now, I have a model for each trackpoint. It's working great, but on insert it is SLOW. 30+ seconds for the 10,000 datapoints. Is there any better way of bulk inserting?

All the time is spent on the SQL side - each insert is quick, but 10,000 add up fast. Each user might have 100 files, and 100 users == long long insert times. Not all at once of course.

I'd be happy to change the application architecture if that would help, just not sure what alternatives I have here. I only ever use the GPS data as a unit. I never search for one record in the set, so the whole ActiveRecord is overkill.

I'd hate to have to do a whole queue system just to handle this one silly insert.

Answer 1

Been thinking this over, and seems like my two options are serialize a hash to a blob in the DB, or store it elsewhere. I don't have FS access, but could use S3.

So I suppose I've changed my question. For storing 100kb objects that are pretty much read-only after creation, any feedback on general principles of S3 vs blob storage?

I think this thread probably covers what i'm looking for for now!

Answer 2

Use ar-extensions's import method to import them all at once: http://github.com/zdennis/ar-extensions/blob/master/ar-extensions/lib/ar-extensions/import.rb#L49-215

The problem is that if you import each record by doing a #save, you are in fact creating one insert statement per row, and calling all of your model's validations. You can instead construct a bigger sql insert statement that pushes all data in at once.