I have identified a way to get fast paged results from the database using CTEs and the Row_Number function, as follows...
DECLARE @PageSize INT = 1
DECLARE @PageNumber INT = 2
DECLARE @Customer TABLE (
ID INT IDENTITY(1, 1),
Name VARCHAR(10),
age INT,
employed BIT)
INSERT INTO @Customer
(name,age,employed)
SELECT 'bob',21,1
UNION ALL
SELECT 'fred',33,1
UNION ALL
SELECT 'joe',29,1
UNIO开发者_开发百科N ALL
SELECT 'sam',16,1
UNION ALL
SELECT 'arthur',17,0;
WITH cteCustomers
AS ( SELECT
id,
Row_Number( ) OVER(ORDER BY Age DESC) AS Row
FROM @Customer
WHERE employed = 1
/*Imagine I've joined to loads more tables with a really complex where clause*/
)
SELECT
name,
age,
Total = ( SELECT
Count( id )
FROM cteCustomers )
FROM cteCustomers
INNER JOIN @Customer cust
/*This is where I choose the columns I want to read, it returns really fast!*/
ON cust.id = cteCustomers.id
WHERE row BETWEEN ( @PageSize * @PageNumber - 1 ) AND ( @PageSize * ( @PageNumber ) )
ORDER BY row ASC
Using this technique the returned results is really really fast even on complex joins and filters.
To perform paging I need to know the Total Rows returned by the full CTE. I have "Bodged" this by putting a column that holds it
Total = ( SELECT
Count( id )
FROM cteCustomers )
Is there a better way to return the total in a different result set without bodging it into a column? Because it's a CTE I can't seem to get it into a second result set.
Without using a temp table first, I'd use a CROSS JOIN to reduce the risk of row by row evaluation on the COUNT
To get total row, this needs to happen separately to the WHERE
WITH cteCustomers
AS ( SELECT
id,
Row_Number( ) OVER(ORDER BY Age DESC) AS Row
FROM @Customer
WHERE employed = 1
/*Imagine I've joined to loads more tables with a really complex where clause*/
)
SELECT
name,
age,
Total
FROM cteCustomers
INNER JOIN @Customer cust
/*This is where I choose the columns I want to read, it returns really fast!*/
ON cust.id = cteCustomers.id
CROSS JOIN
(SELECT Count( *) AS Total FROM cteCustomers ) foo
WHERE row BETWEEN ( @PageSize * @PageNumber - 1 ) AND ( @PageSize * ( @PageNumber ) )
ORDER BY row ASC
However, this isn't guaranteed to give accurate results as demonstrated here:
can I get count() and rows from one sql query in sql server?
Edit: after a few comments.
How to avoid a CROSS JOIN
WITH cteCustomers
AS ( SELECT
id,
Row_Number( ) OVER(ORDER BY Age DESC) AS Row,
COUNT(*) OVER () AS Total --the magic for this edit
FROM @Customer
WHERE employed = 1
/*Imagine I've joined to loads more tables with a really complex where clause*/
)
SELECT
name,
age,
Total
FROM cteCustomers
INNER JOIN @Customer cust
/*This is where I choose the columns I want to read, it returns really fast!*/
ON cust.id = cteCustomers.id
WHERE row BETWEEN ( @PageSize * @PageNumber - 1 ) AND ( @PageSize * ( @PageNumber ) )
ORDER BY row ASC
Note: YMMV for performance depending on 2005 or 2008, Service pack etc
Edit 2:
SQL Server Central shows another technique where you have reverse ROW_NUMBER. Looks useful
@Digiguru
OMG, this really is the wholy grail!
WITH cteCustomers
AS ( SELECT id,
Row_Number() OVER(ORDER BY Age DESC) AS Row,
Row_Number() OVER(ORDER BY id ASC)
+ Row_Number() OVER(ORDER BY id DESC) - 1 AS Total /*<- voodoo here*/
FROM @Customer
WHERE employed = 1
/*Imagine I've joined to loads more tables with a really complex where clause*/
)
SELECT name, age, Total
/*This is where I choose the columns I want to read, it returns really fast!*/
FROM cteCustomers
INNER JOIN @Customer cust
ON cust.id = cteCustomers.id
WHERE row BETWEEN ( @PageSize * @PageNumber - 1 ) AND ( @PageSize * ( @PageNumber ) )
ORDER BY row ASC
So obvious now.
精彩评论