Active Record speed

Discussion in 'Ruby' started by Andrew Libby, Oct 30, 2006.

  1. Andrew Libby

    Andrew Libby Guest

    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
     
    Andrew Libby, Oct 30, 2006
    #1
    1. Advertising

  2. 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
     
    Robert Klemme, Oct 30, 2006
    #2
    1. Advertising

  3. Andrew Libby

    Jacob Fugal Guest

    On 10/30/06, Andrew Libby <> 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
     
    Jacob Fugal, Oct 30, 2006
    #3
  4. Andrew Libby

    Andrew Libby Guest

    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 <> 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
     
    Andrew Libby, Oct 30, 2006
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Ham

    I need speed Mr .Net....speed

    Ham, Oct 28, 2004, in forum: ASP .Net
    Replies:
    6
    Views:
    2,359
    Antony Baula
    Oct 29, 2004
  2. efiedler
    Replies:
    1
    Views:
    2,083
    Tim Ward
    Oct 9, 2003
  3. Replies:
    2
    Views:
    2,314
    Howard
    Apr 28, 2004
  4. Replies:
    2
    Views:
    347
    Christopher Benson-Manica
    Apr 28, 2004
  5. David Heinemeier Hansson
    Replies:
    14
    Views:
    228
    Joel VanderWerf
    Jul 28, 2004
Loading...

Share This Page