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.