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
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.
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 :)