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?
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?
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 H.
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
On 3/23/06, Leonardo F. [email protected]
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.
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?
Wilson B. 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”.
On 3/24/06, Leonardo F. [email protected]
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.
Wilson B. 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-execute.html
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
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.