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

rubyonrails-talk
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.