Pagination on large data sets? – Abort count(*) after a certain time

We use the following pagination technique here:

  • get count(*) of given filter
  • get first 25 records of given filter

-> render some pagination links on the page

This works pretty well as long as count(*) is reasonable fast. In our case the data size has grown to a point where a non-indexd query (although most stuff is covered by indices) takes more than a minute. So at this point the user waits for a mostly unimportant number (total records matching filter, number of pages). The first N records are often ready pretty fast.

Therefore I have two questions:

  • can I limit the count(*) to a certain number
  • or would it be possible to limit it by time? (no count() known after 20ms)

Or just in general: are there some easy ways to avoid that problem? We would like to keep the system as untouched as possible.

Database: Oracle 10g

Update

There are several scenarios

  • a) there's an index -> neither count(*) nor the actual select should be a problem
  • b) there's no index
    • count(*) is HUGE, and it takes ages to determine it -> rownum would help
    • count(*) is zero or very low, here a time limit would help. Or I could just dont do a count(*) if the result set is already below the page limit.

Answers


You could use 'where rownum < x' to limit the number of rows to count. And if you need to show to your user that you has more register, you could use x+1 in count just to see if there is more than x registers.


Need Your Help

Precise pixel grid overlay in Core Graphics?

cocoa core-graphics nsimage cgimage

In my experiments with creating a pixel-centered image editor I've been trying to draw a precise grid overlay to help guide users when trying to access certain pixels. However, the grid I draw isn'...

Are there any unique Id for every user connects to my web server?

asp.net

I need a unique ID for every user connects to my Web server(web site).

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.