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