Je dois travailler avec une liste potentiellement importante d'enregistrements et j'ai googler des moyens d'éviter de sélectionner toute la liste, mais je souhaite que les utilisateurs sélectionnent une page (comme de 1 à 10) et afficher les enregistrements en conséquence. .
dire, pour 1000 enregistrements, je disposerai de 100 pages de 10 enregistrements chacun et les 10 derniers enregistrements seront affichés d'abord, puis si l'utilisateur cliquera à la page 5, il affichera les enregistrements de 41 à 50.
Est-il une bonne idée d'ajouter un numéro de ligne à chaque enregistrement, puis de la requête en fonction du numéro de la ligne? Y a-t-il un meilleur moyen d'atteindre le résultat de radiomessagerie sans trop de surcharge? Jusqu'à présent, ces méthodes décrites ici sont les plus prometteuses:
http://developer.berlios.de/docman/display_doc.php?docid=739&group_id = 2899
7 Réponses :
Essayez quelque chose comme ceci:
declare @page int = 2
declare @size int = 10
declare @lower int = (@page - 1) * @size
declare @upper int = (@page ) * @size
select * from (
select
ROW_NUMBER() over (order by some_column) lfd,
* from your_table
) as t
where lfd between @lower and @upper
order by some_column
Row_Number est connu pour avoir des problèmes de performance avec de très grands ensembles de résultats: 4Guysfromrolla.com/wtbtech/042606 -1.shtml
ne devrait-il pas être 'déclarer @lower int = (@Page * @size) - (@SIZE - 1)'
@Roadwarrior: lien intéressant. Je l'ai ajouté à mon récent Blog Post sur une pagination plus rapide. Connaissez-vous sur le Rechercher la méthode ? Il ne permet pas de vrais compensations indexées, mais peut passer à la page "Suivant" en temps constant.
Au test, @reidevans est correct, il devrait être (@ page * @Size) - (@SIZE - 1) . Le (@ page - 1) * @Size retourne les rangées 1-10, puis 10-20 au lieu de 11-20 comme prévu.
La procédure stockée T-SQL suivante est une mise en œuvre NB: J'utilise une colonne d'identité séquentielle dans cet exemple, mais le code fonctionne sur Toute colonne adaptée au tri de la page. En outre, les pauses de séquence dans la colonne utilisée n'affectent pas le résultat car le code sélectionne un nombre de lignes plutôt qu'une valeur de colonne. P> EDIT: Si vous triez sur une colonne avec potentiellement non Valeurs uniques (par exemple, n'ajoutez pas de nom), puis ajoutez une deuxième colonne à la clause de la commande par la clause pour effectuer les valeurs de tri unique à nouveau. p> CREATE PROCEDURE dbo.PagingTest
(
@PageNumber int,
@PageSize int
)
AS
DECLARE @FirstId int, @FirstRow int
SET @FirstRow = ( (@PageNumber - 1) * @PageSize ) + 1
SET ROWCOUNT @FirstRow
-- Add check here to ensure that @FirstRow is not
-- greater than the number of rows in the table.
SELECT @FirstId = [Id]
FROM dbo.TestTable
ORDER BY [Id]
SET ROWCOUNT @PageSize
SELECT *
FROM dbo.TestTable
WHERE [Id] >= @FirstId
ORDER BY [Id]
SET ROWCOUNT 0
GO
@Scotte: Les pauses ID ne font aucune différence pour ce code, car la taille supérieure est le nombre de lignes de la page (pas le numéro d'identification).
@Scotte: En outre, la colonne utilisée n'a pas besoin d'être un identifiant incrémentiel. Vous pouvez utiliser n'importe quelle colonne adaptée au tri.
@MARTIN: D'accord, le haut devrait fonctionner aussi bien que toute version de SQL Server Greater Tham 2000. Je n'ai pas testé cela, alors je suis venu avec le code que j'ai testé.
En pensant à ça. Cela finira toujours par faire 2 différentes analyses d'index, j'aurais pensé que cela serait légèrement moins efficace, alors il suffit d'utiliser Row_Count et de le faire dans une seule analyse car il devra naviguer dans la racine d'index. et pages intermédiaires pour "trouver sa place" lorsque vous effectuez le où [id]> = @firstid bit . Cela suppose que les deux analyses seront cependant sur le même indice. Je suppose que si le premier scan était contre un indice extrêmement étroit sur ID seul et le second contre un indice de couverture avec ID comme champ de premier plan possible!
@David: SQL Server int. Vous ne pensez pas que 2 ^ 31-1 est assez grand?
@Roadwarrior Répondre en retard - Le problème n'est pas avec une précision entière; C'est avec des clés non uniques dans un environnement multi-serveur et distribué. LOG D'EXPÉDITION, etc. Travaillez autour de cela, mais ce sont des kroups qui pourraient être facilement à l'aide d'un guid. (Je n'ai pas encore entendu un argument factuellement correct contre cela, mais je suis toutes les oreilles.)
@David, dans 20 ans et plus, je n'ai jamais utilisé de fragments, de réplication de la base de données ou de toute autre technique de base de données distribuée. Un cluster de base de données est aussi loin que je suis allé, alors j'ai toujours été en mesure d'utiliser des colonnes d'identité. Je conviens que les identités ne sont pas l'approche idéale dans un environnement où votre base de données doit vraiment être distribuée.
@Roadwarrior et INTS sont totalement acceptables dans cette circonstance. Je traite avec des bases de données distribuées géographiquement qui doivent continuer à fonctionner lorsque le tuyau sous-marin de graisse transatlantique échoue, une unicité ainsi garantie est un peu plus impliquée.
Je suppose que cela ne fonctionne que si la colonne que vous commandez contient des valeurs uniques? Et si je voulais trier sur le nom de famille - alors quand je sélectionne @firstid (ou plutôt, @firstvalue ), je ne peux pas faire> = dessus, car il comprendra alors certaines lignes qui ne devraient pas être, car leurs valeurs de dernière nom sont identiques à la valeur de la dernière nom à @firstrow .
@MajorreFactoring, oui, vous êtes correct. Si vous utilisez une colonne contenant des valeurs non uniques pour tri de page, si les valeurs non uniques s'étendent sur une limite de page, vous verrez un tri imprévisible à cette limite de la page. Pour résoudre ce problème, je vous recommande d'ajouter une deuxième colonne à la clause de la commande. Tant que la combinaison des colonnes a une valeur unique, cela fonctionnera.
@Roadwarrior - ah. Astucieux. Belle petite technique.
Vous pourriez être intéressé par la "méthode de recherche", que j'ai décrit dans ma réponse . C'est un approche plus générique d'utilisation des prédicats (et d'indexation ainsi) sur des compensations pour la pagination.
Salut, qu'en est-il si vous avez des conditions?
Si vous utilisez un CTE avec deux colonnes Row_Number () - une tresse triée ASC, une descente, vous obtenez des numéros de ligne pour la pagination ainsi que le total des enregistrements en ajoutant les deux colonnes Row_Number.
Une note sur le tri inverse, si la colonne a des données non uniques, vous avez une chance que le tri dans la clause over est incompatible.
De plus, vous pouvez utiliser ", compter (*) sur () comme total_row_count" qui retournera une colonne du même numéro encore et encore.
Essayez ceci
Voici une version mise à jour du code de @ Roadwarrior, en utilisant le haut. La performance est identique et extrêmement rapide. Assurez-vous d'avoir un index sur testtable.id
D'autres ont expliqué comment la fonction Row_Number () sur () code> La fonction de classement peut être utilisée pour effectuer des pages. Il convient de mentionner que SQL Server 2012 a finalement inclus prise en charge de la standard SQL offset .. Fetch code> Clause: P> 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
Si un bon cas peut être fait que la recherche de saut à la page n'est pas un excellent design de toute façon, je pense que vous avez vraiment besoin de montrer à vos utilisateurs combien de résultats leur recherche renvoyées (ou au moins une approximation, comme le fait Google, Bien que je ne suis pas sûr que des bases de données SQL aient ce type de fonctionnalité). Cette méthode nécessite donc une requête de comptage supplémentaire, ce qui augmente beaucoup le coût.
Cela peut être une chose YMMV, mais j'ai fait une comparaison entre cette méthode de recherche (sur SQL Server 2008, Hélas) et la méthode «Sainte Graale» de l'article SQLServerCentral, qui vous donne le compte et les deux performances de la même manière. L'ajout du coût de la requête de comptage à la méthode de recherche a toutefois fait que la Graale interroge le gagnant clair.
Pourquoi ne pas utiliser recommandé solution :
Sélectionner la valeur Produit de AventureWorksentities.Products comme produit Commande par produit.Listprice Skip @skip Limit @Limit