Paging with Correct TOTAL Count

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Paging with Correct TOTAL Count

Author

Topic

vonbv
Starting Member

4 Posts

Posted - 2010-11-04 : 03:32:45

Hello,

I have a stored procedure that implements paging. My issue is on how to return the total count in a very efficient way. Here is what my code looks like:

	-- Query A: Query to get the Actual TOTAL rows being filtered	
	SELECT @TOTAL_ROWS=COUNT(*)			
	FROM
		a_view			
	WHERE 		
		some_filter;

	-- Query B: CTE to get only partial rows based on current page number
	WITH qry1 AS (
		SELECT 
			ROW_NUMBER () OVER (ORDER BY fld1) as RowNum, fld1, fld2
		FROM
			a_view
		WHERE 		
			some_filter
	)	
	
	-- Query C: 
	SELECT * FROM qry1	 
	WHERE RowNum BETWEEN @START_ROW_INDEX AND (@START_ROW_INDEX + @MAX_ROWS) - 1

You will notice that Queries A & B are the same. It is because Query A returns the total count based on the filter. I need this so that the consuming application can determine how many pages exists on the result set.

I am thinking the Query A can be replaced by something to make the stored proc run in a more efficient way.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-11-04 : 03:51:09

Why not add COUNT(*) OVER () in the cte?

N 56°04'39.26"
E 12°55'05.63"

vonbv
Starting Member

4 Posts

Posted - 2010-11-04 : 04:26:43

That would mean I have to group by all of the columns. I am returning over ten columns with my query. Maybe I am missing your point.

Sachin.Nand

2937 Posts

Posted - 2010-11-04 : 05:08:16

[code]
WITH qry1 AS (
SELECT
ROW_NUMBER () OVER (ORDER BY fld1) as RowNum, fld1, fld2,
COUNT(*) OVER ()TOTAL_ROWS
FROM
a_view
WHERE
some_filter
)
[/code]

No need for the group by clause.The above statement will work

PBUH

vonbv
Starting Member

4 Posts

Posted - 2010-11-04 : 05:51:16

That's what I need. Thanks!

By the way, I went through the MSDN and I was not able to find such technique. Can you point me to a site that shows technique like that so I can study them? What I meant by technique is that I was not aware COUNT can be use with OVER to achieve such result.

Thanks much!

vonbv
Starting Member

4 Posts

Posted - 2010-11-05 : 01:03:22

Hi Guys,

The COUNT(*) OVER was taking longer to run. I did a test, I created a table with an INT and an NVARCHAR columns. I inserted 1 million rows and run the following query:

--Query 1
declare 	@START_ROW_INDEX int, @MAX_ROWS int, @TR int
select 	@START_ROW_INDEX = 0,	@MAX_ROWS = 50;

WITH PQRY AS (	
select ROW_NUMBER () OVER (ORDER BY personid) as RowNum
	, count(*) over() TotalRows
	, [name]
from person
)
	
SELECT * FROM PQRY	 	
WHERE RowNum BETWEEN @START_ROW_INDEX AND (@START_ROW_INDEX + @MAX_ROWS) - 1
GO
--Query 2
declare 	@START_ROW_INDEX int, @MAX_ROWS int, @TR int
select 	@START_ROW_INDEX = 0,	@MAX_ROWS = 50;

select @TR=count(*) from person;
WITH PQRY AS (	
select ROW_NUMBER () OVER (ORDER BY personid) as RowNum, [name]
from person
)
	
SELECT * FROM PQRY	 	
WHERE RowNum BETWEEN @START_ROW_INDEX AND (@START_ROW_INDEX + @MAX_ROWS) - 1
GO

Query 1 runs an average of 3 seconds and Query 2 runs in less than a second.

I just thought I share that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources