Rails - Oct 2005 - Active Record: Prepared Statements?

Hello all,


my first project is on it''s way, and I am loving it!

But, and that''s a big BUT:

Active Record does not use prepared statements! How is that? Everybody is
bragging about performance and the most obvious tuning measure is missing.
Ok, I don''t know, if MySQL can even do them as I am using Postgres, but
still.

I started looking at the code and it is not prepared for prepared statements
at all (pun not intended), some major work ahead. I am willing to look into
that, but before I start, I was just wondering if someone else is working on
that too?


Cheers

Hagen


_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Oct 14, 2005, at 6:45 AM,
<sixtus-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
<sixtus-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> Active Record does not use prepared statements! How is that?  
> Everybody is bragging about performance and the most obvious tuning  
> measure is missing. Ok, I don''t know, if MySQL can even do them as
> I am using Postgres, but still.
>
> I started looking at the code and it is not prepared for prepared  
> statements at all (pun not intended), some major work ahead. I am  
> willing to look into that, but before I start, I was just wondering  
> if someone else is working on that too?
Do look into this.  It''s been on the radar for nearly a year but  
hasn''t yet reached anyone''s pain threshold.  Enjoy!

Best,
jeremy
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (Darwin)

iD8DBQFDT+Q8AQHALep9HFYRAvQFAJ4zgazd9amKC1pYui0YMR8C1Ml+owCffIvz
vCbru10hLL4Ke40htowFFAk=apI7
-----END PGP SIGNATURE-----

> > I started looking at the code and it is not prepared for prepared
> > statements at all (pun not intended), some major work ahead. I am
> > willing to look into that, but before I start, I was just wondering
> > if someone else is working on that too?
>
> Do look into this. It''s been on the radar for nearly a year but
> hasn''t yet reached anyone''s pain threshold. Enjoy!

After a first code evaluation this seems a major redesign, so I start a
thought process here and see if anybody joins (pretty please):

1) What''s a prepared statement

Everytime you send an sql-stament to the db, it has to be parsed. This is
time consuming and slowing down, especially since the statements are
frequent and similar. A prepared statements solves this, by preparsing the
statement and just feeding the values.

The Statement:

SELECT * FROM foo WHERE bar = ''value'' LIMIT 1;

Can be tuned to (postgres):

PREPARE plan1 (VARCHAR) AS
SELECT * FROM foo WHERE bar = $1 LIMIT 1;

EXECUTE plan1 (''value'');

And on close

DEALLOCATE plan1;

The "plan1" needs to be unique (obviously)

1a) DON''T UNDERESTIMATE THE PERFORMANCE GAIN!
1b) sorry for shouting

2) Why is it such a problem to put them into active record?

Most of the sql statement is created within base.rb, so we either need to

a) write an adapter that parses the statements from base.rb to create the
statements
b) change the api to let base.rb pass the params along the statements
c) change base.rb to generate prepared statement itself

3)

I prefer option c) as prepared statements should be default and *even* mysql
can do them by now, see
http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html

4) I don''t want to change the whole codebase

5) focusing on create and update will be best

6) Suggestion:

- Create "class AbstractPreparedAdapter < AbstractAdapter" that can
handle
prepared statements
- Create PreparedStatement defining the database specific methods
- Modify base.rb .create and .update to check if an AbstractPreparedAdapter
is available, else fallback

7) Doesn''t look that bad after writing all this


Any feedback? Suggestions?


Cheers

Hagen


_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

> a) write an adapter that parses the statements from base.rb to create
> the statements
> b) change the api to let base.rb pass the params along the statements
> c) change base.rb to generate prepared statement itself
In the end how would this offer more, than the native DB query cache gives
already?

-- 
Company  - http://primalgrasp.com
Thoughts - http://deezsombor.blogspot.com

its not just a query cache. a query cache halds the result for a specific
query (i.e. WHERE id>5 ).. but in prepared statement you can alter the
condition without reparsnig the query. every `id > x` query will be faster
than normal.

On 10/15/05, Dee Zsombor
<dee.zsombor-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
>
> > a) write an adapter that parses the statements from base.rb to create
> > the statements
> > b) change the api to let base.rb pass the params along the statements
> > c) change base.rb to generate prepared statement itself
>
> In the end how would this offer more, than the native DB query cache gives
> already?
>
> --
> Company - http://primalgrasp.com
> Thoughts - http://deezsombor.blogspot.com
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

Onur Turgay wrote:
> its not just a query cache. a query cache halds the result for a
> specific query (i.e. WHERE id>5 ).. but in prepared statement you can
> alter the condition without reparsnig the query. every `id > x` query
> will be faster than normal.
>From the DB''s point is "SELECT something FROM somewhere WHERE
id > ?" is
the same command. If the engine is sophisticated enough to implement
prepared statements it will probably be smart enough to store this query
in a generic way. Beside real security benefits this is one claimed
advantage of using the "?" quoted value substitution.


-- 
Company  - http://primalgrasp.com
Thoughts - http://deezsombor.blogspot.com

sixtus-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org wrote:
> c) change base.rb to generate prepared statement itself
With you on that one, with a couple of provisos.
> I prefer option c) as prepared statements should be default and *even* 
> mysql can do them by now, see 
> http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html
The MySQL C API has had prepared statements since at least 1999, unless 
my memory''s off.  I remember coding to them in 3.23.somethingorother...
> 5) focusing on create and update will be best
Why?  Do you have representative numbers to back that up?  I would have 
thought that the number of SELECTs would far, far outweigh creates and 
updates in an average application - even more so, the number of 
formulaic SELECTs.  I''d love to see the :conditions clause in a 
Model.find take advantage of a prepared statement or two - the 
placeholders are in the right place, and everything...

As far as implementation goes, wouldn''t it be simpler to start by 
replacing the first parameter to 
AR::ConnectionAdapters::WhateverAdapter.execute from being an SQL string 
to allowing a list of [sql_with_placeholders, [parameters, for, 
substitution]]?  The database-specific portions would then all be 
handled in the same place, and base.rb doesn''t need to know about the 
difference between database engines.

The one drawback from this is that you''d need to handle the [sql, 
[parts]] format for adapters that don''t have prepared statements (like 
SQLite), but that''s work that''s currently being done anyway in
base.rb
to handle :conditions, and so on.

It would, however, be a firmer base to start from when extending the 
method to other areas of the code.

Just my 2p :-)
-- 
Alex

Hagen

Thanks for raising this very important issue - it is one of my few  
reservations about Rails, particularly for the search engine i''ve  
written against an oracle database.

In oracle, the only way to build a truly scaleable app is to use bind  
variables, at least for statements that you expect to be executed  
many times per second.  This is to avoid hard re-parsing of every  
statement - an expensive process that requires data dictionary  
latches or locks.

For the time being i''ve had to modify the oci_adapter to convert the  
sql from a single string into a string with place holders plus a hash  
of the arguments.  Thus, i send a string with ? placeholders to AR,  
AR puts it all into a single string, then my hacked oci_adapter tries  
to split it up again.  This is far from perfect.

For oracle, use of bind variables via a prepared statement approach  
should be the default.  Not sure about the other dbs, though i wish i  
was.  If Alex is suggesting the db adapters should receive the sql  
and the bind arguments to deal with, then that seems like a good idea  
to me.  I do think there also needs to be a way to indicate to the  
adapter that bind variables or non-bind variables are preferred for a  
given select statement, or even each variable, because for statements  
that take many seconds to execute you may want oracle to optimize the  
full statement with the actual actual variables rather than just  
placeholders.

Greg

> For the time being i''ve had to modify the oci_adapter to convert
the
> sql from a single string into a string with place holders plus a hash  
> of the arguments.  Thus, i send a string with ? placeholders to AR,  AR 
> puts it all into a single string, then my hacked oci_adapter tries  to 
> split it up again.  This is far from perfect.
Would you mind sharing the code you''ve written to do this? I want to do
the same as well, and I''d be happy to pass back any improvements I come
up with.

BTW, for those doubting the potential impact, I posted a message to this 
list a month or so ago -- I had hacked together a quick test that showed 
a roughly 5x performance improvement when using OCI w/ bind variables.

On 16/10/2005, at 9:02 AM, Michael Schoen wrote:
>> For the time being i''ve had to modify the oci_adapter to
convert
>> the  sql from a single string into a string with place holders  
>> plus a hash  of the arguments.  Thus, i send a string with ?  
>> placeholders to AR,  AR puts it all into a single string, then my  
>> hacked oci_adapter tries  to split it up again.  This is far from  
>> perfect.
>>
>
> Would you mind sharing the code you''ve written to do this? I want
> to do the same as well, and I''d be happy to pass back any  
> improvements I come up with.
>
> BTW, for those doubting the potential impact, I posted a message to  
> this list a month or so ago -- I had hacked together a quick test  
> that showed a roughly 5x performance improvement when using OCI w/  
> bind variables.

Michael

Yes i saw your earlier post on bind variables and have been hoping to  
see more posts on this topic.

There is also an oracle session option, cursor_sharing = force, which  
i tried with mixed success.  I found it slowed down some queries  
badly and so I went to this as plan C.

The database i deal with also has a table with keys of the form [A-Z] 
{3}[1-9]{7} which has caused my regex approach some difficulties.   
That''s why there''s 2 regexes - see the ref to "party
ids" in the
comments.

This is by no means a long term solution to the problem, rather just  
a stop gap.  But the sql does turn up in v$sqlarea being reexecuted  
once it is passed thru bind_sql.  It doesn''t show significant timing  
differences for single-user tests, but i expect the soft-parsing will  
have benefits when there are multiple users.

Suggestions much appreciated - it is just a working hack.   This  
version is from 13/10 so i''ll check my latest code tomorrow.  I can  
also send some samples of what it produces tomorrow from work if you  
like.


          # Added by GC.
          # Convert a plain sql, with no bind placeholders,
          # to sql plus binds in an array
          # presumes no existing bind placeholders
          # uses positional parameters
          def bind_sql(sql)
           sql_arr = Array.new
           n=0
           # replace arguments with bind placeholders
           if sql =~ /[a-z][0-9]/i then
             # allow for party ids - ignore integers
             bound_sql =
sql.gsub(/''''/,"@@").gsub(/(''[^'']*'')/)
\
             do |val|
              
sql_arr.push(val.strip.sub(/^''/,"").sub(/''$/,"").gsub(/
@+/,"''"))
               ":#{n = n + 1}"
             end
             sql_arr.unshift(bound_sql)
           else
             # deal with strings and integers
             bound_sql =
sql.gsub(/''''/,"@@").gsub(/(''[^'']*'')|((\d+))/)
\
             do |val|
              
sql_arr.push(val.strip.sub(/^''/,"").sub(/''$/,"").gsub(/
@+/,"''"))
               "#{$1}:#{n = n + 1}"
             end
             sql_arr.unshift(bound_sql)
           end
           sql_arr
         end


> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

>
> > 5) focusing on create and update will be best
> Why? Do you have representative numbers to back that up? I would have
> thought that the number of SELECTs would far, far outweigh creates and
> updates in an average application - even more so, the number of
> formulaic SELECTs. I''d love to see the :conditions clause in a
> Model.find take advantage of a prepared statement or two - the
> placeholders are in the right place, and everything...

You''r absolutely right. I was thinking from the perspective of the
project I
am working on, which is mostly creating the database right now. But
afterwards, it''s mostly reading it ;-)

The one drawback from this is that you''d need to handle the
[sql,
> [parts]] format for adapters that don''t have prepared statements
(like
> SQLite), but that''s work that''s currently being done
anyway in base.rb
> to handle :conditions, and so on.

As I said before, I plan on introducing a new connection-interface, e.g.
PreparedStatements. If present, base.rb will use the new interface, if not
fall back to the current behaviour. This way, we can safely migrate all of
the drivers one by one (or not, in the case of sqlite).

I don''t know how much time I can allocate to the problem, but I do plan
to
have a working solution by the end of next week. I keep y''all updated.


Cheers

Hagen


_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails