Which AR Interfaces Leverage PreparedStatements/Binds?

I could not find the answer to this in a few Google searches and thought I would ask.

So when are prepared statements best leveraged in ActiveRecord's interface? I never really noticed before, but simple condition hashes or scopes do not pass down the binds so that prepared statements are leveraged. So a `Car.find(1)` would but things like `Car.where(:id => 1).first` do not. Is it a WIP to to make this happen or blessed interfaces that help?

- Ken

AFAIK there is no support for prepared statements baked in.

If you want them you need to use the SQL connection directly (and potentially become DB specific). Last time I looked, awhile ago now, there wasn’t a production ready gem to supply reasonable prepared statement support.

Simon

Simon,

I think you misunderstood my question. It has nothing to do with the SQL Server adapter or anything SQL Server related. But in the general sense of noticing from all ActiveRecord adapters that support prepared statements. I could answer my own question if I dug around in the code more, but thought I would ask first.

So why does this `Car.find(1)` leverage ActiveRecord's prepared statements but something like this `Car.where(:id => 1)` does not? The answer is most likely that the condition finders in ActiveRecord do not yet do the leg work to map hash keys to columns so that bind arguments have both the column and value needed. Which is fine, but I wanted to ask if there were other ways of side stepping the issue till that work is done, perhaps in Rails 4. For instance I tried doing something like `cars = Car.arel_table; Car.where(cars[:id].eq(1))` thinking I was giving the relation enough information to leverage prepared statements further down below in ActiveRecord, alas this still does work. So hence my question.

- Ken

P.S.

If you want them you need to use the SQL connection directly (and potentially become DB specific). Last time I looked, awhile ago now, there wasn't a production ready gem to supply reasonable prepared statement support.

Correct, but in sense of the SQL Server adapter, it becomes specific to the low level connection mode. In the case of the SQL Server adapter, we have both a RubyODBC and TinyTDS connection mode. Technically RubyODBC has an interface for preparing statements, but TinyTDS/DBLIB does not. So I leverage prepared statements using an agnostic TSQL approach. Which let's the adapter be less concerned with the low level connection mode. Opening up other connection modes for IronRuby (that failed) or hopefully something like jTDS (hopefully). Cheers!

Ahh. Yes.

I misunderstood your question.

It is the case that the #find* methods have some magic baked into them that aren’t in the rest of the query stack. They are the “highest level” so the #where and arel calls would have to reach “up” to get them… With the performance hit.

… IANA core hacker.

Hi Ken,

You're correct. Right now only code like `Car.find(1)` will create a bound parameter. I don't think we should make any high level interfaces for distinguishing between prepared and non-prepared statements. In other words, we should make it transparent, automatic, and backwards compatible.

I have plans for supporting hash based arguments (since we can determine the columns), I just haven't implemented it yet. Patches welcome. :wink: