[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

comp.lang.ruby

Active Record speed

Andrew Libby

10/30/2006 8:52:00 PM


Hello,

I'm writing some code that loads data into a database on a
routine basis (dump from legacy system daily). I've got a
decent amount of data, and am loading it now using ActiveRecord.

I'm finding that it's taking quite a while longer to process
than I had hoped. To see if it's my ruby, or ActiveRecord
that is the cause I'd like to write an implementation that
loads the data using the underlying ActiveRecord connection
rather than ActiveRecord objects.

I come from a Perl DBI and JDBC world, and would like to use
prepared statements. So I've gotten the underlying Mysql
connection (using ActiveRecord::Base.connection.raw_connection).

When I have code like

stmt = conn.prepare(%Q/
INSERT INTO sometable (t1,t2,t3,t4)
VALUES (?,?,?,?)
/)

bind_params = [1,2,3,4]

stmt.execute(bind_params)

I get an error on the execute statement. It claims I need
to send it 4 parameters. I'd like to send it an array
containing the paramaters because I build the prepared
statement based on the format of my data file. The
bind_params is then an array which is the result of a split
on a line in a data file.

So what's the best way to handle this? Should I begin to
use Ruby's DBI rather than just steeling the underlying
ActiveRecord connections?

Thanks.

Andy


--
Andrew Libby
Tangeis, LLC
Innovative IT Management Solutions
alibby@tangeis.com
3 Answers

Robert Klemme

10/30/2006 9:09:00 PM

0

Andrew Libby wrote:
>
> Hello,
>
> I'm writing some code that loads data into a database on a routine basis
> (dump from legacy system daily). I've got a decent amount of data, and
> am loading it now using ActiveRecord.
>
> I'm finding that it's taking quite a while longer to process than I had
> hoped. To see if it's my ruby, or ActiveRecord that is the cause I'd
> like to write an implementation that loads the data using the underlying
> ActiveRecord connection rather than ActiveRecord objects.
>
> I come from a Perl DBI and JDBC world, and would like to use prepared
> statements. So I've gotten the underlying Mysql
> connection (using ActiveRecord::Base.connection.raw_connection).
>
> When I have code like
>
> stmt = conn.prepare(%Q/
> INSERT INTO sometable (t1,t2,t3,t4)
> VALUES (?,?,?,?)
> /)
>
> bind_params = [1,2,3,4]
>
> stmt.execute(bind_params)

You probably just need to change the line above to

stmt.execute(*bind_params)

> I get an error on the execute statement. It claims I need to send it 4
> parameters. I'd like to send it an array containing the paramaters
> because I build the prepared statement based on the format of my data
> file. The bind_params is then an array which is the result of a split
> on a line in a data file.
>
> So what's the best way to handle this? Should I begin to use Ruby's DBI
> rather than just steeling the underlying ActiveRecord connections?

Kind regards

robert

Jacob Fugal

10/30/2006 9:11:00 PM

0

On 10/30/06, Andrew Libby <alibby@tangeis.com> wrote:
> I come from a Perl DBI and JDBC world, and would like to use
> prepared statements. So I've gotten the underlying Mysql
> connection (using ActiveRecord::Base.connection.raw_connection).

My guess is that prepared statements are indeed the source of your
performance problems. IIRC, ActiveRecord does not cache prepared
statements by default (and if there's an option for it, I do not know
of it) so you're essentially calling prepare once for each INSERT! For
most Rails applications this is a space for improvement but not a show
stopper. For importing loads of data, it's simply unacceptable.

> When I have code like
>
> stmt = conn.prepare(%Q/
> INSERT INTO sometable (t1,t2,t3,t4)
> VALUES (?,?,?,?)
> /)
>
> bind_params = [1,2,3,4]
>
> stmt.execute(bind_params)
>
> I get an error on the execute statement. It claims I need
> to send it 4 parameters.

Unlike in perl (which I'm assuming you're used to from the symptoms
here), arrays and lists are not *quite* the same thing in Ruby. When
you call stmt.execute(bind_params), you are not passing a list of four
parameters to execute (as you might expect), but just one parameter
that is an array. Fortunately, Ruby does provide a mechanism for
"splatting" an array into a list of parameters:

stmt.execute(*bind_params) # note the star

Let us know if this takes care of it for you!

Jacob Fugal

Andrew Libby

10/30/2006 9:51:00 PM

0


Robert, Jaccob you two nailed it. Thanks a bunch.
It's subtle, the difference between a list and an array
especially with a perl background.

Thanks!

Andy



Jacob Fugal wrote:
> On 10/30/06, Andrew Libby <alibby@tangeis.com> wrote:
>> I come from a Perl DBI and JDBC world, and would like to use
>> prepared statements. So I've gotten the underlying Mysql
>> connection (using ActiveRecord::Base.connection.raw_connection).
>
> My guess is that prepared statements are indeed the source of your
> performance problems. IIRC, ActiveRecord does not cache prepared
> statements by default (and if there's an option for it, I do not know
> of it) so you're essentially calling prepare once for each INSERT! For
> most Rails applications this is a space for improvement but not a show
> stopper. For importing loads of data, it's simply unacceptable.
>
>> When I have code like
>>
>> stmt = conn.prepare(%Q/
>> INSERT INTO sometable (t1,t2,t3,t4)
>> VALUES (?,?,?,?)
>> /)
>>
>> bind_params = [1,2,3,4]
>>
>> stmt.execute(bind_params)
>>
>> I get an error on the execute statement. It claims I need
>> to send it 4 parameters.
>
> Unlike in perl (which I'm assuming you're used to from the symptoms
> here), arrays and lists are not *quite* the same thing in Ruby. When
> you call stmt.execute(bind_params), you are not passing a list of four
> parameters to execute (as you might expect), but just one parameter
> that is an array. Fortunately, Ruby does provide a mechanism for
> "splatting" an array into a list of parameters:
>
> stmt.execute(*bind_params) # note the star
>
> Let us know if this takes care of it for you!
>
> Jacob Fugal
>

--
Andrew Libby
Tangeis, LLC
Innovative IT Management Solutions
alibby@tangeis.com