Can I get the calculated rows and total in one query?

Question

I have a basic grid with paging enabled in my web application. This grid is populated with SQL data via the web API using Dapper. In my API controller, I run two separate requests: one to retrieve the rows (which are shown in my grid), and one to retrieve the total number of entries (to be displayed in my paging controls). And it works. However, I am trying to optimize my queries.

My first query, which retrieves rows, returns only 50 rows at a time (using OFFSET and FETCH to provide a search call:

 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 retrieves the number of ALL rows, but uses the same tables, the same joins, and the same WHERE :

 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 request, for a total of 5 + seconds. A lag is not the end of the world, in any way, but I would like to cut this time in half.

I wanted to know if there is a way to get 50 rows and get the total number of ALL rows in one query. I understand that these two questions do two separate things. But I think there might be a “way” to customize these two queries and combine them into one, since the table, join, and WHERE clauses are identical between them.

Answer 1

You can try this query:

 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 query above, depending on what defines a single record in the result set.

Answer 2

 SELECT DISTINCT T_INDEX.*, (SELECT COUNT(DISTINCT T_INDEX.IndexId) FROM T_INDEX INNER JOIN T_INDEXCALLER ON T_INDEX.IndexId = T_INDEXCALLER.IndexIdAS) AS TotalCount 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 

Answer 3

I propose another solution: do not do this blind optimization .

You are just trying to get happiness - this is unreasonable.

They do not do the same, and therefore they can be optimized individually. But it’s more important that both of your queries run in 2.5 seconds, which looks strange for queries with a simple connection and the simple task of getting 50 records or count all . I know this depends on the size of the tables and your server hardware, but still.

So, I think you can optimize both of them, and there will be no need to "combine" them into one.

First, I will review the query execution plan. I am pretty sure that this can be significantly accelerated. So, add query plans to your question.

But even without requests, I already have a few questions:

• why distinct ?
• Do you have an index on T_INDEX.CallTime ?