# SQL Server, is ODBC driver better than ADO?

**URL:** <https://www.ruby-forum.com/t/sql-server-is-odbc-driver-better-than-ado/119083>\
**Category:** Rails\
**Created:** [November 2, 2007, 10:48pm UTC](https://www.ruby-forum.com/t/sql-server-is-odbc-driver-better-than-ado/119083 "2007-11-02T22:48:46Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [November 2, 2007, 10:48pm UTC](https://www.ruby-forum.com/t/sql-server-is-odbc-driver-better-than-ado/119083/1 "2007-11-02T22:48:46Z")

</div>

I am using SQL Server on a legacy database. I had to write my own  
pagination using find\_by\_sql.  
Then I had to switch from ADO to ODBC because when I tried to update  
records I was getting weird  
transaction errors from time to time. I did a google search and  
someone who had had this same error was told to try using the ODBC  
driver. I did that and I have never had the problem since. My database  
pagination seems kind of slow and I am not sure if it was faster under  
ADO. Any thoughts on comparisons of these two drivers for SQL Server ?  
I think I might be on SQL Server 2003

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [February 12, 2008, 7:14am UTC](https://www.ruby-forum.com/t/sql-server-is-odbc-driver-better-than-ado/119083/2 "2008-02-12T07:14:18Z")

</div>

The paging code used by Sqlserver.rb (and hence, ado.rb), is  
inefficient. I think the ODBC-based version is written closer to the  
ODBC library than the ADO.rb code is. The ADO.rb code does not do much  
at all with ADO except set up a connection object and recordset  
object, and the sqlserver.rb part of it sends SQL commands to it,  
rather than set up its own recordset object and page through it using  
recordset methods. If ADO.rb + sqlserver.rb were closer to ADO, then  
perhaps it’d be more efficient in this aspect…

select top :page\_size \* from (select top :page\_size \* :page\_num from  
:table order by 1 ASC) order by 1 desc

It does this because…well, that’s just how SQL Server works \<= 2000.  
I think they added paging attributes to TOP in 2005, but I don’t know  
for sure off the top of my head.

I do hope to some day have plenty of time to try and write a  
sqlserver.rb that doesn’t rely on the DBI.rb code but only on invoking  
ADODB objects directly, but this’ll work only on Win32 boxes, of  
course… and all the issues w.r.t. server-side vs client-side  
cursors, ADO’s implicit/black-box connection pooling, etc.

[Ruby.Net](http://Ruby.Net) will let you use the .Net framework instead, but again, this  
would leave you in a Win32-only solution unless you were using the  
Mono framework instead…