CODEWITHSUNDEEP
sqlsql-server
Matt Spinks
Matt Spinks

Reputation: 6698

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

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.

Upvotes: 8

Views: 4212

Answers (3)

pkuderov
pkuderov

Reputation: 3551

I propose another solution: don't do this blind optimization.

You're just trying to get lucky - it's unreasonable.

They doesn't do the same and therefor they might be optimized individually. But it's more important, that both of your queries executed for 2.5 seconds which looks strange for queries with just a simple join and simple task of getting 50 records or count all. I know it depends on the size of tables and your server's hardware, but still.

So I think you can optimize both of them and there'll be no need to "merge" them into one.

I'd look at the query execution plan first. I'm pretty sure it can be greatly speeded up. So, please, add the query plans to your question.

But even without queries I already have some questions:

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

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

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.

Upvotes: 3

santoshkumar Pithani
santoshkumar Pithani

Reputation: 121

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

Upvotes: 0

Related Questions