sql - ejemplos - forma eficiente de implementar paginación

paginacion sql server 2016

Question

¿Debo usar los métodos Skip() y Take() LINQ para paginación, o implementar mi propia paginación con una consulta SQL?

¿Cuál es más eficiente? ¿Por qué elegiría uno sobre el otro?

Estoy usando SQL Server 2008, ASP.NET MVC y LINQ.

Answer 1

En 2008 no podemos usar Skip (). Take ()

El camino es:

var MinPageRank = (PageNumber - 1) * NumInPage + 1
var MaxPageRank = PageNumber * NumInPage

var visit = Visita.FromSql($"SELECT * FROM (SELECT [RANK] = ROW_NUMBER() OVER (ORDER BY Hora DESC),* FROM Visita WHERE ) A WHERE A.[RANK] BETWEEN {MinPageRank} AND {MaxPageRank}").ToList();

Answer 2

En SQL Server 2008:

DECLARE @PAGE INTEGER = 2
DECLARE @TAKE INTEGER = 50

SELECT [t1].*
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[COLUMNORDER] DESC) AS [ROW_NUMBER], [t0].*
    FROM [dbo].[TABLA] AS [t0]
    WHERE ([t0].[COLUMNS_CONDITIONS] = 1)
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN ((@PAGE*@TAKE) - (@TAKE-1)) AND (@PAGE*@TAKE)
ORDER BY [t1].[ROW_NUMBER]

En t0, todos los registros En t1 son solo los que corresponden a esa página

Answer 3

Intenta usar

FROM [TableX]
ORDER BY [FieldX]
OFFSET 500 ROWS
FETCH NEXT 100 ROWS ONLY

para obtener las filas de 501 a 600 en el servidor SQL, sin cargarlas en la memoria. Tenga en cuenta que esta sintaxis está disponible solo con SQL Server 2012

Answer 4

LinqToSql convertirá automáticamente un .Skip (N1) .Take (N2) en la sintaxis de TSQL por usted. De hecho, cada "consulta" que haces en Linq, en realidad solo está creando una consulta SQL para ti en el fondo. Para probar esto, simplemente ejecute el Analizador de SQL mientras se ejecuta su aplicación.

La metodología de omisión / recepción me ha funcionado muy bien, y otras de lo que he leído.

Por curiosidad, ¿qué tipo de consulta de autopaginación tienes, que crees que es más eficiente que la omisión / toma de Linq?

Answer 5

Mientras que LINQ-to-SQL generará una cláusula OFFSET (posiblemente emulada usando ROW_NUMBER() OVER() como otros mencionaron ), existe una forma completamente diferente, mucho más rápida 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 .

Answer 6

Puede implementar la paginación de esta manera simple pasando el PageIndex

Declare @PageIndex INT = 1
Declare  @PageSize INT = 20

Select ROW_NUMBER() OVER ( ORDER BY Products.Name ASC )  AS RowNumber,
    Products.ID,
    Products.Name
into #Result 
From Products

SELECT @RecordCount = COUNT(*) FROM #Results 

SELECT * 
FROM #Results
WHERE RowNumber
BETWEEN
    (@PageIndex -1) * @PageSize + 1 
    AND
    (((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1

Answer 7

Tratando de darle una breve respuesta a su duda, si ejecuta los métodos de skip(n).take(m) en linq (con SQL 2005/2008 como servidor de base de datos), su consulta utilizará la Select ROW_NUMBER() Over ... .declaración, con es de alguna manera paginación directa en el motor SQL.

Dando un ejemplo, tengo una tabla db llamada mtcity y escribí la siguiente consulta (también funciona con linq para las entidades):

using (DataClasses1DataContext c = new DataClasses1DataContext())
{
    var query = (from MtCity2 c1 in c.MtCity2s
                select c1).Skip(3).Take(3);
    //Doing something with the query.
}

La consulta resultante será:

SELECT [t1].[CodCity], 
    [t1].[CodCountry], 
    [t1].[CodRegion], 
    [t1].[Name],  
    [t1].[Code]
FROM (
    SELECT ROW_NUMBER() OVER (
        ORDER BY [t0].[CodCity], 
        [t0].[CodCountry], 
        [t0].[CodRegion], 
        [t0].[Name],
        [t0].[Code]) AS [ROW_NUMBER], 
        [t0].[CodCity], 
        [t0].[CodCountry], 
        [t0].[CodRegion], 
        [t0].[Name],
        [t0].[Code]
    FROM [dbo].[MtCity] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]

Lo cual es un acceso de datos en ventanas (muy bien, por cierto, devolverá datos desde el principio y accederá a la tabla siempre que se cumplan las condiciones). Esto será muy similar a:

With CityEntities As 
(
    Select ROW_NUMBER() Over (Order By CodCity) As Row,
        CodCity //here is only accessed by the Index as CodCity is the primary
    From dbo.mtcity
)
Select [t0].[CodCity], 
        [t0].[CodCountry], 
        [t0].[CodRegion], 
        [t0].[Name],
        [t0].[Code]
From CityEntities c
Inner Join dbo.MtCity t0 on c.CodCity = t0.CodCity
Where c.Row Between @p0 + 1 AND @p0 + @p1
Order By c.Row Asc

Con la excepción de que, esta segunda consulta se ejecutará más rápido que el resultado de linq porque usará exclusivamente el índice para crear la ventana de acceso a datos; esto significa que, si necesita algún tipo de filtrado, el filtro debe estar (o debe estar) en la lista de Entidades (donde se crea la fila) y también se deben crear algunos índices para mantener el buen rendimiento.

Ahora, ¿qué es mejor?

Si tiene un flujo de trabajo bastante sólido en su lógica, implementar la forma SQL correcta será complicado. En ese caso, LINQ será la solución.

Si puede bajar esa parte de la lógica directamente a SQL (en un procedimiento almacenado), será aún mejor porque puede implementar la segunda consulta que le mostré (usando índices) y permitir que SQL genere y almacene el Plan de ejecución del consulta (mejorando el rendimiento).

Answer 8

Utilizamos un CTE envuelto en SQL dinámico (porque nuestra aplicación requiere clasificación dinámica del lado del servidor de datos) dentro de un procedimiento almacenado. Puedo proporcionar un ejemplo básico si lo desea.

No he tenido la oportunidad de ver el T / SQL que produce LINQ. ¿Alguien puede publicar una muestra?

No usamos LINQ o acceso directo a las tablas, ya que requerimos la capa adicional de seguridad (si el SQL dinámico lo rompe un tanto).

Algo como esto debería hacer el truco. Puede agregar valores parametrizados para parámetros, etc.

exec sp_executesql ''WITH MyCTE AS (
    SELECT TOP (10) ROW_NUMBER () OVER '' + @SortingColumn + '' as RowID, Col1, Col2
    FROM MyTable
    WHERE Col4 = ''''Something''''
)
SELECT *
FROM MyCTE
WHERE RowID BETWEEN 10 and 20''

Answer 9

puedes mejorar aún más el rendimiento, chech this

From CityEntities c
Inner Join dbo.MtCity t0 on c.CodCity = t0.CodCity
Where c.Row Between @p0 + 1 AND @p0 + @p1
Order By c.Row Asc

si usa el de de esta manera, dará mejores resultados:

From   dbo.MtCity  t0
   Inner Join  CityEntities c on c.CodCity = t0.CodCity

razón: porque estás usando la clase where en la tabla CityEntities que eliminará muchos registros antes de unirte a MtCity, así que 100% seguro de que aumentará el rendimiento muchas veces ...

De todos modos, la respuesta de rodrigoelp es realmente útil.

Gracias