Fixing Rails Pagination for SQL Server

MS SQL Server certainly feels like the red headed step child of the Rails connection adapters. The core developers aren't interested in it, and I'd have to guess that most Rails developers deploy to MySQL or PostgreSQL.

A good example of why the SQL Server support needs more love from the Rails community. The pagination code in the connection adapter is horribly ugly. SQL Server 2000 doesn't support a limit or offset, which makes pagination extremely difficult. For kicks, check out the SQL that the Rails connection adapter generates for a limit and offset query in SQL Server. There's enough sub queries and reverse sorts to make your head spin. Not to mention the awful performance killing select count(*) before every query.

SQL Server 2005 makes our life a little easier in that it added row_number() support. With this, it's possible, however not straight forward, to perform pagination that doesn't make you want to puke so much. Unfortunately, Rails hasn't yet split their SQL Server adapters into a SQL Server 2000 and SQL Server 2005 adapters. I strongly encourage this move, as there are many differences between the two.

If you are running SQL Server 2005, and you want to fix many pagination problems that plague the sqlserver_adapter.rb (just look inside the Rails Trac sometime, there's a lot), have I got the monkey patch for you. We've been using this for a little while now, and it seems to do the trick. YMMV but it should hopefully give you an idea of what's possible.



module ActiveRecord
  module ConnectionAdapters
    class SQLServerAdapter

      def add_limit_offset!(sql, options)
        if options[:limit] and options[:offset]
          options[:order] ||= sql.match('FROM (.*) ')[1] + '.id'
          sql.sub!(/ORDER BY.*$/i, '')
          sql.sub!(/SELECT/i,
                  "SELECT row_number() over( order by #{options[:order]} ) as row_num, \n")
          sql.replace("select top #{options[:limit]} * from (#{sql}) as tmp_table1 \n" +
                "where row_num > #{options[:offset]}")
        end
      end
    end
  end
end



Not only was the built in pagination queries terribly slow (because it always executes a select count(*) before the query itself), but it had problems when doing paginations with included models. This is something that ActiveScaffold does all the time, so if you are using that and SQL Server, you've no doubt felt the pain when you tried to sort a column.

Popular posts from this blog

Lists and arrays in Dart

(This is Part 3 in a series about Dart. Check out Part 2, Function in Dart .) Warning : We expect the Dart libraries to undergo potentially sweeping changes before Dart goes to alpha. This document is relevant as of 2011-12-22. Intro Dart is a "batteries included" effort to help app developers build modern web apps. An important "battery" is the bundled core Dart libraries, providing common and rich functionality. Dart is building a solution for large, complex web apps, and providing well tested, integrated, and common libraries is key to helping a web app developer be more productive out of the box. The Collection libraries are a crucial set of APIs that Dart developers get for free. Much more than simple arrays and maps, the Collection library includes standard ways to filter, iterate, inspect, compose, and sort your data. This post specifically looks at List<E> , Dart's ordered, indexable collection of objects. Aside: The Dart project is lucky
Read more

Converting Array to List in Scala

Now, this has to have a built-in somewhere in Scala , because it just seems too common. So, how to convert an Array to a List in Scala? Why do I need this? I needed to drop to Java for some functionality, which in this case returns an Array. I wanted to get that Array into a List to practice my functional programming skillz. **Update**: I figured out how to convert Arrays to Lists the Scala way. Turns out it's a piece of cake. val myList = List.fromArray(Array("one", "two", "three")) or val myList = Array("one","two","three").elements.toList The call to elements returns an Iterator , and from there you can convert to a List via toList . Nice. Because my first version wasn't actually tail recursive, what follows is a true tail recursive solution, if I were to implement this by hand. The above, built in mechanism is much better, though. object ArrayUtil { def toList[a](array: Array[a]): List[a] = { d
Read more

Null-aware operators in Dart

Image
Three new language features just landed in the latest dev channel build of the Dart ! Collectively known as null-aware operators , these new features will help you reduce the code required to work with potentially null objects. I'm excited for these new abilities, because typing less is always a good thing. Read on to learn more, and be sure to try these new features on Dart Pad . ?? Use ?? when you want to evaluate and return an expression IFF another expression resolves to null. exp ?? otherExp is similar to ((x) => x == null ? otherExp : x)(exp) ??= Use ??= when you want to assign a value to an object IFF that object is null. Otherwise, return the object. obj ??= value is similar to ((x) => x == null ? obj = value : x)(obj) ?. Use ?. when you want to call a method/getter on an object IFF that object is not null (otherwise, return null). obj?.method() is similar to ((x) => x == null ? null : x.method())(obj) Yo
Read more