La paginación es un concepto común que permite a los usuarios ver datos en pequeños subconjuntos, similar a pasar las páginas de un libro. Si bien muchas herramientas de desarrollo proporcionan funcionalidad de paginación incorporada, es importante considerar la escalabilidad y el rendimiento al implementar la paginación en una aplicación.
El mayor problema con la paginación incorporada es que recupera todos los datos de una consulta posterior y realiza el filtrado en la capa de aplicación, en lugar de en la capa de base de datos. Esto puede generar una mayor sobrecarga y tiempos de carga más lentos, frustrando a los usuarios que esperan un rendimiento rápido.
Afortunadamente, SQL Server 2005 introdujo una nueva funcionalidad llamada Row_Number, que facilita la paginación personalizada. Row_Number permite a los desarrolladores asignar un número secuencial a cada fila devuelta, basado en la partición y el orden especificados. Al devolver solo los datos necesarios a la aplicación, podemos mejorar el rendimiento y reducir el estrés en el servidor.
Echemos un vistazo a un ejemplo de cómo implementar la paginación personalizada en SQL Server:
CREATE PROCEDURE usp_ContactPaging (
@SortCol VARCHAR(25) = 'ContactId ASC',
@FirstName VARCHAR(25) = NULL,
@LastName VARCHAR(25) = NULL,
@pgSize INT = 25,
@pgNbr INT = 1
)
AS
BEGIN
DECLARE @NbrPages INT
IF @FirstName IS NULL AND @LastName IS NULL
BEGIN
SELECT @NbrPages = CEILING(count(*) / (@pgSize * 1.0)) FROM Contacts
END
IF @FirstName IS NOT NULL AND @LastName IS NULL
BEGIN
SELECT @NbrPages = CEILING(count(*) / (@pgSize * 1.0)) FROM Contacts WHERE [FirstName] = @FirstName
END
IF @FirstName IS NULL AND @LastName IS NOT NULL
BEGIN
SELECT @NbrPages = CEILING(count(*) / (@pgSize * 1.0)) FROM Contacts WHERE [LastName] = @LastName
END
IF @FirstName IS NOT NULL AND @LastName IS NOT NULL
BEGIN
SELECT @NbrPages = CEILING(count(*) / (@pgSize * 1.0)) FROM Contacts WHERE [FirstName] = @FirstName AND [LastName] = @LastName
END
;WITH PagingCTE (Row_ID, ContactId, FirstName, LastName)
AS
(
SELECT ROW_NUMBER() OVER (
ORDER BY
CASE WHEN @SortCol = 'FirstName DESC' THEN FirstName END DESC,
CASE WHEN @SortCol = 'FirstName ASC' THEN FirstName END ASC,
CASE WHEN @SortCol = 'LastName ASC' THEN LastName END ASC,
CASE WHEN @SortCol = 'LastName DESC' THEN LastName END DESC,
CASE WHEN @SortCol = 'ContactID ASC' THEN ContactId END ASC,
CASE WHEN @SortCol = 'ContactID DESC' THEN ContactId END DESC
) AS [Row_ID],
ContactId,
FirstName,
LastName
FROM Contacts
WHERE [FirstName] = COALESCE(@FirstName, FirstName)
AND [LastName] = COALESCE(@LastName, LastName)
)
SELECT
Row_ID,
ContactId,
FirstName,
LastName,
@pgNbr AS PageNumber,
@NbrPages AS TotalNbrPages
FROM PagingCTE
WHERE Row_ID >= (@pgSize * @pgNbr) - (@pgSize - 1)
AND Row_ID <= @pgSize * @pgNbr
END
GOEste procedimiento almacenado utiliza una Expresión de Tabla Común (CTE) para crear la numeración de filas. Las variables incluyen la columna de ordenamiento, el nombre, el apellido, el número de página y el tamaño de página. La cláusula where utiliza una fórmula basada en el tamaño de página y el número de página para calcular qué filas deben devolverse a la aplicación. Al enviar valores como @pgSize = 25 y @pgNbr = 1, le estamos indicando al procedimiento que devuelva la primera página con 25 filas.
Una cosa importante a tener en cuenta es la cláusula order by, que ordena los datos en función del parámetro pasado. El uso de expresiones case permite un ordenamiento dinámico sin la necesidad de SQL dinámico, lo que proporciona un buen rendimiento y minimiza los riesgos de seguridad.
Vale la pena mencionar que el procedimiento almacenado anterior se puede optimizar aún más utilizando lógica de flujo de control para introducir búsquedas de índices. Sin embargo, este enfoque puede resultar en un procedimiento almacenado más largo y complejo.
Otra opción es utilizar SQL dinámico parametrizado, que permite una mayor flexibilidad y reutilización del plan de consulta. Al ejecutar SQL dinámico utilizando el procedimiento almacenado del sistema sp_executesql, podemos parametrizar la declaración y reducir el riesgo de ataques de inyección.
Aquí hay un ejemplo del procedimiento almacenado utilizando SQL dinámico parametrizado:
ALTER PROCEDURE usp_ContactPaging (
@SortCol VARCHAR(25) = 'ContactId ASC',
@FirstName VARCHAR(25) = NULL,
@LastName VARCHAR(25) = NULL,
@pgSize INT = 25,
@pgNbr INT = 1
)
AS
BEGIN
DECLARE @SQL nvarchar(max), @params nvarchar(100)
DECLARE @NbrPages INT
IF @FirstName IS NULL AND @LastName IS NULL
BEGIN
SELECT @NbrPages = CEILING(count(*) / (@pgSize * 1.0)) FROM Contacts
END
IF @FirstName IS NOT NULL AND @LastName IS NULL
BEGIN
SELECT @NbrPages = CEILING(count(*) / (@pgSize * 1.0)) FROM Contacts WHERE [FirstName] = @FirstName
END
IF @FirstName IS NULL AND @LastName IS NOT NULL
BEGIN
SELECT @NbrPages = CEILING(count(*) / (@pgSize * 1.0)) FROM Contacts WHERE [LastName] = @LastName
END
IF @FirstName IS NOT NULL AND @LastName IS NOT NULL
BEGIN
SELECT @NbrPages = CEILING(count(*) / (@pgSize * 1.0)) FROM Contacts WHERE [FirstName] = @FirstName AND [LastName] = @LastName
END
SET @params = N'@SIZE INT, @nbr INT, @Pages INT, @Sort VARCHAR(25)'
SET @SQL = N'
;WITH PagingCTE (Row_ID, ContactId, FirstName, LastName)
AS
(
SELECT --PUEDES ELIMINAR EL CASE Y USAR LA VARIABLE DE ORDENAMIENTO AQUÍ PERO AUMENTA LA SUSCEPTIBILIDAD
ROW_NUMBER() OVER (
ORDER BY
CASE WHEN @Sort=''FirstName DESC'' THEN FirstName END DESC,
CASE WHEN @Sort=''FirstName ASC'' THEN FirstName END ASC,
CASE WHEN @Sort=''LastName ASC'' THEN LastName END ASC,
CASE WHEN @Sort=''LastName DESC'' THEN LastName END DESC,
CASE WHEN @Sort=''ContactID ASC'' THEN ContactId END ASC,
CASE WHEN @Sort=''ContactID DESC'' THEN ContactId END DESC
) AS [Row_ID],
ContactId,
FirstName,
LastName
FROM Contacts '
+ CASE
WHEN @FirstName IS NOT NULL AND @LastName IS NULL THEN N'WHERE FirstName = ' + QUOTENAME(@FirstName, '''')
WHEN @FirstName IS NULL AND @LastName IS NOT NULL THEN N'WHERE LastName = ' + QUOTENAME(@LastName, '''')
WHEN @FirstName IS NOT NULL AND @LastName IS NOT NULL THEN N'WHERE FirstName = ' + QUOTENAME(@FirstName, '''') + ' AND ' + N'LastName = ' + QUOTENAME(@LastName, '''')
END
+ N'
)
SELECT
Row_ID,
ContactId,
FirstName,
LastName,
@Nbr AS PageNumber,
@Pages AS TotalNbrPages
FROM PagingCTE
WHERE Row_ID >= (@SIZE * @nbr) - (@SIZE -1)
AND Row_ID <= @SIZE * @nbr'
EXEC sp_executesql @SQL, @params, @SIZE = @pgSize, @nbr = @pgNbr, @Pages = @NbrPages, @Sort = @SortCol
END
GOEl SQL dinámico parametrizado permite una mayor flexibilidad y reutilización del plan de consulta. Al utilizar sp_executesql para ejecutar la declaración SQL dinámica, podemos pasar parámetros y reducir el riesgo de ataques de inyección.
La paginación personalizada en SQL Server puede mejorar en gran medida la satisfacción del usuario, los tiempos de carga de página y reducir la cantidad de datos devueltos a la aplicación. Al implementar la lógica de paginación personalizada utilizando Row_Number, los desarrolladores pueden lograr un mejor rendimiento y escalabilidad en sus aplicaciones.