How to get total result count in paged result views?

Question

Hi, I have a DB table with approx. 100,000 entries.

My web app will show paged results of search queries, which may yield between 0 and 100,000 records.

When generating the output I want to do two things:

• Show total nr of results
• Show paged view, with 50-100 results per page.

Obviously I would like to query records for just one page at a time from DB, but here is a dilemma - how do get the COUNT() without running the entire query?

And if I have to run the entire query, isn't it better to select it all and cache in memory?

What do you usually do in such a case, if we are in the range of 100 krecords per result set?

Basically, What the most efficient way to be able to show both "found xxxxx results" message and results split into pages ?

Answer 1

You can get the count first by running a separate query, without retrieving all other table columns.

Depending on the count value, you can then decide your retrieval/paging strategy.

Quite a number of paging related posts in SO here.

Answer 2

It's expensive to show the total number of pages. The database has to finish your query up to the last row. It has to read them from disk, execute joins, evaluate where clauses and column calculations.

So it's expensive, but is it worth it? As it turns out, the total page count is a feature that nobody uses. So don't get side tracked into spending a lot of time on this. It's not worth it.

Don't show the total number of pages.

Answer 3

This is how I've achieved something along those lines before. Performance is far from great, but in the particular context it's used it doesn't matter that much (less than 70k rows even assuming worst case, most queries filter the internal derived table to the point where it's a non-issue.

I would echo the "is this worth it?" sentiment expressed in other answers.

DECLARE @StartRow INT
DECLARE @EndRow INT
SET @StartRow = 10;
SET @EndRow = 30;

SELECT M.[RowID], M.[UPRN], M.[Address_1], M.RowID+RowID2 as [TotalRows]
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY A1.UPRN ASC) as RowID,
       ROW_NUMBER() OVER (ORDER BY A1.UPRN DESC) as RowID2,
       A1.UPRN, 
       A1.Add_1
          FROM Core.Addresses A1
    ) as M
    WHERE M.RowID BETWEEN @StartRow and @EndRow