From: Nathan Day <nathan_day@...>
Date: 2008-06-06T23:18:18+09:00
Subject: Re: bind variables


On 05/06/2008, at 4:11 PM, David Masover wrote:

> On Thursday 05 June 2008 00:47:13 Nathan Day wrote:
>> One
>> concern with Ruby is the use  of bind variables, we need to use bind
>> variables for performance concerns, I am sure I am getting different
>> concepts mixed up.
>
> Probably prepared statements is what would make the difference in  
> performance.

Would there be in point in using a prepared statement if you don't  
have bind variables, every change to a variable would result in an  
complete new statement. There is also an issue with Oracle 9 and  
earlier which resulting in bad performance without bind variable,  
oracle caches compiled statements and without bind variables every  
query is treated as a new statement. We have even had extreme case  
where the database would collect so many compiled queries that it was  
clear out that the database failed. Oracle issue has a compromise fix,  
oracle 11 can use bind variables to make decisions about how to  
perform the query.

>
>> It seems the default SQL library with Ruby on Rails
>> does not support bind variables,
>
> That would be the default ORM. ActiveRecord does not support bind  
> variables at
> the SQL adapter level. It does support
>
> The individual SQL libraries, such as mysql, postgres, etc, do  
> support bind
> variables. This won't help you much if you're using Rails in its  
> entirety --
> but if you really want to, you can always swap out ActiveRecord for  
> something
> else, like DataMapper or Sequel. Of course, by then, you might  
> consider using
> another framework altogether, like Merb.
>
> Also: There have been some lively discussions about this in the Rails
> community. (ruby-talk is a Ruby discussion list, and contrary to  
> popular
> opinion, Ruby is more than just Rails.)
>
>> there does seem to be at least one
>> other SQL library for SQL Sequel, which can  perform parameterized
>> queries, is this true bind variables or are they just turning into a
>> string underneath for us, the fact that it is documented as to
>> protection against SQL injection. Seems to suggest it is true bind
>> variable.
>
> I can't speak for Sequel, but ActiveRecord does support fake bind  
> variables in
> a few places, to guard against SQL injection. Maybe someday it will  
> do actual
> bind variables, but for now, it just turns it into a string under  
> the hood.
>
>> Another feature which is not critical but would be nice, I haven't
>> been able to find information about this for Sequel, but sqlite-ruby
>> has this though is of no use to use since we need to support oracle,
>> postsql initially and more latter on.
>
> What feature is this?

Ok that was not vary clear, I was referring to query metadata.

>
>
> And by the way, Google for information about optimizing Rails on  
> Oracle.
> People have done tricks to make Oracle work reasonably well even  
> without real
> bind variables.
>