moyen efficace de mettre en œuvre la pagination

Question

Dois-je utiliser LINQ Skip() et Take() méthode pour la pagination, ou de mettre en œuvre mon propre pagination avec une requête SQL?

Qui est le plus efficace? Pourquoi choisir l'un plutôt que l'autre?

Je suis à l'aide de SQL Server 2008, ASP.NET MVC et LINQ.

Answer 1

Essayer de vous donner une brève réponse à vos doutes, si vous exécutez l' skip(n).take(m) méthodes sur linq (avec SQL 2005 / 2008 en tant que serveur de base de données) de votre recherche à l'aide de l' Select ROW_NUMBER() Over ... déclaration, est en quelque sorte directe de pagination dans le moteur SQL.

Vous donner un exemple, j'ai une table db appelés mtcity et j'ai écrit la requête suivante (travail aussi bien avec linq to entities):

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 requête sera:

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]

Qui est une fenêtre d'accès aux données (assez cool, btw cuz sera de retour les données depuis le début et accéder à la table aussi longtemps que les conditions sont remplies). Ce sera très similaire à:

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

Sauf que, cette deuxième requête sera exécutée plus rapidement que le linq résultat parce qu'il sera exclusivement à l'aide de l'index pour créer la fenêtre d'accès aux données, ce qui signifie, si vous avez besoin d'un filtrage, le filtrage doit être (ou doit être) dans l'Entité d'inscription (où la ligne est créée) et certains indices doivent être créés et à maintenir la qualité de la performance.

Maintenant, quoi de mieux?

Si vous avez beaucoup de solides flux de travail dans votre logique, la mise en œuvre de la bonne SQL chemin va être compliqué. Dans ce cas, LINQ sera la solution.

Si vous pouvez diminuer la partie de la logique directement en SQL (dans une procédure stockée), ce sera encore mieux parce que vous pouvez mettre en œuvre la deuxième requête que je vous ai montré (à l'aide d'indices) et permettre de SQL pour générer et stocker le Plan d'Exécution de la requête (amélioration des performances).

Answer 2

Essayez d'utiliser

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

pour obtenir les lignes de 501 à 600 dans SQL server, sans les charger en mémoire. Notez que cette syntaxe est devenu disponible avec SQL Server 2012 seulement

Answer 3

Alors que LINQ-to-SQL génère un OFFSET de l'alinéa (éventuellement émulées à l'aide de ROW_NUMBER() OVER() comme d'autres l'ont mentionné), il est tout à fait différent, beaucoup plus rapide pour effectuer l'échange en SQL. Ceci est souvent appelé la "méthode de recherche", comme décrit dans ce billet de blog ici.

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

L' @previousScore et @previousPlayerId des valeurs sont les valeurs respectives du dernier enregistrement de la page précédente. Cela vous permet de chercher les "à côté" de la page. Si l' ORDER BY direction ASC, il suffit d'utiliser > à la place.

Avec la méthode ci-dessus, vous ne pouvez pas sauter immédiatement à la page 4, sans avoir d'abord cherché le précédent de 40 dossiers. Mais souvent, vous ne voulez pas sauter aussi loin de toute façon. Au lieu de cela, vous obtenez un beaucoup plus rapide requête qui pourrait être en mesure d'extraire des données en temps constant, en fonction de votre indexation. De Plus, vos pages de rester "stable", peu importe si les données sous-jacentes des changements (p. ex., page 1, alors que vous êtes sur la page 4).

C'est la meilleure façon de mettre en œuvre la pagination lorsque le chargement paresseux plus de données dans les applications web, par exemple.

Remarque, la "méthode de recherche" est aussi appelé jeu de clés de pagination.

Answer 4

LinqToSql sera automatiquement convertir un .Skip(N1).Prendre(N2) dans le TSQL syntaxe pour vous. En fait, chaque "requête" vous n'en Linq, est en fait tout simplement la création d'une requête SQL pour vous dans le fond. Pour tester cela, il suffit d'exécuter le générateur de profils SQL tandis que votre demande est en cours d'exécution.

Le skip/prendre méthodologie a très bien fonctionné pour moi, et d'autres de ce que j'ai lu.

Par curiosité, quel est le type de l'auto-pagination requête avez-vous, qui vous croyez est plus efficace que Linq passer/prendre?

Answer 5

Nous utilisons une expression de table commune enveloppé dans du SQL Dynamique (car notre application nécessite dynamique de tri de données côté serveur) à l'intérieur d'une procédure stockée. Je peux fournir un exemple de base si vous le souhaitez.

Je n'ai pas eu la chance de regarder la T/SQL que LINQ produit. Quelqu'un peut-il poster un échantillon?

Nous n'avons pas utiliser LINQ ou tout droit d'accès aux tables que nous avons besoin de la couche de sécurité supplémentaire (accordé le SQL dynamique des pauses un peu).

Quelque chose comme ceci devrait faire l'affaire. Vous pouvez ajouter dans les valeurs paramétrées pour les paramètres, 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'