Forum: Ruby on Rails AR-extensions didn't speed things up -- why not?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Cd9b5c14565e85b8877b9dabbbf78368?d=identicon&s=25 andy (Guest)
on 2009-02-26 04:26
(Received via mailing list)
I was excited to try AR-extensions (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
F86901feca747abbb5c6c020362ef2e7?d=identicon&s=25 Zach Dennis (zdennis)
on 2009-03-04 17:23
(Received via mailing list)
On Feb 25, 10:25 pm, andy <aegg...@gmail.com> 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?
Cd9b5c14565e85b8877b9dabbbf78368?d=identicon&s=25 Andy Eggers (Guest)
on 2009-03-04 17:33
(Received via mailing list)
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
F86901feca747abbb5c6c020362ef2e7?d=identicon&s=25 Zach Dennis (zdennis)
on 2009-03-04 17:57
(Received via mailing list)
On Mar 4, 11:32 am, Andy Eggers <aegg...@gmail.com> 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
Cd9b5c14565e85b8877b9dabbbf78368?d=identicon&s=25 Andy Eggers (Guest)
on 2009-03-04 20:09
(Received via mailing list)
Well, that solves it. And no I don't know how to do more efficient
importing of data with sqlite.

Thanks,
Andy
Aad37b5f7116c8d1f547d23b37566032?d=identicon&s=25 Greg Donald (destiney)
on 2009-03-04 21:52
(Received via mailing list)
On Wed, Mar 4, 2009 at 10:56 AM, zdennis <zach.dennis@gmail.com> 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 Donald
http://destiney.com/
6883e5ef03484d4fcef507d7b4f1d243?d=identicon&s=25 Matt Jones (Guest)
on 2009-03-05 17:39
(Received via mailing list)
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 Jones
B6ed830f9e0c67ecfcb7489ffdb321b7?d=identicon&s=25 Marcus Persson (mpl)
on 2009-03-19 11:44
Greg Donald wrote:
> On Wed, Mar 4, 2009 at 10:56 AM, zdennis <zach.dennis@gmail.com> 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 Donald
> http://destiney.com/

Reference for future readers -- a transaction does the trick. An order
of magnitude faster.
This topic is locked and can not be replied to.