# ActiveRecord slow performance on SQL Server 2000 **URL:** \ **Category:** Rails\ **Created:** [November 12, 2005, 12:49pm UTC](https://www.ruby-forum.com/t/activerecord-slow-performance-on-sql-server-2000/47522 "2005-11-12T12:49:18Z")\ **Posts on this page:** 3\ **Page:** 1

**Author:** ![chad.lung](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/b5ac83/32.png) [@chad.lung](https://www.ruby-forum.com/u/chad.lung)\ **Post date:** [November 12, 2005, 12:49pm UTC](https://www.ruby-forum.com/t/activerecord-slow-performance-on-sql-server-2000/47522/1 "2005-11-12T12:49:18Z")

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 ---

> > 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. ---

-----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-----