problems with bulk insertion and "bulk validation" in Rails

Question

I'm using ar-extensions' import feature to do bulk import and it's quick, but not as quick as I'd like. Two problems I am seeing from the logs:

1. I still see individual SQL insert statements - why isn't it doing multirow insertion?

2. I have a :validates_uniqueness_of and I see that it does a SELECT for every row to do it. Is there a "bulk validation" way it could just select everything with a WHERE clause and validate the uniqueness that way instead?

I'm hesitant to drop down to SQL for doing this, so any suggestions - or using a different gem/plugin? Thanks!

Answer 1

I use FasterCSV on a couple projects and it seems fast enough. However, it does NOT do "multirow inserts". I don't think ActiveRecord is able to do that, specially is validations are involved.

Using your DB's native import mechanism will allways be faster, but you will lose some of AR's goodness - validations and filters, mostly.

Answer 2

I use an instance_attribute (@bulk_loading) in my model for when I'm doing bulk insertions. If the variable is true, then some of the validations are not run.

As egarcia says, currently AR doesn't support multirow insertions.

validate_uniqueness_of validation speedups

1) create a unique index in the dbms to do the check and do NOT use AR for the check. -- Just catch the appropriate error from the dbms driver when the insert fails due to violating the unique index

2) create a non-unique index in the db for the uniqueness_validation and check (using db query analysis techniques) that the index is being used and the sql uniqueness check is executing as fast as possible.