ActiveRecord slow performance on SQL Server 2000

Rails
1

As a test, I created a new database in SQL Server 2000 and then and
imported
just the “Orders” table from the NorthWind DB. Next I added an “id”
column
(identity, identity seed) to the Orders table and made it the primary
key. I
wrote the following quick app:

require “rubygems”
require_gem “activerecord”

ActiveRecord::Base.establish_connection(:adapter => “sqlserver”,
:host => “DBI:ADO:Provider=SQLOLEDB;Data Source=SQL01;Initial
Catalog=Northwind2;User Id=sa;Password=password;”,
:password=> “password”, :username=>“sa”, :database=>“Northwind2”)

class Orders < ActiveRecord::Base

end

puts “Getting ready…”

for rs in Orders.find_by_sql “SELECT * FROM Orders”
puts "Order ID: " + rs[“OrderID”].to_s()
end

also used this line: for rs in Orders.find(:all)

This takes about a minute to actually run on my system (Pentium IV with
2
gig of ram). This seemed pretty slow, so I did the same thing but with
the
DBI directly and it ran very quickly.

Am I doing something wrong here, or is the performance of ActiveRecord
in
SQL Server 2000 much slower than it is on MySQL?

Thanks,

Chad

2

This takes about a minute to actually run on my system (Pentium IV with 2
gig of ram). This seemed pretty slow, so I did the same thing but with the
DBI directly and it ran very quickly.

Are you timing just the cose of the database call, or the ActiveRecord
setup as well? In any case, ActiveRecord provides far more than just
a db query mechanism, and there’s an associated overhead whichever db
you use. I’ve been developing rails apps against SQL Server for ~6
months and haven’t had any performance problems.

Am I doing something wrong here, or is the performance of ActiveRecord in
SQL Server 2000 much slower than it is on MySQL?

It’s quite possible. There are certainly differences in the way the
two adapters work. The SQL Server adapter has suffered from a lack of
love in the past, though that is now changing. That said, running the
activerecord test suite against the two on my machine takes: ~20
seconds on SQL Server (through ODBC though, not ADO) and ~50 seconds
on MySQL.

Tom W.

3

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

On Nov 8, 2005, at 7:26 AM, CLung wrote:

This takes about a minute to actually run on my system (Pentium IV
with 2 gig of ram). This seemed pretty slow, so I did the same
thing but with the DBI directly and it ran very quickly.

Am I doing something wrong here, or is the performance of
ActiveRecord in SQL Server 2000 much slower than it is on MySQL?

Did you omit MySQL results? You may be interested in using script/
profiler to track down the bottleneck here.

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

iD8DBQFDcP9vAQHALep9HFYRAsROAJ9pioutyBWh26Xd9bXPm9A3ZXXorgCaA01+
c9Z5HXewSaDO/GCXR4HAS4E=
=tbtc
-----END PGP SIGNATURE-----