wbsurfver-/E1597aS9LQAvxtiuMwx3w@public.gmane.org
2007-Nov-02 21:47 UTC
SQL Server, is ODBC driver better than ADO ?
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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 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... On 11/2/07, wbsurfver-/E1597aS9LQAvxtiuMwx3w@public.gmane.org <wbsurfver-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > > 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 > > > > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---