# AR-extensions didn't speed things up -- why not? **URL:** \ **Category:** Rails\ **Created:** [February 26, 2009, 4:26am UTC](https://www.ruby-forum.com/t/ar-extensions-didnt-speed-things-up-why-not/161389 "2009-02-26T04:26:05Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![andy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/aeb1de/32.png) [@andy](https://www.ruby-forum.com/u/andy)\ **Post date:** [February 26, 2009, 4:26am UTC](https://www.ruby-forum.com/t/ar-extensions-didnt-speed-things-up-why-not/161389/1 "2009-02-26T04:26:05Z")
I was excited to try AR-extensions ([http://www.continuousthinking.com/](http://www.continuousthinking.com/) tags/arext) to do a bunch of data importing that had been really slow. But then I was disappointed to see that it actually didn’t help and wondered if I was missing something. I’m finding that it actually goes faster to do batch.each{|l| Quote.create(:date =\> l[0], :company\_id =\> l [1], :daily\_return =\> l[2])} than to do Quote.import([:date, :company\_id, :daily\_return], batch) In the script/server log when I do Quote.import I get an INSERT statement for each line of the input, like this: SQL (0.005698) INSERT INTO “quotes” (“date”,“company\_id”,“daily\_return”) VALUES(‘20040803’, 3210,'0.002108 ‘) SQL (0.008398) INSERT INTO “quotes” (“date”,“company\_id”,“daily\_return”) VALUES(‘20040804’, 3210,’-0.004628 ') and when I do a bunch of iterated #create calls I get statements like this: Quote Create (0.000774) INSERT INTO “quotes” (“daily\_return”, “price”, “date”, “company\_id”, “abnormal\_return”, “volume”) VALUES (0.020922, NULL, ‘2008-11-28’, 428, NULL, NULL) Quote Create (0.000689) INSERT INTO “quotes” (“daily\_return”, “price”, “date”, “company\_id”, “abnormal\_return”, “volume”) VALUES (-0.026083, NULL, ‘2008-12-01’, 428, NULL, NULL) So the import method is doing the same number of SQL INSERT statements; here they happen to be faster although I’m not sure why that is. I expected the import method to speed things up by combining the commits into one statement or something. Is this the expected behavior? Thanks, Andy ---
**Author:** ![andy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/aeb1de/32.png) [@andy](https://www.ruby-forum.com/u/andy)\ **Post date:** [March 4, 2009, 5:23pm UTC](https://www.ruby-forum.com/t/ar-extensions-didnt-speed-things-up-why-not/161389/2 "2009-03-04T17:23:28Z")
On Feb 25, 10:25 pm, andy [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > than to do > SQL (0.008398) INSERT INTO > Quote Create (0.000689) INSERT INTO “quotes” (“daily\_return”, > “price”, “date”, “company\_id”, “abnormal\_return”, “volume”) VALUES > (-0.026083, NULL, ‘2008-12-01’, 428, NULL, NULL) > > So the import method is doing the same number of SQL INSERT > statements; here they happen to be faster although I’m not sure why > that is. I expected the import method to speed things up by combining > the commits into one statement or something. > > Is this the expected behavior? What database are you using? ---
**Author:** ![andy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/aeb1de/32.png) [@andy](https://www.ruby-forum.com/u/andy)\ **Post date:** [March 4, 2009, 5:57pm UTC](https://www.ruby-forum.com/t/ar-extensions-didnt-speed-things-up-why-not/161389/3 "2009-03-04T17:57:38Z")
On Mar 4, 11:32 am, Andy E. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > Thanks for getting back to me, Zach. > > I’m using sqlite3. I had seen somewhere that ar-extensions worked okay > with sqlite. Was I wrong? It works fine with sqlite, but AFAIK sqlite doesn’t provide a mechanism for multiple inserts, so it defaults back to one record per insert. If you know of a way to mold sqlite for efficient importing of data I am all ears. Zach ---
**Author:** ![andy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/aeb1de/32.png) [@andy](https://www.ruby-forum.com/u/andy)\ **Post date:** [March 4, 2009, 5:33pm UTC](https://www.ruby-forum.com/t/ar-extensions-didnt-speed-things-up-why-not/161389/4 "2009-03-04T17:33:28Z")
Thanks for getting back to me, Zach. I’m using sqlite3. I had seen somewhere that ar-extensions worked okay with sqlite. Was I wrong? Andy ---
**Author:** ![andy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/aeb1de/32.png) [@andy](https://www.ruby-forum.com/u/andy)\ **Post date:** [March 4, 2009, 8:09pm UTC](https://www.ruby-forum.com/t/ar-extensions-didnt-speed-things-up-why-not/161389/5 "2009-03-04T20:09:28Z")
Well, that solves it. And no I don’t know how to do more efficient importing of data with sqlite. Thanks, Andy ---
**Author:** ![andy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/aeb1de/32.png) [@andy](https://www.ruby-forum.com/u/andy)\ **Post date:** [March 4, 2009, 9:52pm UTC](https://www.ruby-forum.com/t/ar-extensions-didnt-speed-things-up-why-not/161389/6 "2009-03-04T21:52:47Z")
On Wed, Mar 4, 2009 at 10:56 AM, zdennis [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > If you know of a way to mold sqlite for efficient importing of data I > am all ears. Wrap your inserts in a transaction. – Greg D. [http://destiney.com/](http://destiney.com/) ---
**Author:** ![andy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/aeb1de/32.png) [@andy](https://www.ruby-forum.com/u/andy)\ **Post date:** [March 5, 2009, 5:39pm UTC](https://www.ruby-forum.com/t/ar-extensions-didnt-speed-things-up-why-not/161389/7 "2009-03-05T17:39:08Z")
If you’re inserting a lot of rows, it can also be helpful to wrap the whole operation in an ActiveRecord::Base#benchmark call, like so: Quote.benchmark(“Inserting stuff”) do # insert stuff end This makes your log file more readable, and saves a lot of time (not generating all the log entries). –Matt J. ---
**Author:** ![andy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/aeb1de/32.png) [@andy](https://www.ruby-forum.com/u/andy)\ **Post date:** [March 19, 2009, 11:44am UTC](https://www.ruby-forum.com/t/ar-extensions-didnt-speed-things-up-why-not/161389/8 "2009-03-19T11:44:57Z")
Greg D. wrote: > On Wed, Mar 4, 2009 at 10:56 AM, zdennis [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > > > If you know of a way to mold sqlite for efficient importing of data I > > am all ears. > > Wrap your inserts in a transaction. > > – > Greg D. > [http://destiney.com/](http://destiney.com/) Reference for future readers – a transaction does the trick. An order of magnitude faster.