Get total row count while paging

Question

I have a search screen where the user has 5 filters to search on.
I constructed a dynamic query, based on these filter values, and page 10 results at a time.
This is working fine in SQL2012 using OFFSET and FETCH, but I'm using two queries to do this.

I want to show the 10 results and display the total number of rows found by the query (let's say 1000).
Currently I do this by running the query twice - once for the Total count, then again to page the 10 rows.
Is there a more efficient way to do this?

Induline

Answer 1

You don't have to run the query twice.

SELECT ..., total_count = COUNT(*) OVER()
FROM ...
ORDER BY ...
OFFSET 120 ROWS
FETCH NEXT 10 ROWS ONLY;

Based on the chat, it seems your problem is a little more complex - you are applying DISTINCT to the result in addition to paging. This can make it complex to determine exactly what the COUNT() should look like and where it should go. Here is one way (I just want to demonstrate this rather than try to incorporate the technique into your much more complex query from chat):

USE tempdb;
GO
CREATE TABLE dbo.PagingSample(id INT,name SYSNAME);

-- insert 20 rows, 10 x 2 duplicates
INSERT dbo.PagingSample SELECT TOP (10) [object_id], name FROM sys.all_columns;
INSERT dbo.PagingSample SELECT TOP (10) [object_id], name FROM sys.all_columns;

SELECT COUNT(*) FROM dbo.PagingSample; -- 20

SELECT COUNT(*) FROM (SELECT DISTINCT id, name FROM dbo.PagingSample) AS x; -- 10

SELECT DISTINCT id, name FROM dbo.PagingSample; -- 10 rows

SELECT DISTINCT id, name, COUNT(*) OVER() -- 20 (DISTINCT is not computed yet)
 FROM dbo.PagingSample
 ORDER BY id, name
 OFFSET (0) ROWS FETCH NEXT (5) ROWS ONLY; -- 5 rows

-- this returns 5 rows but shows the pre- and post-distinct counts:
SELECT PostDistinctCount = COUNT(*) OVER() -- 10, 
  PreDistinctCount -- 20, 
  id, name 
FROM 
(
  SELECT DISTINCT id, name, PreDistinctCount = COUNT(*) OVER() 
    FROM dbo.PagingSample
    -- INNER JOIN ...
) AS x
ORDER BY id, name
OFFSET (0) ROWS FETCH NEXT (5) ROWS ONLY;

Clean up:

DROP TABLE dbo.PagingSample;
GO

Levitt

This works just that in the dynamic query if I give the variable name as @oTotalRecords = COUNT(1) OVER(), it is saying that "Must declare the scalar variable "@oTotalRecords". Can you please tell me how can i fix it. – Induline

I am reading the value into a variable and reading from that in application. – Induline

Can I get it into a variable from sql itself?? – Induline

Is there any other way that I can do it? – Induline

let us continue this discussion in chat – Induline

Hi Aaron, can we continue this discussion in chat please – Induline

Hi Aaron,how can I add the distinct in the over() clause – Induline

Answer 2

My solution is similar to "rs. answer"

DECLARE @PageNumber AS INT, @RowspPage AS INT
SET @PageNumber = 2
SET @RowspPage = 5 

SELECT   COUNT(*) OVER() totalrow_count,*
    FROM databasename
    where columnname like '%abc%'
    ORDER BY columnname
    OFFSET ((@PageNumber - 1) * @RowspPage) ROWS
    FETCH NEXT @RowspPage ROWS ONLY;

The return result will include totalrow_count as the first column name

Oared

Answer 3

Can you try something like this

SELECT TOP 10 * FROM 
(
   SELECT COUNT(*) OVER() TOTALCNT, T.*
   FROM TABLE1 T
   WHERE col1 = 'somefilter'
) v

or

SELECT * FROM 
(
   SELECT COUNT(*) OVER() TOTALCNT, T.*
   FROM TABLE1 T
   WHERE col1 = 'somefilter'
) v
ORDER BY COL1
OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY

Now you have total count in your totalcnt column and you can use this column to show total number of rows

Hortense

Problem is there is a distinct in there, so it complicates the logic a bit. At the same level, COUNT() OVER() will be calculated before the DISTINCT. – Levitt

Answer 4

In my testing with a complex join and ~6,000 records returned, it's much faster to do two separate queries. Faster, as in milliseconds total to get the total and separately bring back a subset of 100 records, vs 17 seconds to do the combined query. Anyone else see this kind of performance hit? Obviously, it could have something to do with the data structure but this is still a huge difference.

Impermissible

Answer 5

I hope I'm not too late to jump in on this question, but I ran across a very similar problem tonight. I had a paging class that was over inflating the number of results returned because the previous developer was dropping the DISTINCT and just doing a SELECT count(*) of the table joins. While this doesn't solve the 2 query problem I ended up using a nested query so that it looked like this:

Original Query

SELECT DISTINCT
  field1, field2
FROM
  table1 t1
  left join table2 t2 on t2.id = t1.id

Over Inflated Results Query

SELECT
  count(*)
FROM
  table1 t1
  left join table2 t2 on t2.id = t1.id

My Results Query Solution

SELECT
  count(*)
FROM
  (SELECT DISTINCT
     field1, field2
   FROM
     table1 t1
     left join table2 t2 on t2.id = t1.id) as tbl;

Copyholder