I'm working on long-awaited support for prepared statements in ActiveRecord. Before I finish my work, I would like to know whether the Ruby on Rails core team accepts my design, and whether they will review my patch (once it is finished) seriously. I have documented the design at: http://izumi.plan99.net/blog/?p=35
Nicely done! But am I to understand StatementBuilder code is going to replace (cleanup) all the string hacks currently involved in building SQL queries? Will the usage of prepared statements in MySQL/PostgreSQL become implicit with this? Also, did you follow the "ActiveRecord refactoring" thread [1]? Did Zach [2][3] really do it?
On May 14, 7:15 pm, "Mislav Marohnić" <mislav.maroh...@gmail.com> wrote:
> Nicely done! But am I to understand StatementBuilder code is going to > replace (cleanup) all the string hacks currently involved in building SQL > queries? Will the usage of prepared statements in MySQL/PostgreSQL become > implicit with this?
Thanks. :) The use of prepared statements in MySQL/PostgreSQL will completely replace the argument escaping stuff. Database adapters that don't support prepared statements (or haven't implemented support yet) will silently fallback to argument escaping, as is done now.
> Also, did you follow the "ActiveRecord refactoring" > thread [1]? Did Zach [2][3] really do it?
On 5/14/07, Hongli Lai <hongli...@gmail.com> wrote:
> The use of prepared statements in MySQL/PostgreSQL will > completely replace the argument escaping stuff.
So, every query ever issued will become a prepared statement. Isn't this going to lead to unnecessary overhead? I mean, there are *a lot* of various SELECT statements generated by even a simple Rails application. Does it really make sense to prepare every one of them? Having backends cache the execution plan for rare SELECTs can possibly have undesirable overhead.
Also, how will ActiveRecord know how to reuse these statements? For example:
a = Author.find(1) post = Author.posts.first post.author
The first and the last query are different to AR, yet they take a same shape ("SELECT * FROM authors WHERE authors.id = ?") and can both be represented by a single prepared statement. With your solution, will AR know how to reuse the prepared statement in different places, or will it try to prepare it twice?
> So, every query ever issued will become a prepared statement. Isn't this > going to lead to unnecessary overhead? I mean, there are *a lot* of various > SELECT statements generated by even a simple Rails application. Does it > really make sense to prepare every one of them? Having backends cache the > execution plan for rare SELECTs can possibly have undesirable overhead. > Also, how will ActiveRecord know how to reuse these statements? For example:
> a = Author.find(1) > post = Author.posts.first > post.author
> The first and the last query are different to AR, yet they take a same shape > ("SELECT * FROM authors WHERE authors.id = ?") and can both be represented > by a single prepared statement. With your solution, will AR know how to > reuse the prepared statement in different places, or will it try to prepare > it twice?
The current implementation immediately removes prepared statements after using them once, so they will be reparsed twice. Right now I'm trying to make it actually work and getting the framework in place. Optimization can be done later (and I'm fairly confident that it can be optimized). In the future I can add caching. The cache will have a limited size, and items can be removed based on least-recently-used or some other criteria, similar to how CPU caches work.
On 5/14/07, Hongli Lai <hongli...@gmail.com> wrote:
> The current implementation immediately removes prepared statements > after using them once
Doesn't that negate the whole idea of having prepared statements in the database in the first place? I don't think Rails core team would accept the patch that slows down ActiveRecord just for the sake of using a database feature (in a wrong way).
On May 14, 9:07 pm, "Mislav Marohnić" <mislav.maroh...@gmail.com> wrote:
> Doesn't that negate the whole idea of having prepared statements in the > database in the first place? I don't think Rails core team would accept the > patch that slows down ActiveRecord just for the sake of using a database > feature (in a wrong way).
Actually, no. One of the problems with quoting string manually is excessive memory usage in Rails. For example, if you're uploading a 1 MB photo to your database, then Rails has to quote a 1 MB string, which is very slow and results in more than 1 MB memory usage because of string overheads. Using prepared statements will remove this problem. And I'm not sure that using prepared statements (and immediately deleting them) really makes things slower. Why would it be? After all, if you're not using prepared statements then the database will have to reparse each statement every time anyway. This will need more benchmarking. The prepared statement caching that I mentioned should be trivial to implement. It's on my todo list.
On 5/14/07, Hongli Lai <hongli...@gmail.com> wrote:
> And I'm not sure that using prepared statements (and immediately > deleting them) really makes things slower. Why would it be?
It'll be somewhat slower than just throwing a SQL statement at the database because it adds extra synchronous communication associated with preparing the statement. In most cases it'll be negligible.
From past experiences, I would expect to see a noticeable, if not huge, gain from caching prepared statements. At least, with Oracle.
On 5/14/07, Alexey Verkhovsky <alexey.verkhov...@gmail.com> wrote:
> From past experiences, I would expect to see a noticeable, if not > huge, gain from caching prepared statements. At least, with Oracle.
I'd dare to say that Oracle isn't exactly very popular in Rails world. The majority uses Postgres and MySQL, and they would see a noticable gain only when these prepared statements were reused for multiple queries.
On May 14, 10:35 pm, "Alexey Verkhovsky" <alexey.verkhov...@gmail.com> wrote:
> On 5/14/07, Mislav Marohnić <mislav.maroh...@gmail.com> wrote:
> > On 5/14/07, Alexey Verkhovsky <alexey.verkhov...@gmail.com> wrote: > > I'd dare to say that Oracle isn't exactly very popular in Rails world.
> It is in my corner of the woods. Let me not mention that evil E-word again :)
> > and they would see a noticable gain only when these prepared statements > > were reused for multiple queries.
> It's the same with Oracle. Prepared statement is merely a way to avoid > hitting SQL parser and query optimizer.
That's all good and well but right now I have my hands full on making it *work* (I have a ton of unit test failures to fix). I promise you I will take care of performance once this actually works.
On 5/14/07, Hongli Lai <hongli...@gmail.com> wrote:
> Are there any other discussion points left?
Yes. In your place I would contact Zach to get an update on his progress. If he really did some refactoring like he said, it is most likely that he made some statement builder class himself. Picking up where he left off in this area would be a boost.
I wish you luck with the project! Hopefully your cleanup will finally make AR::Base (and related files) SQL-free; connection adapters and "statements builders" should handle that logic.
