Pagination using SQL Server is slow and often times out on last page

Rails
1

I am working with a legacy database that is in SQL Server. I can’t add
any new fields to the database. I think there was a field you are
supposed to add for count as an attribute. At any rate, I tried to
paginate the records in rails. The table is 20,000 records. I just try
to paginate the whole table and sort by any one of the fields. The
query runs very slow, it is faster for page 1, for the last page, page
423 or something, it usually times out. I am on SQL Server 2000.

I did some google searches, and from what this site says, I am under
the impression that there may be a
problem I am up against:
http://www.semergence.com/2007/07/31/fixing-rails-pagination-for-sql-server/

This site makes it sound like SQL Server 2005 might be better, but as
I said I am on SQL Server 2000
http://www.singingeels.com/Articles/Pagination_In_SQL_Server_2005.aspx

If there is no easy way to fix this, I guess I may have to set up an
option to just return the first N rows without paginating the table. I
wonder if there is any easy way to paginate just the first N rows and
if that might be easy ?

Also, if I paginate the table once, and I know that no new records
have been added, I wonder if there are any assumptions I can make so
that I don’t have to paginate again until I go to a page that was not
in one of the pagination links ? That’s not clear that that might be
possible, and seems lke it wouldn’t help any.

I’m not really sure what to try or investigate and I have allot of
other stuff to worry about in developing this site as well.

If I can’t paginate, maybe I could add some sort of regexp search
feature for various fields and so on.

thanks

2

Is it slow for all columns? Do those columns have indexes? If not,
or if SQL server sucks (go figure…) then it will be very slow.

–Michael

3

I figured out what I need to do I think. I looked at this site:
http://joseph.randomnetworks.com/archives/2006/05/22/sql-server-equivalent-to-mysql-and-postgresql-limit-clause/

A bunch of web sites talked about modifying the active record code in
your rails install, I don’t think I want to try that.

I tried this out. First, I do a select count(*) to figure out my
table size, that seems to run fast.
Say my page size is 100, and my table size is 20,000. I am sorting by
the ‘user’ column in my table.
The worst case scenario is I need the very last page. In order to get
that, I run something like the following query
(the end case of the last page can be a little odd as you can cut
yourself off and lose the last page or lose a row somewhere if your
numbers are slightly off):

SELECT TOP 100 *
FROM mytable
WHERE (user NOT IN
(SELECT TOP 20000 user
FROM mytable
ORDER BY user))
ORDER BY user

That ran pretty fast for me and from that, I can easily work out
whatever page I need etc.

All I have to do is code that up into a find_by_sql() call I believe.
I’ll write my own paginate() function
and paginate code, pass it my record name or whatever. I’d rather do
that than try to mess with active record core code I think.