Rails core - May 2007 - Request for comments: prepared statements in ActiveRecord

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

Please comment on this.
Thank you.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

On 5/14/07, Hongli Lai <honglilai@gmail.com>
wrote:
>
>
> 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?

[1]
http://groups.google.com/group/rubyonrails-core/browse_thread/thread/32659af615f48fd5/1d9170c2e1b05979
[2] http://www.continuousthinking.com/
[3]
http://groups.google.com/groups/profile?enc_user=WZYiNRUAAADUiWRahsdnVJcZQgJLgcVW9h3i3SmjGmAJbX05nZ-8fQ

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

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?
>
> [1]http://groups.google.com/group/rubyonrails-core/browse_thread/thread/...
> [2]http://www.continuousthinking.com/
> [3]http://groups.google.com/groups/profile?enc_user=WZYiNRUAAADUiWRahsdn...
I didn''t follow any of these threads, but I have read the
ActiveRecord::Extensions website in the past.

This prepared statements support is entirely my own work, and is not
related to Zach''s work.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

On 5/14/07, Hongli Lai <honglilai@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?

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

On May 14, 8:25 pm, "Mislav Marohnić"
<mislav.maroh...@gmail.com>
wrote:
> 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.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

On 5/14/07, Hongli Lai <honglilai@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).

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

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.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

On 5/14/07, Hongli Lai <honglilai@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.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

On 5/14/07, Alexey Verkhovsky <alexey.verkhovsky@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.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

On 5/14/07, Mislav Marohnić <mislav.marohnic@gmail.com>
wrote:
> On 5/14/07, Alexey Verkhovsky <alexey.verkhovsky@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.

--
Alex

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

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.

Are there any other discussion points left?


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

On 5/14/07, Hongli Lai <honglilai@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.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

Alexey Verkhovsky wrote:
>> 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.
I''ve done some testing w/ Oracle, and a very significant gain comes
from
simply using real bind variables. Caching the prepared statements in the 
client doesn''t matter much at all, presuming you''re going to
have
multiple clients. But the use of real bind variables allows Oracle 
itself to cache the statements and avoid multiple hard parses.




--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

Here''s an update. I noticed that using prepared statements will indeed
make MySQL queries a bit slower. The unit tests in an unmodified Rails
edge source tree took 18 seconds. After adding prepared statements,
they took 28 seconds. I''ve been working on optimizing this since
yesterday evening, and I succeeded.
Using prepared statements is only advantageous in MySQL if the
arguments are large (i.e. uploading a large image), so the code will
now fallback to _not_ using prepared statements if it detects that no
argument is larger than 32 KB. The unit tests now take 18 seconds
again, thus eliminating the performance problem entirely. Something
similar can be implemented in other adapters.

It noticed this comment in activerecord/test/binary_test.rb:
  # Without using prepared statements, it makes no sense to test
  # BLOB data with SQL Server, because the length of a statement is
  # limited to 8KB.
  #
  # Without using prepared statements, it makes no sense to test
  # BLOB data with DB2 or Firebird, because the length of a statement
  # is limited to 32KB.
So it does make sense to add support for prepared statements in Rails.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

On May 15, 11:17 am, Hongli Lai <hongli...@gmail.com>
wrote:
> Here''s an update. I noticed that using prepared statements will
indeed
> make MySQL queries a bit slower. The unit tests in an unmodified Rails
> edge source tree took 18 seconds. After adding prepared statements,
> they took 28 seconds. I''ve been working on optimizing this since
> yesterday evening, and I succeeded.
> Using prepared statements is only advantageous in MySQL if the
> arguments are large (i.e. uploading a large image), so the code will
> now fallback to _not_ using prepared statements if it detects that no
> argument is larger than 32 KB. The unit tests now take 18 seconds
> again, thus eliminating the performance problem entirely. Something
> similar can be implemented in other adapters.
>
> It noticed this comment in activerecord/test/binary_test.rb:
>   # Without using prepared statements, it makes no sense to test
>   # BLOB data with SQL Server, because the length of a statement is
>   # limited to 8KB.
>   #
>   # Without using prepared statements, it makes no sense to test
>   # BLOB data with DB2 or Firebird, because the length of a statement
>   # is limited to 32KB.
> So it does make sense to add support for prepared statements in Rails.
And I forgot to say this in my last email. Before I added the fallback
code, I implemented a cache for compiled prepared statements. The
cache hit rate is 65%, but it resulted in no noticeable performance
improvement, at least on MySQL.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

On 5/14/07, Hongli Lai <honglilai@gmail.com>
wrote:
>
> 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.
I know this is tangential, but is there any good reason for storing
images in the database?

I can think of a few, but they're not very good;
  - security (but that can be handled with http or other auth).
  - logging (you want to send your images through rails so you know
the # of downloads)
  - ??

I've always advised people that filesystem is preferrable (it's a
file, keep in in the file system) since you can easily back it up, and
you don't have to hit rails to get the image (sloooow, and uses up an
app listener).

If you ignore the problem of quoting 1mb strings from image uploads,
what other reason is there for prepared statements?

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

On May 15, 11:55 pm, Courtenay <court3...@gmail.com>
wrote:
> On 5/14/07, Hongli Lai <hongli...@gmail.com> wrote:
> I know this is tangential, but is there any good reason for storing
> images in the database?
>
> I can think of a few, but they''re not very good;
>   - security (but that can be handled with http or other auth).
>   - logging (you want to send your images through rails so you know
> the # of downloads)
>   - ??
- All data is in one place. That may make backupping easier.
- Support for transactions. Suppose that you not only store the image,
but also some metadata. If I first save the image file on disk, and
then save the metadata, but the computer crashes during metadata
saving, then I''m left with a stale file that''s not used. If I
first
save the metadata and then saves the image file, and the computer
crashes during image file saving, then I have a corrupted image file
but intact metadata.

> If you ignore the problem of quoting 1mb strings from image uploads,
> what other reason is there for prepared statements?
Oracle support. Oracle lives by prepared statements, even if those
prepared statements aren''t reused, as someone has pointed out. One can
argue that few Rails developers use Oracle, but I can also argue that
that''s because Rails performs badly on Oracle.

Furthermore:
- My code will fallback to using "unprepared statements" if it thinks
that using prepared statements is not necessary, so performance is not
a problem.
- It moves a lot of the statement building code to separate classes.
This makes ActiveRecord::Base smaller, and thus easier to read and to
maintain.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

On 5/14/07, Hongli Lai <honglilai@gmail.com>
wrote:
> 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
>
> Please comment on this.
> Thank you.
I like the gist of your idea and approach. Thanks for working on it.

I''d start with using bound variables first, though. They''re
always
beneficial and are a natural stepping stone to prepared statements
which can be added later for those queries which may benefit (many do
not).

Sorry I didn''t comment on your earlier postgres patch: it includes a
lot of superfluous style edits and untested changes, so I didn''t
review further. I salute your effort but it could use a lot of cleanup
before committing.

Are you attending RailsConf this week?

jeremy

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

Just wondering, but is there any advantage to trying to manually
manage the execution plan cache? I know MSSQL-Server for example
caches the execution-plan of every parameterized Query (anything
executed with the sp_executesql stored-procedure), which means the
majority of ADO and ADO.NET based applications, and no one on that
side of the fence would ever worry about such a thing; the server
manages that cache itself, exactly as it manages what indexes to keep
in memory at any given moment.

So I guess my question is: Is flushing the statements really necessary
or are you just working under an assumption the database (Postgres/
MySQL) will blindly allow it to grow to unreasonable levels?

On May 15, 4:17 am, Hongli Lai <hongli...@gmail.com>
wrote:
> Here''s an update. I noticed that using prepared statements will
indeed
> make MySQL queries a bit slower. The unit tests in an unmodified Rails
> edge source tree took 18 seconds. After adding prepared statements,
> they took 28 seconds. I''ve been working on optimizing this since
> yesterday evening, and I succeeded.
> Using prepared statements is only advantageous in MySQL if the
> arguments are large (i.e. uploading a large image), so the code will
> now fallback to _not_ using prepared statements if it detects that no
> argument is larger than 32 KB. The unit tests now take 18 seconds
> again, thus eliminating the performance problem entirely. Something
> similar can be implemented in other adapters.
>
> It noticed this comment in activerecord/test/binary_test.rb:
>   # Without using prepared statements, it makes no sense to test
>   # BLOB data with SQL Server, because the length of a statement is
>   # limited to 8KB.
>   #
>   # Without using prepared statements, it makes no sense to test
>   # BLOB data with DB2 or Firebird, because the length of a statement
>   # is limited to 32KB.
> So it does make sense to add support for prepared statements in Rails.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

Hongli Lai wrote:
> Oracle support. Oracle lives by prepared statements, even if those
> prepared statements aren''t reused, as someone has pointed out. One
can
> argue that few Rails developers use Oracle, but I can also argue that
> that''s because Rails performs badly on Oracle.
I''d have to disagree with you there. Rails actually performs very well 
with Oracle. A change made a while back to default to "similar" 
cursor_sharing resulted in much of the benefit of bind variables. 
"performs badly" is definitely too strong.



--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

Jeremy Kemper wrote:
> I''d start with using bound variables first, though.
They''re always
> beneficial and are a natural stepping stone to prepared statements
> which can be added later for those queries which may benefit (many do
> not).
 From my tests w/ Oracle, all the benefit was in the use of (real, aka 
database) bind variables. For a while now I''ve had a working (passes
all
tests) implementation of AR that uses bind variables for inserts and 
updates, those being by far the easiest to tweak. Given how widely 
spread throughout the codebase the selects are, I haven''t had the time 
to think through a reasonable approach there.

Though I like what was suggested (by you I think) back in Feb, around 
the idea of SqlString < String. Has anyone looked into that further?
> Are you attending RailsConf this week?
Perhaps a group of us could get together to dig into this?





--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

On May 16, 1:11 am, "Jeremy Kemper" <jer...@bitsweat.net>
wrote:
> I like the gist of your idea and approach. Thanks for working on it.
>
> I''d start with using bound variables first, though.
They''re always
> beneficial and are a natural stepping stone to prepared statements
> which can be added later for those queries which may benefit (many do
> not).
Thanks. :) Though I don''t exactly understand what you mean -
aren''t
prepared statements and bounded variables the same thing? That is, is
it not so that one cannot exist without the other?
My patch uses both prepared statements and bounded variables, in case
that wasn''t clear.

> Sorry I didn''t comment on your earlier postgres patch: it includes
a
> lot of superfluous style edits and untested changes, so I didn''t
> review further. I salute your effort but it could use a lot of cleanup
> before committing.
No need to apologize. And I''d like to add a minor correction: the
PostgreSQL patch isn''t mine, I''m merely trying to get
attention on
that patch, because it has a lot of fixes that I depend on.

> Are you attending RailsConf this week?
I will not be attending RailsConf.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

On May 16, 5:49 am, "Michael A. Schoen" <scho...@earthlink.net>
wrote:
> I''d have to disagree with you there. Rails actually performs very
well
> with Oracle. A change made a while back to default to "similar"
> cursor_sharing resulted in much of the benefit of bind variables.
> "performs badly" is definitely too strong.
I have no experience with Oracle, but according to some blogs, such as
this one:
http://uncommentedbytes.blogspot.com/2006/07/major-enterprise-ruby-on-rails-issues.html
Rails performs badly because of the lack of use of prepared
statements. Here is a quote from Greg Luck:
"We have two production applications running on Ruby. And how is it.
Well, despite being perhaps no more than 5% of the functionality of
our applications, Ruby on Rails is the number one consumer of Oracle
CPU and logical gets. Why? Rails does not support prepared statements,
so Oracle has to reparse every time."

And I forgot to mention this last time, but there''s one more reason
for using prepared statements/bounded variables: binary support in
PostgreSQL. I have a Rails app which uses PostgreSQL, and its
Person.password_password column was of the type bytea. It contains a
raw hash of the user''s password. When a user logs in, the app
performed the following query:
Person.find_by_username_and_password_hash(username,
calculate_hash(password))
This will send a query to PostgreSQL, and this query will contain a
binary string. However, in PostgreSQL, binary strings must be escaped
with escape_bytea() - the regular string escaping function will not
do. Because the binary string isn''t escaped properly, the whole
database connection breaks (it throws an exception, something about
"character not allowed in Unicode mode" or something).

One may wonder why the binary string isn''t escaped correctly. The
reason is this:
1. It seems impossible in Ruby to determine whether a String contains
binary data without scanning through the entire String (expensive), so
the current PostgreSQL adapter assumes that strings are text strings,
and escapes them as text strings, unless it knows that this string
belongs to a binary column.
2. During ActiveRecord::Base.save, ActiveRecord will now which
attributes belong to which columns, so saving records that contain
binary data is no problem. The string escaper will have knowledge
about which argument belongs to which column.
3. However, things go wrong during ActiveRecord::Base.find. In there,
it is impossible to find out which arguments belong to which table
columns.

By using prepared statements/bounded variables, we can eliminate this
entire problem. Strings will not be escaped, but be directly bounded
to a prepared statements.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

On May 16, 5:42 am, Sam Smoot <ssm...@gmail.com>
wrote:
> So I guess my question is: Is flushing the statements really necessary
> or are you just working under an assumption the database (Postgres/
> MySQL) will blindly allow it to grow to unreasonable levels?
I''m sorry, I do not understand what you mean by "flushing
statements"
and "allow it to grow to unreasonable levels".


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

Hongli Lai wrote:
> On May 16, 5:49 am, "Michael A. Schoen"
<scho...@earthlink.net> wrote:
> 
>>I''d have to disagree with you there. Rails actually performs
very well
>>with Oracle. A change made a while back to default to
"similar"
>>cursor_sharing resulted in much of the benefit of bind variables.
>>"performs badly" is definitely too strong.
> 
> 
> I have no experience with Oracle, but according to some blogs, such as
> this one:
>
http://uncommentedbytes.blogspot.com/2006/07/major-enterprise-ruby-on-rails-issues.html
> Rails performs badly because of the lack of use of prepared
> statements. Here is a quote from Greg Luck:
> "We have two production applications running on Ruby. And how is it.
> Well, despite being perhaps no more than 5% of the functionality of
> our applications, Ruby on Rails is the number one consumer of Oracle
> CPU and logical gets. Why? Rails does not support prepared statements,
> so Oracle has to reparse every time."
This was before Rails 1.2 which introduced the cursor_sharing=similar 
session parameter. It''s oracle-specific and makes the database rewrite 
the queries on the fly to use bind variables instead of literal values.

Btw even before 1.2 you may had used that parameter (only database-wide 
  instead of on a single application).

In any case using real bind variable would represent a further 
optimization step, as it''d allow a fine tuning of which bind variables 
vs literal values use in each query.

Luca
-- 

Web:   http://spazidigitali.com - http://thetyper.com
Email mailto://luca.mearelli@gmail.com
Skype callto://l.mearelli
--

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

On 5/16/07, Hongli Lai <honglilai@gmail.com>
wrote:
>
>
> On May 16, 5:42 am, Sam Smoot <ssm...@gmail.com> wrote:
> > So I guess my question is: Is flushing the statements really necessary
> > or are you just working under an assumption the database (Postgres/
> > MySQL) will blindly allow it to grow to unreasonable levels?
>
> I''m sorry, I do not understand what you mean by "flushing
statements"
> and "allow it to grow to unreasonable levels".

The former refers to your intention to drop prepared statements after each
query. The latter is what would happen if you didn''t cleanup these
prepared
statements - you''d suddenly have hundreds of them in the database. I
think
databases *would* blindly allow it to grow to unreasonable levels - can they
do otherwise? Prepared statements were designed to be made manually; I
don''t
think any of us can predict what happens when you have an insane number of
them active, eg. would it be a hog, would it take up memory, etc.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

Luca Mearelli wrote:
> Hongli Lai wrote:
> In any case using real bind variable would represent a further 
> optimization step, as it''d allow a fine tuning of which bind
variables
> vs literal values use in each query.
Agreed. In my tests the _client_ performance impact of using real bind 
variables instead of relying on cursor_sharing of similar was basically 
non-existent, but there was some (small, but still some) server 
improvement. Plus some Oracle query plans worked better (ie., required 
fewer hints) when constants were left as constants.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

On 5/15/07, Michael A. Schoen <schoenm@earthlink.net>
wrote:
> Jeremy Kemper wrote:
> > I''d start with using bound variables first, though.
They''re always
> > beneficial and are a natural stepping stone to prepared statements
> > which can be added later for those queries which may benefit (many do
> > not).
>
>  From my tests w/ Oracle, all the benefit was in the use of (real, aka
> database) bind variables. For a while now I''ve had a working
(passes all
> tests) implementation of AR that uses bind variables for inserts and
> updates, those being by far the easiest to tweak. Given how widely
> spread throughout the codebase the selects are, I haven''t had the
time
> to think through a reasonable approach there.
>
> Though I like what was suggested (by you I think) back in Feb, around
> the idea of SqlString < String. Has anyone looked into that further?
I wish :)

> > Are you attending RailsConf this week?
>
> Perhaps a group of us could get together to dig into this?
That''d be awesome. I''ll be at the hackfest at the Jupiter
hotel the
nights of the 17th-19th.
It''s at 800 E Burnside St, about a half mile from the convention
center.

jeremy

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

Jeremy Kemper wrote:
> On 5/15/07, Michael A. Schoen <schoenm@earthlink.net> wrote:
>>Though I like what was suggested (by you I think) back in Feb, around
>>the idea of SqlString < String. Has anyone looked into that further?
> 
> I wish :)
> 
I actually started this morning to look into it ;)
>>>Are you attending RailsConf this week?
>>
>>Perhaps a group of us could get together to dig into this?
> 
> 
> That''d be awesome. I''ll be at the hackfest at the Jupiter
hotel the
> nights of the 17th-19th.
> It''s at 800 E Burnside St, about a half mile from the convention
center.
aahh I won''t be at the conf... any way to meet in a virtual space? 
perhaps the #railsconf irc channel?

Luca

-- 

Web:   http://spazidigitali.com - http://thetyper.com
Email mailto://luca.mearelli@gmail.com
Skype callto://l.mearelli
--

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

On May 16, 1:04 pm, "Mislav Marohnić"
<mislav.maroh...@gmail.com>
wrote:
> The former refers to your intention to drop prepared statements after each
> query. The latter is what would happen if you didn''t cleanup these
prepared
> statements - you''d suddenly have hundreds of them in the database.
I think
> databases *would* blindly allow it to grow to unreasonable levels - can
they
> do otherwise? Prepared statements were designed to be made manually; I
don''t
> think any of us can predict what happens when you have an insane number of
> them active, eg. would it be a hog, would it take up memory, etc.
I tested this with MySQL. I can''t have unlimited number of prepared
statements. After a certain threshold, queries will fail.
My MySQLAdapter implementation caches up to 32 prepared statements
(this number can be changed). If the cache is full, the least recently
used one will be removed. Though, according to benchmarks, caching
prepared statements doesn''t improve performance at all on MySQL.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

On 5/17/07, Hongli Lai <honglilai@gmail.com>
wrote:
>
>
> I tested this with MySQL. I can''t have unlimited number of
prepared
> statements. After a certain threshold, queries will fail.

It would be good to know what is this treshold.

... according to benchmarks, caching
> prepared statements doesn''t improve performance at all on MySQL.

It is possible that there is a performance boost on raw queries, but that
it''s not visible because the ActiveRecord object instantiation time is
an
order of magnitude higher.

Also, simple queries against a database that has a miniscule amount of
records don''t benefit much from having a cached execution plan. You
could
first benchmark several hundred thousand INSERTs, then UPDATE those records,
then query those tables with SELECTs with as much conditions and joins you
could throw in. Don''t forget the database indexes which play an
important
role in laying out the execution plan.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

On May 18, 12:58 am, "Mislav Marohnić"
<mislav.maroh...@gmail.com>
wrote:
> It would be good to know what is this treshold.
For some reason I''m unable to reproduce that problem now. Perhaps I
was mistaken.

> It is possible that there is a performance boost on raw queries, but that
> it''s not visible because the ActiveRecord object instantiation
time is an
> order of magnitude higher.
>
> Also, simple queries against a database that has a miniscule amount of
> records don''t benefit much from having a cached execution plan.
You could
> first benchmark several hundred thousand INSERTs, then UPDATE those
records,
> then query those tables with SELECTs with as much conditions and joins you
> could throw in. Don''t forget the database indexes which play an
important
> role in laying out the execution plan.
I''ve searched Google for benchmarks on prepared-vs-unprepared
statements, and to my surprise I couldn''t find any. I''ll try
to setup
a good benchmark.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

On May 17, 9:55 pm, Luca Mearelli <luca.meare...@gmail.com>
wrote:
> aahh I won''t be at the conf... any way to meet in a virtual space?
> perhaps the #railsconf irc channel?
Yes, I''d be delighted to. :) I hope we can solve this together.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

Thank you so much for working on this feature.  Prepared statement/
bind variable support is critical for Oracle projects, especially in
the (evil) enterprise world.  I know that Rails isn''t targeted for
large enterprise apps, but that doesn''t mean it isn''t targeted
at
_enterprises_.  Lots of small and medium-sized internal apps would
benefit from Rails, but interoperability/ROI may dictate Oracle as the
database.  Enterprise DBAs may (rightly, IMHO) scoff at a framework
that lacks prepared statement support.

Brian Hartin

On May 14, 11:36 am, Hongli Lai <hongli...@gmail.com>
wrote:
> 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
>
> Please comment on this.
> Thank you.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

Very interesting to see you working on this project. I work a lot for
Enterprises and it makes a lot of difference when it comes to Oracle
with prepared statements and data binding. In case of loops etc. we
had find the performance difference to be in multiples.

I was just wondering how far this development has reached and when I
would be able to use it. Planning a large rails project around Oracle
soon. Good thing I know that the existing rails work and in future we
would be able to get much better performance.

Deepak


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

On Aug 17, 6:21 pm, Deepak Kumar <deepak1...@gmail.com>
wrote:
> Very interesting to see you working on this project. I work a lot for
> Enterprises and it makes a lot of difference when it comes to Oracle
> with prepared statements and data binding. In case of loops etc. we
> had find the performance difference to be in multiples.
>
> I was just wondering how far this development has reached and when I
> would be able to use it. Planning a large rails project around Oracle
> soon. Good thing I know that the existing rails work and in future we
> would be able to get much better performance.
>
> Deepak
Good to see someone appreciates my work. :) MySQL support is probably
done. I was still working on PostgreSQL support. There''s no Oracle
support though as I don''t have access to an Oracle database, but
modifying the Oracle adapter should be fairly trivial.
The work is currently on hold as I''m busy with other things, but I
intent to resume this work in the near future.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---

Hi Hongli,
> > Very interesting to see you working on this project. I work a lot for
> > Enterprises and it makes a lot of difference when it comes to Oracle
> > with prepared statements and data binding. In case of loops etc. we
> > had find the performance difference to be in multiples.
>
> > I was just wondering how far this development has reached and when I
> > would be able to use it. Planning a large rails project around Oracle
> > soon. Good thing I know that the existing rails work and in future we
> > would be able to get much better performance.
>
> Good to see someone appreciates my work. :) MySQL support is probably
> done. I was still working on PostgreSQL support. There''s no Oracle
> support though as I don''t have access to an Oracle database, but
> modifying the Oracle adapter should be fairly trivial.
> The work is currently on hold as I''m busy with other things, but I
> intent to resume this work in the near future.
Did I hear you live in the Enschede region? :-) I do too and would
like to offer my help to implement the PostgreSQL side of things. Send
me a note if you''d like to meet up sometime and I''d gladly
share
drinks and thoughts.

--
Roderick van Domburg
http://www.nedforce.nl


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com
To unsubscribe from this group, send email to
rubyonrails-core-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en
-~----------~----~----~----~------~----~------~--~---