thr3ads.net - Rails - AR-extensions didn''t speed things up -- why not? [Feb 2009]

If this information is useful, please help other people find it:
Share via:

andy

2009-Feb-26 03:25 UTC

AR-extensions didn''t speed things up -- why not?

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





--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

zdennis

2009-Mar-04 16:22 UTC

head link

Re: AR-extensions didn''t speed things up -- why not?

On Feb 25, 10:25 pm, andy
<aegg...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> 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?
What database are you using?

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Andy Eggers

2009-Mar-04 16:32 UTC

head link

Re: AR-extensions didn''t speed things up -- why not?

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


On Mar 4, 11:22 am, zdennis
<zach.den...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> On Feb 25, 10:25 pm, andy
<aegg...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
>
>
> > 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?
>
> What database are you using?--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

zdennis

2009-Mar-04 16:56 UTC

head link

Re: AR-extensions didn''t speed things up -- why not?

On Mar 4, 11:32 am, Andy Eggers
<aegg...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
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

>
> Andy
>
> On Mar 4, 11:22 am, zdennis
<zach.den...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
>
>
> > On Feb 25, 10:25 pm, andy
<aegg...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > > 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?
>
> > What database are you using?--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Andy Eggers

2009-Mar-04 19:05 UTC

head link

Re: AR-extensions didn''t speed things up -- why not?

Well, that solves it. And no I don''t know how to do more efficient
importing of data with sqlite.

Thanks,
Andy

On Mar 4, 11:56 am, zdennis
<zach.den...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> On Mar 4, 11:32 am, Andy Eggers
<aegg...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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
>
>
>
> > Andy
>
> > On Mar 4, 11:22 am, zdennis
<zach.den...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > > On Feb 25, 10:25 pm, andy
<aegg...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > > > 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?
>
> > > What database are you using?--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Greg Donald

2009-Mar-04 20:51 UTC

head link

Re: AR-extensions didn''t speed things up -- why not?

On Wed, Mar 4, 2009 at 10:56 AM, zdennis
<zach.dennis-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
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/

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Matt Jones

2009-Mar-05 16:38 UTC

head link

Re: AR-extensions didn''t speed things up -- why not?

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

On Mar 4, 3:51 pm, Greg Donald
<gdon...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> On Wed, Mar 4, 2009 at 10:56 AM, zdennis
<zach.den...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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 Donaldhttp://destiney.com/--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Marcus Persson

2009-Mar-19 10:44 UTC

head link

Re: AR-extensions didn''t speed things up -- why not?

Greg Donald wrote:> On Wed, Mar 4, 2009 at 10:56 AM, zdennis
<zach.dennis-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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.
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Rails - Feb 2009 - AR-extensions didn't speed things up -- why not?

AR-extensions didn''t speed things up -- why not?

Re: AR-extensions didn''t speed things up -- why not?

Re: AR-extensions didn''t speed things up -- why not?

Re: AR-extensions didn''t speed things up -- why not?

Re: AR-extensions didn''t speed things up -- why not?

Re: AR-extensions didn''t speed things up -- why not?

Re: AR-extensions didn''t speed things up -- why not?

Re: AR-extensions didn''t speed things up -- why not?