For a search page, I need the first 10 results and the total number of results of a query.

That part is easy:

SELECT TOP(10) ... FROM ... WHERE ... ORDER BY ...

But it's also amazingly slow. Both statements take about the same time.

So I found this one:

SELECT TOP(10) ... FROM ... WHERE ... ORDER BY ...

That cuts down the time by ~50%. Great. But unfortunately the rowcount returned is 10. If I remove the "TOP(10)" the rowcount is correct, but the timespan is more or less the same as in the original version. (Even though I only access the first 10 rows.)

That's frustrating, because the total-number-of-lines info must exist, even in the TOP(10) version. (At least I think so, because we use a very complex ORDER BY clause, with 3 "CASE" conditions to sort empty data to the bottom)

Is there an efficient way to get both? TOP(10) data and total number?

  • WITH cte AS (SELECT ...) SELECT TOP(10) *, COUNT(*) OVER () total_count FROM cte. Then extract the count from any record and use another fields as a datasource. example fiddle
    – Akina
    Jul 24 '19 at 12:55
  • 1
    "the total-number-of-lines info must exist" -- wrong. The total number of rows is only known when the last row is retrieved. This is why @@ROWCOUNT is 10 after the query with TOP(10) -- only 10 rows are returned by the query, nobody knows how many more matching rows there might be.
    – mustaccio
    Jul 24 '19 at 13:08
  • If you have permissions to query sys.partitions, you can get the row count for your table by: select rows from sys.partitions where object_id = object_id(N'MyTable') and index_id = 1 (for clustered index) or 0 (for heap).
    – Queue Mann
    Jul 24 '19 at 13:10
  • @QueueMann since it can only be either 0 or 1, you can say index_id IN (0,1). But you need to use SUM(rows) to account for partitioned tables. Jul 24 '19 at 13:15

If your ORDER BY and WHERE clauses are complicated, then proposed solution should cut time in half:
1. Create a new table for intermittent results (it can be a temp table, table variable or memory optimized table with session id marker and schema_only setting).
2. Insert all rows from source query into this table, including order by expressions as additional columns.
3. Run your count and top 10 query from this new table.

  • Thanks, you're right, this greatly improves the performance.
    – leuk98743
    Jul 25 '19 at 9:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.