How to get the final result in the viewed results?

Question

I have a database table with approx. 100,000 entries.

My web application will display the results of paged search queries , which can contain from 0 to 100,000 entries.

When generating the output, I want to do two things:

• Show total results
• Show pagination with 50-100 results per page.

Obviously, I would like to request records for only one page at a time from the database, but here's the dilemma: how to get COUNT () without doing the whole query?

And if I need to run the entire request, is it not better to select it and cache it in memory?

What do you usually do in this case, if we are in the range of 100 quests for the result set?

Basically, What is the most effective way to show both "found xxxxx results" and the results are paginated?

Answer 1

You can get the invoice first by running a separate query without getting all the other columns in the table.

Depending on the value of the counter, you can decide your search / search strategy.

Enough posts related to paging, here.

Answer 2

It is expensive to show the total number of pages. The database should complete your query to the last line. He must read them from disk, perform unions, evaluate where clauses and column calculations are.

, ? , - , . . .

.

Answer 3

This is how I have already achieved something in this direction. Performance is far from great, but in the specific context that it used, it does not matter much (less than 70 thousand rows. Even in the worst case, most queries filter the internal view to the point where it is not a problem.

I would repeat "is it worth it?" feelings 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