.net - una - procedimientos almacenados sql server desde java

¿Cómo devolver una página de resultados de SQL?

Question

Muchas aplicaciones tienen grillas que muestran datos de una tabla de base de datos de una página a la vez. Muchos de ellos también le permiten al usuario elegir el número de registros por página, ordenarlos por cualquier columna y navegar de un lado a otro a través de los resultados.

¿Qué es un buen algoritmo para implementar este patrón sin llevar toda la tabla al cliente y luego filtrar los datos en el cliente? ¿Cómo traes solo los registros que quieres mostrar al usuario?

¿LINQ simplifica la solución?

Answer 1

Recomiendo usar LINQ o intentar copiar lo que hace. Tengo una aplicación donde uso los métodos LINQ Take y Skip para recuperar datos paginados. El código se ve así:

MyDataContext db = new MyDataContext();
var results = db.Products
.Skip((pageNumber - 1) * pageSize)
.Take(pageSize);

La ejecución de SQL Server Profiler revela que LINQ está convirtiendo esta consulta en SQL similar a:

SELECT [ProductId], [Name], [Cost], and so on...
FROM (
SELECT [ProductId], [Name], [Cost], [ROW_NUMBER]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [Name]) AS [ROW_NUMBER],
[ProductId], [Name], [Cost]
FROM [Products]
)
WHERE [ROW_NUMBER] BETWEEN 10 AND 20
)
ORDER BY [ROW_NUMBER]

En inglés simple:
1. Filtre sus filas y use la función ROW_NUMBER para agregar números de fila en el orden que desee.
2. Filtre (1) para devolver solo los números de fila que desee en su página.
3. Ordene (2) por el número de fila, que es el mismo que el orden que quería (en este caso, por Nombre).

Answer 2

Solución de Oracle:

select * from (
select a.*, rownum rnum from (
YOUR_QUERY_GOES_HERE -- including the order by
) a
where rownum <= MAX_ROW
) where rnum >= MIN_ROW

Answer 3

Hay algunas soluciones que uso con MS SQL 2005.

Uno de ellos es ROW_NUMBER (). Pero, personalmente, no me gusta ROW_NUMBER () porque no funciona para obtener grandes resultados (DB en el que trabajo es realmente grande - más de 1TB de datos ejecutando miles de consultas en segundo lugar - ya sabes - grandes redes sociales sitio).

Aquí está mi solución favorita.

Usaré un tipo de pseudo código de T-SQL.

Busquemos la segunda página de usuarios ordenada por nombre, apellido, donde cada página tiene 10 registros.

@page = 2 -- input parameter
@size = 10 -- can be optional input parameter
if @page < 1 then begin
@page = 1 -- check page number
end
@start = (@page-1) * @size + 1 -- @page starts at record no @start
-- find the beginning of page @page
SELECT TOP (@start)
@forename = forename,
@surname = surname
@id = id
FROM
users
ORDER BY
forename,
surname,
id -- to keep correct order in case of have two John Smith.
-- select @size records starting from @start
SELECT TOP (@size)
id,
forename,
surname
FROM
users
WHERE
(forename = @forename and surname = @surname and id >= @id) -- the same name and surname, but bigger id
OR (forename = @forename and surname > @surname) -- the same name, but bigger surname, id doesn''t matter
OR (forename > @forename) -- bigger forename, the rest doesn''t matter
ORDER BY
forename,
surname,
id

Answer 4

LINQ combinado con expresiones lambda y clases anónimas en .Net 3.5 simplifica enormemente este tipo de cosas.

Consultando la base de datos:

var customers = from c in db.customers
join p in db.purchases on c.CustomerID equals p.CustomerID
where p.purchases > 5
select c;

Número de registros por página:

customers = customers.Skip(pageNum * pageSize).Take(pageSize);

Ordenando por cualquier columna:

customers = customers.OrderBy(c => c.LastName);

Obtener solo los campos seleccionados del servidor:

var customers = from c in db.customers
join p in db.purchases on c.CustomerID equals p.CustomerID
where p.purchases > 5
select new
{
CustomerID = c.CustomerID,
FirstName = c.FirstName,
LastName = c.LastName
};

Esto crea una clase anónima de tipo estático en la que puede acceder a sus propiedades:

var firstCustomer = customer.First();
int id = firstCustomer.CustomerID;

Los resultados de las consultas tienen una carga diferida por defecto, por lo que no está hablando con la base de datos hasta que realmente necesite los datos. LINQ en .Net también simplifica enormemente las actualizaciones al mantener un contexto de datos de cualquier cambio que haya realizado, y solo actualiza los campos que usted cambia.

Answer 5

Hay una discusión sobre esto aquí

La técnica obtiene la página número 100,000 de una base de datos de 150,000 líneas en 78ms

Usando el conocimiento del optimizador y SET ROWCOUNT, el primer EmployeeID en la página que se solicita se almacena en una variable local para un punto de partida. A continuación, configure ROWCOUNT en la cantidad máxima de registros que se solicita en @maximumRows. Esto permite buscar el conjunto de resultados de una manera mucho más eficiente. El uso de este método también aprovecha los índices preexistentes en la tabla, ya que va directamente a la tabla base y no a una tabla creada localmente.

Me temo que no puedo juzgar si es mejor que la respuesta aceptada actual.

Answer 6

En MS SQL Server 2005 y superior, ROW_NUMBER () parece funcionar:

T-SQL: paginación con ROW_NUMBER ()

DECLARE @PageNum AS INT;
DECLARE @PageSize AS INT;
SET @PageNum = 2;
SET @PageSize = 10;
WITH OrdersRN AS
(
SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum
,OrderID
,OrderDate
,CustomerID
,EmployeeID
FROM dbo.Orders
)
SELECT *
FROM OrdersRN
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
AND @PageNum * @PageSize
ORDER BY OrderDate
,OrderID;

Answer 7

En realidad, LINQ tiene métodos Skip y Take que se pueden combinar para elegir qué registros se obtienen.

Mira eso.

Para DB: Paginación en SQL Server 2005

Answer 8

En esencia, hay dos formas de hacer paginación en la base de datos (supongo que está usando SQL Server):

Usando OFFSET

Otros han explicado cómo se puede utilizar la función de clasificación ROW_NUMBER() OVER() para realizar páginas. Vale la pena mencionar que SQL Server 2012 finalmente incluyó soporte para la cláusula OFFSET .. FETCH estándar de SQL OFFSET .. FETCH :

SELECT first_name, last_name, score
FROM players
ORDER BY score DESC
OFFSET 40 ROWS FETCH NEXT 10 ROWS ONLY

Si está utilizando SQL Server 2012 y la compatibilidad con versiones anteriores no es un problema, probablemente debería preferir esta cláusula, ya que será ejecutada de manera más óptima por SQL Server en casos de esquina.

Usando el método SEEK

Existe una forma completamente diferente, mucho más rápida pero menos conocida de realizar paginación en SQL. Esto a menudo se llama el "método de búsqueda" como se describe en esta publicación del blog aquí .

SELECT TOP 10 first_name, last_name, score
FROM players
WHERE (score < @previousScore)
OR (score = @previousScore AND player_id < @previousPlayerId)
ORDER BY score DESC, player_id DESC

Los valores @previousScore y @previousPlayerId son los valores respectivos del último registro de la página anterior. Esto le permite buscar la página "siguiente". Si la dirección ORDER BY es ASC , simplemente use > lugar.

Con el método anterior, no puede pasar de inmediato a la página 4 sin haber buscado primero los 40 registros anteriores. Pero a menudo, no quieres ir tan lejos de todos modos. En cambio, obtiene una consulta mucho más rápida que podría obtener datos en tiempo constante, según su indexación. Además, sus páginas permanecen "estables", sin importar si los datos subyacentes cambian (por ejemplo, en la página 1, mientras se encuentra en la página 4).

Esta es la mejor manera de implementar la paginación cuando la carga es más lenta en las aplicaciones web, por ejemplo.

Tenga en cuenta que el "método de búsqueda" también se denomina paginación del conjunto de claves .