Can I retrieve paged rows and total count within a single query?

Question

I have a basic grid with paging enabled in my web app. This grid is being populated by SQL data via a Web API using Dapper. In my API controller I am running two separate queries: one to extract the rows (which are shown in my grid), and one to obtain the total number of records (to show in my paging controls). And this works. However, I am trying to optimize my queries.

My first query, which extracts the rows, returns only 50 rows at a time (using OFFSET and FETCH, to provide paging:

SELECT DISTINCT T_INDEX.* 
FROM T_INDEX
INNER JOIN T_INDEXCALLER ON T_INDEX.IndexId = T_INDEXCALLER.IndexId
WHERE... --a fairly complex WHERE statement
ORDER BY CallTime DESC
OFFSET (@offset) ROWS FETCH NEXT 50 ROWS ONLY

My second query extracts the count of ALL rows, but uses the same tables, the same joins, and the same WHERE clause:

SELECT COUNT(DISTINCT T_INDEX.IndexId)
FROM T_INDEX
INNER JOIN T_INDEXCALLER ON T_INDEX.IndexId = T_INDEXCALLER.IndexId
WHERE... --the same fairly complex WHERE statement

As I said, this works. And it takes about 2.5 seconds per query, for a total of 5+ seconds. The time lag is not the end of the world, by any means, but I would like to cut that time in half.

I wanted to know if there is any way to retrieve the 50 rows and retrieve the total count of ALL the rows within one query. I realize that these two queries are doing two separate things. But my thinking is that there "might" be a way to tweak these two queries and combine them into one, since the tables, joins, and WHERE clause are identical between the two.

Answer 1

You can give this query a try:

SELECT *
FROM (
   SELECT *, COUNT(*) OVER () AS cnt
   FROM (
      SELECT DISTINCT T_INDEX.*, 
      FROM T_INDEX
      INNER JOIN T_INDEXCALLER ON T_INDEX.IndexId = T_INDEXCALLER.IndexId
      WHERE... --a fairly complex WHERE statement
   ) AS t1 ) AS t2
ORDER BY CallTime DESC
OFFSET (@offset) ROWS FETCH NEXT 50 ROWS ONLY

You can simplify the above query depending on what determines a distinct record in the result set.