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?
> 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
> 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?
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.
> 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?
On 3/24/06, Leonardo Francalanci <Leonardo.Francalanci@commprove.com> wrote:> > 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? >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 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".
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. On 3/24/06, Wilson Bilkovich <wilsonb@gmail.com> wrote:> > On 3/24/06, Leonardo Francalanci <Leonardo.Francalanci@commprove.com> > wrote: > > > 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? > > > > 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. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060325/9aa62820/attachment.html
Wilson Bilkovich wrote: [...]> 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?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