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.
August 1st, 2007 at 4:20 am
An alternative track: how about investigating the ODBC Adapter for Rails instead?
September 5th, 2007 at 7:14 am
Where exactly do i implement his patch?
October 12th, 2007 at 6:04 am
Tried it against ActiveScaffold and it breaks on some JOINS
Also tried
“WITH MyTmp AS
(SELECT ROW_NUMBER() OVER( order by #{options[:order]} ) as row_num , * FROM (#{sql})
SELECT * FROM MyTmp where row-num between #{options[:offset]} AND ( #{options[:offset]} + #{options[:limit]} - 1)”
But the OLE connector couldn’t understand - probably needs a native 2005 connector
Cool idea though -
October 17th, 2007 at 7:57 am
Thanks for this! I’ve been banging my head against the wall, trying to get some intelligent support for :offset on SQL Server.
I’m not sure what the deal is with the “n”s at the end of lines 10 and 11 (inside the strings). I assumed they were typos, and it seems to work for me without them (but not with them).
Also, your monkey-patch broke two things (that I could find): the ability to use :join or :include, and the ability to use just :limit (without :offset).
I *think* that I fixed both of those bugs here: http://pastie.caboo.se/108142
module ActiveRecord
module ConnectionAdapters
class SQLServerAdapter
def add_limit_offset!(sql, options)
if options[:limit] and options[:offset]
options[:order] ||= sql.match(’FROM (\w*) ‘)[1] + ‘.id’
sql.sub!(/ORDER BY.*$/i, ”)
sql.sub!(/SELECT/i,
“SELECT row_number() over( order by #{options[:order]} ) as row_num, “)
sql.replace(”select top #{options[:limit]} * from (#{sql}) as tmp_table1 ” +
“where row_num > #{options[:offset]}”)
elsif sql !~ /^\s*SELECT (@@|COUNT\()/i
sql.sub!(/^\s*SELECT(\s+DISTINCT)?/i) do
“SELECT#{$1} TOP #{options[:limit]}”
end unless options[:limit].nil?
end
end
end
end
end
December 28th, 2007 at 2:37 pm
Bless you! I was going crazy with this function adding extra back-slashes to the query, among other things. I did have to make a couple changes to the snippet you posted - there seemed to be a couple extra “n”s in there:
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, “)
sql.replace(”SELECT TOP #{options[:limit]} * FROM (#{sql}) as tmp1 ” +
“WHERE row_num > #{options[:offset]}”)
end
end
September 26th, 2008 at 8:32 am
Hi everyone,
I posted an updated version of the patch for SQL Server 2005 at my blog
http://alexle.net/archives/292
Cheers!
Alex