From: Sam Smoot <ssmoot@...>
Date: 2006-10-21T04:00:14+09:00
Subject: Re: MsSqlClient ActiveRecord Adapter initial gem-release!

Dr Nic wrote:
> How bad was the default AR SQLServer adapter? How much better is this
> one? Will it provide any additional interfaces to the DB that the
> default AR API provides?
>
> Nic
>
> --
> Posted via http://www.ruby-forum.com/.

I want to make it clear I have nothing but respect for the
maintainer(s) of the sqlserver adapter, WIN32OLE, RubyDBI, etc and
their abilities. My friend Scott (who wrote most of the initial version
of mssqlclient until feature parity) and I just saw an opportunity to
contribute.

Performance of the sqlserver adapter is fine (for Rails) to me. I just
figured people love performance improvements. If  you're doing some
sort of processing with AR this might raise the bar for AR a bit higher
though allowing it to operate in situations you might otherwise have
required batch inserts for.

The issue with the original adapter for my use, is that you can't
really use it effectively for internationalized applications since
WIN32OLE (what the DBI/ADO driver uses) doesn't support multi-byte
encodings.

Another issue was that all the Rails adapters use a single connection
that stays open for the lifetime of the application. Which is just a
really bad choice IMO and results in "zombie state" errors eventually.
I wrote a fix for that that basically just drops the connection and
reconnects. It's a convention imposed by Rails though, and it's a
little hard to work around so I understand why the sqlserver adapter
doesn't try to. Fortunately, we've managed to get around it and provide
an adapter that only has an open connection if executing a query or
currently in a transaction (at least that's the intent).

I also have an acts_as_partitioned plugin for managing partitioned
tables transparently, and a bulk_copy mixin that's kind of related,
allowing you to do things like this:

  # batches are .NET DataTables, that aren't sent to the
  # database until "commit!" is called.
  Customer::batch do |batch|
    batch.save Customer.new(:name => 'bob', :age => rand())
  end

  # batch.commit! is called on block exit automatically
  # unless batch.abort! or batch.rollback! was called.

Eventually I'd like to look into integrating that as well (maybe in
it's own project?).

So, I think with a little hard work we can hack together a solution
that makes MS SQL Server not only an acceptable deployment option for
Rails/Ruby, but one with a number of unique performance and programming
advantages. At least that's my hope.

Now if someone wants to pay me to work on this full-time (hint,
hint)... ;-)