ActiveRecord vs SQL

Hi% username%!

Recently, I began to study Ruby on Rails and I was faced with the task of importing data from a CSV file into a MySQL table. The task is not complicated and I wrote the code quite quickly, but only I was very surprised that while it was being executed, I managed to pour myself some coffee and smoke a cigarette.

- Something is wrong here! - I thought and began to dig.

For tests, I created a test_object model, a table in the database:

 create_table: test_objects do | t |
	 t.column: field1,: integer
	 t.column: field2,: integer
	 t.column: field3,: integer
	 t.column: field4,: integer
	 t.column: field5,: integer
 end

and wrote a small script:
')

 values ​​= []
 5000.times do
	 values.push ({: field1 => rand (10000),: field2 => rand (10000),: field3 => rand (10000), 
				 : field4 => rand (10000),: field5 => rand (10000)})
 end
 values.each do | item |
	 TestObject.new (item) .save
 end

Runtime: ~ 30 seconds in development environment, ~ 22 seconds - production. Too much ...

Then I remembered the ar-extension plugin, which I stumbled upon in search of implementing “INSERT ... ON DUPLICATE KEY UPDATE" sql queries on rails. It allows you to insert data into the table in one query.

 require 'ar-extensions'
 require 'ar-extensions / adapters / mysql'
 require 'ar-extensions / import / mysql'

 ....

 objs = []
 values.each do | item |
	 objs.push (TestObject.new (item))
 end
 TestObject.import objs

Runtime: ~ 14 seconds in the development environment, ~ 12 seconds - production. Already better, but still a lot.

Then I decided to try to stop using ActiveRecord in this piece of code, and use a simple sql query.

 sql = ActiveRecord :: Base.connection ()
 values.each do | item |
	 sql.execute ("INSERT INTO` test_objects` (`field1`,` field2`, `field3`,` field4`, `field5`) 
		 VALUES ('# {item [: field1]}', '# {item [: field2]}', '# {item [: field3]}', '# {item [: field4]}', '# {item [: field5]} ') ")
 end

Runtime: ~ 5 seconds in the development environment, ~ 3.5 seconds - production.

And if you use transactions

 sql = ActiveRecord :: Base.connection ()
 sql.execute ("START TRANSACTION")
 values.each do | item |
	 sql.execute ("INSERT INTO` test_objects` (`field1`,` field2`, `field3`,` field4`, `field5`) 
		 VALUES ('# {item [: field1]}', '# {item [: field2]}', '# {item [: field3]}', '# {item [: field4]}', '# {item [: field5]} ') ")
 end
 sql.execute ("COMMIT")

Runtime: ~ 2 seconds in development environment, ~ 0.8 seconds - production! Much faster than using ActiveRecord!

If you use bulk-insert (thanks to CWN and Ventura ):

 objs = []
 values.each do | item |
	 objs.push ("('# {item [: field1]}', '# {item [: field2]}', '# {item [: field3]}', '# {item [: field4]}}, '# {item [: field5]}') ")
 end
 sql.execute ("INSERT INTO` test_objects` (`field1`,` field2`, `field3`,` field4`, `field5`) VALUES" + objs.join (','))

Runtime: ~ 0.1-0.2 seconds in production environment!

Conclusion: ActiveRecord is a hell of a handy thing, and in no case I urge you to abandon its use, but in those parts of the code where its wide capabilities and speed are important, it is better to use ordinary SQL queries.

UPD: Added time to work in production environment and test using bulk-insert