Discusión sobre la implementación de paginación de conjuntos de resultados grandes en ASP.NET

En las aplicaciones web, paginar un gran conjunto de resultados de bases de datos es un problema bien conocido. En pocas palabras, no desea que todos los datos de la consulta se muestren en una sola página, por lo que la visualización con paginación es más apropiada. Aunque esta no es una tarea sencilla en ASP tradicional, en ASP.NET, el control DataGrid simplifica este proceso a sólo unas pocas líneas de código. Por lo tanto, en asp.net, la paginación es muy simple, pero el evento de paginación DataGrid predeterminado leerá todos los registros de la base de datos y los colocará en la aplicación web asp.net. Cuando tiene más de un millón de datos, esto causará serios problemas de rendimiento (si no lo cree, puede ejecutar una consulta en su aplicación y observar el consumo de memoria de aspnet_wp.exe en el administrador de tareas). Situación) Esta es la razón es necesario personalizar el comportamiento de la paginación para garantizar que solo se obtengan los registros de datos requeridos por la página actual.

Hay muchos artículos y publicaciones sobre este problema en Internet, así como algunas soluciones maduras. Mi propósito al escribir este artículo no es mostrarle un procedimiento almacenado que resolverá todos sus problemas, sino optimizar los métodos existentes y brindarle una aplicación para probar para que pueda hacerlo de acuerdo con sus necesidades de desarrollo.

Pero no estoy muy satisfecho con los métodos que se introducen actualmente en línea. En primer lugar, se utiliza ADO tradicional, que obviamente está escrito para ASP "antiguo". Los métodos restantes son procedimientos almacenados de SQL Server y algunos de ellos no se pueden utilizar debido a tiempos de respuesta demasiado lentos, como puede ver en los resultados de rendimiento al final del artículo, pero hay algunos que me llamaron la atención.

Generalización

Quiero analizar cuidadosamente los tres métodos más utilizados actualmente, que son tablas temporales (TempTable), SQL dinámico (DynamicSQL) y recuento de filas (Rowcount). A continuación, prefiero llamar al segundo método el método Asc-Desc (ascendente-descendente). No creo que SQL dinámico sea un buen nombre porque también puedes aplicar la lógica de SQL dinámico en otro método. El problema común con todos estos procedimientos almacenados es que debe estimar qué columnas va a ordenar, no solo las columnas de clave principal (columnas PK), lo que puede generar una serie de problemas: para cada consulta, debe mostrarlo a través de paginación, lo que significa que para cada columna de clasificación diferente debe tener muchas consultas de paginación diferentes, lo que significa que debe realizar un procedimiento almacenado diferente para cada columna de clasificación (independientemente del método de paginación que se utilice), o debe coloque esta funcionalidad en un procedimiento almacenado con la ayuda de SQL dinámico. Estos dos métodos tienen un pequeño impacto en el rendimiento, pero aumentan la capacidad de mantenimiento, especialmente si necesita utilizar este método para mostrar diferentes consultas. Por lo tanto, en este artículo intentaré utilizar SQL dinámico para resumir todos los procedimientos almacenados, pero por algunas razones, solo podemos lograr una universalidad parcial, por lo que aún tenemos que escribir procedimientos almacenados independientes para consultas complejas.

El segundo problema al permitir todos los campos de clasificación, incluidas las columnas de clave principal, es que si esas columnas no están indexadas correctamente, ninguno de estos métodos ayudará. En todos estos métodos, primero se debe ordenar una fuente de paginación. Para tablas de datos grandes, el costo de ordenar usando columnas que no son de índice es insignificante. En este caso, todos los procedimientos almacenados no se pueden utilizar en situaciones reales debido al largo tiempo de respuesta. (El tiempo correspondiente varía desde unos segundos hasta unos minutos, dependiendo del tamaño de la tabla y del primer registro a obtener). Los índices en otras columnas pueden introducir problemas de rendimiento adicionales no deseados; por ejemplo, pueden volverse muy lentos si importa una gran cantidad de datos todos los días.

Tabla temporal

Primero, voy a hablar sobre el método de tabla temporal. Esta es una solución ampliamente recomendada que he encontrado varias veces en mis proyectos. Echemos un vistazo a la esencia de este método:

CREATE TABLE #Temp(
ID int IDENTIDAD CLAVE PRIMARIA,
PK /*aquí va PKtype*/
)

INSERTAR EN #Temp SELECCIONAR PK DE la tabla ORDENAR POR SortColumn

SELECCIONAR DE la tabla JOIN # Temp temp ON Table.PK = temp .PK ORDENAR POR temp .ID DONDE ID > @StartRow AND ID< @EndRow

copiando todas las filas en temporal tabla, podemos optimizar aún más la consulta (SELECT TOP EndRow...), pero la clave es el peor de los casos: una tabla que contiene 1 millón de registros generará una tabla temporal con 1 millón de registros.

ascendente-descendente

en mi prueba.

Este método utiliza la clasificación predeterminada en la subconsulta y la clasificación inversa en la consulta principal.

DECLARAR @temp TABLA(
PK /* Tipo PK */
PRIMARIO NO NULO
)

INSERTAR EN @temp SELECCIONAR ARRIBA @PageSize PK DESDE
(
SELECCIONAR ARRIBA(@StartRow + @PageSize)
PAQUETE,
SortColumn /* Si la columna de clasificación es diferente de la PK, SortColumn debe
ser recuperado también, de lo contrario solo es necesario el PK
*/

ORDENAR POR OrdenarColumna
/*
orden predeterminado: normalmente ASC
*/
)

ORDENAR POR OrdenarColumna
/*
orden predeterminado invertido: normalmente DESC
*/

SELECCIONAR DE la tabla JOIN @Temp temp ON Tabla .PK= temp .PK
ORDENAR POR OrdenarColumna
/*
orden predeterminada
*/

recuento de filas

se basa en la expresión SET ROWCOUNT en SQL, de modo que se pueden omitir filas innecesarias y se pueden obtener los registros de fila requeridos:

DECLARE @Sort /* el tipo de columna de clasificación */

SET ROWCOUNT @ StartRow

SELECCIONAR @Sort=SortColumn DESDE la tabla ORDENAR POR SortColumn
SET ROWCOUNT @PageSize

SELECCIONAR DE la tabla DONDE SortColumn >= @Sort ORDENAR POR SortColumn

de subconsulta

que he considerado, y sus fuentes son diferentes. El primero es el conocido método de consulta triple o autoconsulta. En este artículo, también utilizo una lógica común similar que abarca todos los demás procedimientos almacenados. La idea aquí es conectar con todo el proceso, hice algunas reducciones al código original ya que no se necesita el recuento de registros en mis pruebas)

SELECCIONE DE la tabla DONDE PK IN(
SELECCIONE EL PK SUPERIOR @PageSize DE LA TABLA DONDE EL PK NO ESTÁ EN
(
SELECCIONE TOP @StartRow PK DE la tabla ORDENAR POR SortColumn)
ORDENAR POR OrdenarColumna)
ORDENAR POR SortColumn

Cursor

Mientras miraba el grupo de discusión de Google, encontré el último método. Este método utiliza un cursor dinámico del lado del servidor. Muchas personas intentan evitar el uso de cursores porque son irrelevantes e ineficientes debido a su orden. Pero mirando hacia atrás, la paginación es en realidad una tarea ordenada, debe regresar a la línea de inicio para registrar. En el método anterior, primero selecciona todas las filas antes de comenzar la grabación, agrega las filas requeridas para grabar y luego elimina todas las filas anteriores. Los cursores dinámicos tienen una opción FETCH RELATIVE que realiza saltos mágicos. La lógica básica es la siguiente:

DECLARE @PK /* PKType */

DECLARE @tblPK

TABLE(
PK /*PKType*/ CLAVE PRIMARIA NO NULA
)

DECLARAR PagingCursor CURSOR DYNAMICREAD_ONLY PARA
SELECCIONE @PK DE la tabla ORDENAR POR SortColumn

ABRIR PagingCursor
FETCH RELATIVE @StartRow DESDE PagingCursor HACIA @PK

MIENTRAS @PageSize>0 Y @@FETCH_STATUS =0
COMENZAR
INSERTAR @tblPK(PK) VALORES(@PK)
BUSCAR SIGUIENTE DESDE PagingCursor EN @PK
ESTABLECER @Tamaño de página = @Tamaño de página - 1
FIN

CERRAR
Cursor de paginación
DESASIGNAR
PagingCursor

SELECCIONAR DE la tabla JOIN @tblPK temp ON Tabla .PK= temp .PK

Generalización de consultas complejas

en ORDER BY SortColumn

Señalé antes que todos los procedimientos almacenados usan SQL dinámico para lograr la generalización, por lo que en teoría pueden usar cualquier tipo de consultas complejas. A continuación se muestra un ejemplo de una consulta compleja basada en la base de datos Northwind.

SELECCIONE Clientes.NombreContacto AS Cliente, Clientes.Dirección + ' , ' + Clientes.Ciudad + ', '+ Clientes.País
Dirección AS, SUMA([Detalles del pedido].Precio unitario*[Detalles del pedido] .Cantidad)
AS [Dinero total gastado]

FROM Clientes

INNER JOIN Pedidos ON Clientes.CustomerID = Pedidos.CustomerID

INNER JOIN [ Detalles del pedido ] ON Pedidos.ID del pedido = [Detalles del pedido].ID del pedido

WHERE Clientes.País <> 'EE.UU.' AND Clientes.País <> 'México '

GRUPO POR Clientes.Nombre de contacto,Clientes.Dirección,Clientes.Ciudad, Clientes.País
HAVING(SUM([OrderDetails].UnitPrice * [ OrderDetails ] .Quantity)) > 1000

ORDER BY Customer DESC, Address DESC

devuelve la llamada de almacenamiento de paginación de la segunda página de la siguiente manera:

EXEC NombreProcedimiento

/*Tablas */

'
Clientes
INNER JOIN Pedidos ON Customers.CustomerID=Orders.CustomerID
UNIÓN INTERNA [Detalles del pedido] ON Orders.OrderID=[OrderDetails].OrderID
'
,
/* PAQUETE */

'
Clientes.ID de cliente
'
,
/* ORDENAR */

'
Clientes.NombreContacto DESC,Clientes.DirecciónDESC
'
,

/*Número de página */

2
,

/*Tamaño de página */

10
,

/*Campos */

'
Clientes.Nombre de contacto COMO cliente,
Clientes.Dirección+'' , '' +Clientes.Ciudad+ '' , '' +Clientes.País ASDirección, SUM([Detalles del pedido].Precio unitario*[Detalles del pedido].Cantidad)AS[Dinero total gastado]
'
,

/*Filtro */

'
Clientes.País<>'' EE.UU. '' ANDClientes.País<> '' México ''' ,

/*GroupBy */

'
Clientes.ID de cliente, Clientes.Nombre de contacto, Clientes.Dirección,
Clientes.Ciudad,Clientes.País
TENER(SUM([Detalles del pedido].Precio unitario*[Detalles del pedido].Cantidad))>1000
'

Vale la pena señalar que usó alias en la instrucción ORDER BY en la consulta original, pero será mejor que no haga esto en un procedimiento almacenado paginado, porque omitir filas antes de comenzar a grabar lleva mucho tiempo. De hecho, existen muchos métodos de implementación, pero el principio es no incluir todos los campos al principio, sino solo incluir la columna de clave principal (equivalente a la columna de clasificación en el método RowCount), lo que puede acelerar la finalización de la tarea. Sólo en la página de solicitud se obtienen todos los campos obligatorios. Además, no hay alias de campo en la consulta final y, en las consultas de omisión de filas, se deben utilizar columnas de índice de antemano.

Hay otro problema con el procedimiento almacenado RowCount. Para lograr la generalización, solo se permite una columna en la declaración ORDER BY. Este también es un problema con el método ascendente-descendente y el método del cursor, aunque pueden ordenar varias columnas. se debe garantizar que solo haya un campo en la clave principal. Supongo que esto podría solucionarse con SQL más dinámico, pero en mi opinión no merece la pena. Si bien estas situaciones son posibles, no suceden con mucha frecuencia. Por lo general, puede utilizar los principios anteriores para paginar también de forma independiente los procedimientos almacenados.

Pruebas de rendimiento

En las pruebas, utilicé cuatro métodos; si tienes un método mejor, me interesaría conocerlo. De todos modos, necesito comparar estos métodos y evaluar su desempeño. En primer lugar, mi primera idea es escribir una aplicación de prueba asp.net que contenga un DataGrid de paginación y luego probar los resultados de la página. Por supuesto, esto no refleja el tiempo de respuesta real del procedimiento almacenado, por lo que una aplicación de consola es más adecuada. También incluí una aplicación web, pero no para pruebas de rendimiento, sino como ejemplo de paginación personalizada de DataGrid y procedimientos almacenados trabajando juntos.

En la prueba, utilicé una gran tabla de datos generada automáticamente e inserté alrededor de 500.000 datos. Si no tiene una tabla de este tipo para experimentar, puede hacer clic aquí para descargar un diseño de tabla y un script de procedimiento almacenado para generar datos. En lugar de utilizar una columna de clave primaria de incremento automático, utilicé un identificador único para identificar el registro. Si uso el script que mencioné anteriormente, podría considerar agregar una columna de incremento automático después de generar la tabla. Los datos de incremento automático se ordenarán numéricamente según la clave principal. Esto también significa que tiene la intención de utilizar un procedimiento almacenado paginado. con clasificación de clave principal para obtener los datos de la página actual.

Para implementar la prueba de rendimiento, llamé a un procedimiento almacenado específico varias veces a través de un bucle y luego calculé el tiempo de respuesta promedio. Teniendo en cuenta las razones del almacenamiento en caché, para modelar con mayor precisión la situación real, el tiempo que tarda la misma página en obtener datos de múltiples llamadas a un procedimiento almacenado generalmente no es adecuado para la evaluación. Por lo tanto, cuando llamamos al mismo procedimiento almacenado, el número de página solicitado para cada llamada debe ser aleatorio. Por supuesto, debemos suponer que el número de páginas es fijo, de 10 a 20 páginas, y que los datos con diferentes números de página se pueden obtener muchas veces, pero de forma aleatoria.

Una cosa que podemos notar fácilmente es que el tiempo de respuesta está determinado por la distancia de los datos de la página que se obtendrán en relación con la posición inicial del conjunto de resultados. Cuanto más lejos de la posición inicial del conjunto de resultados, más registros habrá. omitido. Esta es también la razón por la que no incluyo los 20 primeros en mi secuencia aleatoria. Como alternativa, usaría 2^n páginas, y el tamaño del bucle es el número de páginas diferentes necesarias * 1000, por lo que cada página se recupera casi 1000 veces (definitivamente habrá una desviación debido a motivos aleatorios)

Resultados

aquí son los resultados de mi prueba:






Conclusión

Las pruebas se realizaron en orden de mejor a peor: recuento de filas, cursor, ascendente-descendente, subconsulta. Una cosa interesante es que, por lo general, las personas rara vez visitan páginas después de las primeras cinco páginas, por lo que el método de subconsulta puede satisfacer sus necesidades en este caso, dependiendo del tamaño de su conjunto de resultados y de qué tan lejos está Para predecir la frecuencia de aparición de la página. , es probable que también utilices una combinación de estos métodos. Si fuera yo, preferiría el método de recuento de filas en cualquier caso, funciona bastante bien, incluso para la primera página, "cualquier caso" aquí representa algunos casos donde la generalización es difícil, en este caso, usaría un cursor. (Probablemente usaría el método de subconsulta para los dos primeros, y después el método del cursor)