paginacion - Consulta de SQL Server con paginación y conteo

sql server paginar consulta

Question

Quiero hacer una consulta de base de datos con paginación. Por lo tanto, utilicé una expresión de tabla común y una función clasificada para lograr esto. Mira el ejemplo de abajo.

declare @table table (name varchar(30));
insert into @table values (''Jeanna Hackman'');
insert into @table values (''Han Fackler'');
insert into @table values (''Tiera Wetherbee'');
insert into @table values (''Hilario Mccray'');
insert into @table values (''Mariela Edinger'');
insert into @table values (''Darla Tremble'');
insert into @table values (''Mammie Cicero'');
insert into @table values (''Raisa Harbour'');
insert into @table values (''Nicholas Blass'');
insert into @table values (''Heather Hayashi'');

declare @pagenumber int = 2;
declare @pagesize int = 3;
declare @total int;

with query as
(
    select name, ROW_NUMBER() OVER(ORDER BY name ASC) as line from @table
)
select top (@pagesize) name from query
    where line > (@pagenumber - 1) * @pagesize

Aquí, puedo especificar las variables @pagesize y @pagenumber para darme solo los registros que quiero. Sin embargo, este ejemplo (que proviene de un procedimiento almacenado) se utiliza para realizar una paginación de cuadrícula en una aplicación web. Esta aplicación web requiere mostrar los números de página. Por ejemplo, si tengo 12 registros en la base de datos y el tamaño de la página es 3, tendré que mostrar 4 enlaces, cada uno representando una página.

Pero no puedo hacer esto sin saber cuántos registros hay, y este ejemplo solo me da el subconjunto de registros.

Luego cambié el procedimiento almacenado para devolver el recuento (*).

declare @pagenumber int = 2;
declare @pagesize int = 3;
declare @total int;
with query as
(
    select name, ROW_NUMBER() OVER(ORDER BY name ASC) as line, total = count(*) over()from @table
)
select top (@pagesize) name, total from query
    where line > (@pagenumber - 1) * @pagesize

Entonces, junto con cada línea, mostrará el número total de registros. Pero no me gustó.

Mi pregunta es si hay una mejor forma (rendimiento) de hacerlo, tal vez configurando la variable @total sin devolver esta información en SELECT. ¿O es esta columna total algo que no dañará demasiado el rendimiento?

Gracias

Answer 1

¿Qué pasa si calculas el recuento de antemano?

declare @pagenumber int = 2;
declare @pagesize int = 3;
declare @total int;

SELECT @total = count(*)
FROM @table

with query as
(
   select name, ROW_NUMBER() OVER(ORDER BY name ASC) as line from @table
)
select top (@pagesize) name, @total total from query
where line > (@pagenumber - 1) * @pagesize

Otra forma, es calcular el max(line) . Revisa el enlace

Devuelva los registros totales de SQL Server cuando use ROW_NUMBER

UPD:

Para consultas individuales, verifique la respuesta de marc_s en el enlace de arriba.

    with query as
    (
       select name, ROW_NUMBER() OVER(ORDER BY name ASC) as line from @table
    )
    select top (@pagesize) name, 
       (SELECT MAX(line) FROM query) AS total 
    from query
    where line > (@pagenumber - 1) * @pagesize

Answer 2

Hay muchas maneras en que podemos lograr la paginación: espero que esta información sea útil para usted y para otros.

Ejemplo 1: usando la siguiente cláusula offset-fetch. introducir en 2005

declare @table table (name varchar(30));
insert into @table values (''Jeanna Hackman'');
insert into @table values (''Han Fackler'');
insert into @table values (''Tiera Wetherbee'');
insert into @table values (''Hilario Mccray'');
insert into @table values (''Mariela Edinger'');
insert into @table values (''Darla Tremble'');
insert into @table values (''Mammie Cicero'');
insert into @table values (''Raisa Harbour'');
insert into @table values (''Nicholas Blass'');
insert into @table values (''Heather Hayashi'');

declare @pagenumber int = 1
declare @pagesize int = 3

--this is a CTE( common table expression and this is introduce in 2005)
with query as
(
  select ROW_NUMBER() OVER(ORDER BY name ASC) as line, name from @table
) 

--order by clause is required to use offset-fetch
select * from query
order by name 
offset ((@pagenumber - 1) * @pagesize) rows
fetch next @pagesize rows only

Ejemplo 2: usando la función row_number () y entre

declare @table table (name varchar(30));
insert into @table values (''Jeanna Hackman'');
insert into @table values (''Han Fackler'');
insert into @table values (''Tiera Wetherbee'');
insert into @table values (''Hilario Mccray'');
insert into @table values (''Mariela Edinger'');
insert into @table values (''Darla Tremble'');
insert into @table values (''Mammie Cicero'');
insert into @table values (''Raisa Harbour'');
insert into @table values (''Nicholas Blass'');
insert into @table values (''Heather Hayashi'');

declare @pagenumber int = 2
declare @pagesize int = 3

SELECT *
FROM 
(select ROW_NUMBER() OVER (ORDER BY PRODUCTNAME) AS RowNum, * from Products)
as Prodcut
where RowNum between (((@pagenumber - 1) * @pageSize )+ 1) 
and (@pagenumber * @pageSize )

Espero que estos sean de utilidad para todos.

Answer 3

Suponiendo que está utilizando MSSQL 2012, puede usar Offset and Fetch que limpia mucho la paginación del lado del servidor. Hemos encontrado que el rendimiento está bien, y en la mayoría de los casos es mejor. En cuanto a obtener el recuento total de columnas, solo use la función de ventana debajo de en línea ... no incluirá los límites impuestos por ''desplazamiento'' y ''búsqueda''.

Para Row_Number, puede usar las funciones de la ventana como lo hizo, pero le recomendaría que calcule ese lado del cliente como (pagenumber * tamaño de página + resultadosetRowNumber), así que si está en la 5ta página de 10 resultados y en la tercera fila saldría la fila 53.

Cuando se aplica a una tabla de pedidos con aproximadamente 2 millones de pedidos, encontré lo siguiente:

VERSIÓN RÁPIDA

Esto corrió en menos de un segundo. Lo bueno de esto es que puede hacer su filtrado en la expresión de tabla común una vez y se aplica tanto al proceso de paginación como al recuento. Cuando tiene muchos predicados en la cláusula where, esto mantiene las cosas simples.

declare @skipRows int = 25,
        @takeRows int = 100,
        @count int = 0

;WITH Orders_cte AS (
    SELECT OrderID
    FROM dbo.Orders
)

SELECT 
    OrderID,
    tCountOrders.CountOrders AS TotalRows
FROM Orders_cte
    CROSS JOIN (SELECT Count(*) AS CountOrders FROM Orders_cte) AS tCountOrders
ORDER BY OrderID
OFFSET @skipRows ROWS
FETCH NEXT @takeRows ROWS ONLY;

Versión lenta

Esto tomó aproximadamente 10 segundos, y fue el Conde (*) lo que causó la lentitud. Me sorprende que esto sea tan lento, pero sospecho que es simplemente calcular el total para cada fila. Aunque está muy limpio.

declare @skipRows int = 25,
@takeRows int = 100,
@count int = 0


SELECT 
    OrderID,
    Count(*) Over() AS TotalRows
FROM Location.Orders
ORDER BY OrderID
OFFSET @skipRows ROWS
FETCH NEXT @takeRows ROWS ONLY;

CONCLUSIÓN

Ya pasamos por este proceso de ajuste de rendimiento y en realidad encontramos que dependía de la consulta, los predicados utilizados y los índices involucrados. Por ejemplo, el segundo introdujimos una vista que generó, por lo que en realidad consultamos fuera de la tabla base y luego unimos la vista (que incluye la tabla base) y en realidad funciona muy bien.

Yo sugeriría tener un par de estrategias sencillas y aplicarlas a consultas de alto valor que están funcionando.

Answer 4

@pagenumber=5
@pagesize=5

Crea una expresión de tabla común y escribe lógica como esta

Between ((@pagenumber-1)*(@pagesize))+1 and (@pagenumber *@pagesize)

Answer 5

DECLARE @pageNumber INT = 1  , 
        @RowsPerPage INT = 20

SELECT  *
FROM    TableName
ORDER BY Id
        OFFSET ( ( @pageNumber - 1 ) * @RowsPerPage ) ROWS
             FETCH NEXT @RowsPerPage ROWS ONLY;