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

**URL:** <https://www.ruby-forum.com/t/pagination-using-sql-server-is-slow-and-often-times-out-on-last-page/117569>\
**Category:** Rails\
**Created:** [October 18, 2007, 4:34am UTC](https://www.ruby-forum.com/t/pagination-using-sql-server-is-slow-and-often-times-out-on-last-page/117569 "2007-10-18T04:34:07Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [October 18, 2007, 4:34am UTC](https://www.ruby-forum.com/t/pagination-using-sql-server-is-slow-and-often-times-out-on-last-page/117569/1 "2007-10-18T04:34:07Z")

</div>

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/](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](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

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [October 18, 2007, 5:19am UTC](https://www.ruby-forum.com/t/pagination-using-sql-server-is-slow-and-often-times-out-on-last-page/117569/2 "2007-10-18T05:19:43Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [October 18, 2007, 7:28am UTC](https://www.ruby-forum.com/t/pagination-using-sql-server-is-slow-and-often-times-out-on-last-page/117569/3 "2007-10-18T07:28:48Z")

</div>

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/](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.