Forum: Ruby on Rails ActiveRecord & Prepared Statement

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.
08a8169980150afdfb0fafd4f7d3b1c4?d=identicon&s=25 Leonardo Francalanci (Guest)
on 2006-03-23 16:21
(Received via mailing list)
I searched the archives and found a thread about it, but I didn't
understand: are prepared statement used by ActiveRecord on those
databases that could handle them (Postgresql, Mysql, Oracle etc)?
In case they are not used, are they planned? Is there a roadmap about
Rails I can find somewhere?
6edd67c92a1dab5eb23fed79f3c18564?d=identicon&s=25 David Heinemeier Hansson (Guest)
on 2006-03-23 17:14
(Received via mailing list)
> I searched the archives and found a thread about it, but I didn't
> understand: are prepared statement used by ActiveRecord on those
> databases that could handle them (Postgresql, Mysql, Oracle etc)?
> In case they are not used, are they planned? Is there a roadmap about
> Rails I can find somewhere?

They are not used.

The roadmap for Rails is to continue to accept patches around issues
that affect most people most of the time. So the best way to get
prepared statements into Rails is to do the work yourself. Or hire
someone to do it for you.
--
David Heinemeier Hansson
http://www.loudthinking.com -- Broadcasting Brain
http://www.basecamphq.com   -- Online project management
http://www.backpackit.com   -- Personal information manager
http://www.rubyonrails.com  -- Web-application framework
08a8169980150afdfb0fafd4f7d3b1c4?d=identicon&s=25 Leonardo Francalanci (Guest)
on 2006-03-23 18:01
(Received via mailing list)
> The roadmap for Rails is to continue to accept patches around issues
> that affect most people most of the time. So the best way to get
> prepared statements into Rails is to do the work yourself. Or hire
> someone to do it for you.

I asked if there was a roadmap just to see if anyone else was already
working on it, not because I want someone to do it for me.
Anyway, in case I decide "to do the work myself", is anyone else working
on it, so that I could join the development and help?
25e11a00a89683f7e01e425a1a6e305c?d=identicon&s=25 Wilson Bilkovich (Guest)
on 2006-03-23 21:03
(Received via mailing list)
On 3/23/06, Leonardo Francalanci <Leonardo.Francalanci@commprove.com>
wrote:
> > The roadmap for Rails is to continue to accept patches around issues
> > that affect most people most of the time. So the best way to get
> > prepared statements into Rails is to do the work yourself. Or hire
> > someone to do it for you.
>
> I asked if there was a roadmap just to see if anyone else was already
> working on it, not because I want someone to do it for me.
> Anyway, in case I decide "to do the work myself", is anyone else working
> on it, so that I could join the development and help?
>

The problem with prepared statements is that they are typically much
slower than normal statements, the first few times.  If you're not
doing several hundred calls to the same piece of SQL, they are often
slower than normal statements.
The other advantage they usually offer is protection from SQL
injection, and the ActiveRecord :conditions code already handles that.

It would be nice to move the statement handling/quoting into the
database adapter classes, though, because then different adapters
could either use the superclass implementation, or define their own.
That would make it easier to enable prepared statements for adapters
that could benefit from that.
08a8169980150afdfb0fafd4f7d3b1c4?d=identicon&s=25 Leonardo Francalanci (Guest)
on 2006-03-24 09:46
(Received via mailing list)
> The problem with prepared statements is that they are typically much
> slower than normal statements, the first few times.  If you're not
> doing several hundred calls to the same piece of SQL, they are often
> slower than normal statements.

Well, who doesn't do several hundred calls to the same piece of SQL
(with different parameter values, of course)? I would say that's the
regular way of quering a database (i.e. select a,b,c from taba join tabb
on taba.k=tabb.g where taba.id=$parameter). For example one of Oracle's
performance monitors parameter is actually the time spent on parsing and
calculate a query plan vs executing the query itself.


> It would be nice to move the statement handling/quoting into the
> database adapter classes, though, because then different adapters
> could either use the superclass implementation, or define their own.

Yes, that is what I was thinking about, because different databases
handle it in a different way. Now: in Java you can use
PreparedStatements and/or simple Statements, it's up to the programmer.
I think that would be the right choice, as opposed to having the
language picking one for you. So: should ActiveRecord *always* use
preparedstatements when available or there should be some kind of
"switch" (a new parameter in query calls for example) that the user can
use to decide for preparedstatement or regular statement?
25e11a00a89683f7e01e425a1a6e305c?d=identicon&s=25 Wilson Bilkovich (Guest)
on 2006-03-25 02:22
(Received via mailing list)
On 3/24/06, Leonardo Francalanci <Leonardo.Francalanci@commprove.com>
wrote:
> calculate a query plan vs executing the query itself.
> language picking one for you. So: should ActiveRecord *always* use
> preparedstatements when available or there should be some kind of
> "switch" (a new parameter in query calls for example) that the user can
> use to decide for preparedstatement or regular statement?
>

If the connection adapters were more 'subclass-friendly', I'd say that
it would be worth supporting both.  However, there are a decent number
of explicit classname checks in the Rails code that would need to be
dealt with.
If prepared statements were determined to be worth implementing, I'd
say just switch them on in every adapter that supported them.
I'm interested in the idea.

How do we make this work in a share-nothing environment, though?
Ruby process #1:
statement.prepare("select * from blah where id = ?")
STDERR.puts statement.object_id
statement.execute(123)

Ruby process #2:
statement.prepare("select * from blah where id = ?")
STDERR.puts statement.object_id
statement.execute(555)

We don't want to re-prepare the statement the next time.. but we don't
have any global-scope place to store the statement handle.
Is Oracle smart enough to reuse an identical statement if one has
already been prepared?  Do we suddenly have to start worrying about
the number of cursors being created?

Other than that, it shouldn't be hard to do a research patch for this.
4005a47a8f2ceee49670b920593c1d52?d=identicon&s=25 Ben Munat (Guest)
on 2006-03-25 09:01
(Received via mailing list)
Wilson Bilkovich wrote:
> We don't want to re-prepare the statement the next time.. but we don't
> have any global-scope place to store the statement handle.

How about using DRb... or EZ's wonderfully simple adapter for it?

b

PS: I can't resist... here's yet another use-case for a long-running
"operating
environment" for rails... a "servlet container".
5ce55767684e0cd1727db2b8b8975640?d=identicon&s=25 Steve Longdo (Guest)
on 2006-03-25 09:20
(Received via mailing list)
The connection to the database is shared.  Seems like the logical place
to
put in some sort "query cache" for compiled stored procedures or other
often
used bits of SQL.  Granted some duplication would happen as more than
one
connection to the DB may be needed.
Eea7ad39737b0dbf3de38874e0a6c7d8?d=identicon&s=25 Justin Forder (Guest)
on 2006-03-25 09:35
(Received via mailing list)
Wilson Bilkovich wrote:
[...]
> Ruby process #2:
> statement.prepare("select * from blah where id = ?")
> STDERR.puts statement.object_id
> statement.execute(555)
>
> We don't want to re-prepare the statement the next time.. but we don't
> have any global-scope place to store the statement handle.
> Is Oracle smart enough to reuse an identical statement if one has
> already been prepared?  Do we suddenly have to start worrying about
> the number of cursors being created?

Prepared statements are tied to specific connections. There's a MySQL
example here:

   http://dev.mysql.com/doc/refman/5.0/en/mysql-stmt-...

in which the line

   stmt = mysql_stmt_init(mysql);

creates a statement using the parameter mysql, which is required to be a
valid connection handle.

Similarly, in JDBC the method prepareStatement is a method of
Connection.

So AFAIK prepared statements would need to be cached in each Ruby
process.

regards

   Justin
This topic is locked and can not be replied to.