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.


i posted this earlier for a MySQL related question but the techniques performed here should work as well if not better in Oracle :)

php pagination with break or continue


Need Your Help

Empirical analysis for binary search not matching Theoretical Analysis

java computation-theory

I'm currently doing a test for the Binary searches average case. Simply all I do is I generate a random variable and then search for this random variable in different sized arrays using the binary

Validate 0.5 as Step Value in HTML5 Number Input

javascript jquery html html5

I want to have an input field which only allows to type whole numbers or .5 decimal numbers. e.g. 0, 0.5, 1, 1.5, 2, 2.5.......etc etc