Moyen efficace d'obtenir @@ rowcount à partir d'une requête à l'aide de row_number

Question

J'ai un coûteux requête à l'aide de la fonction row_number plus() fonctionnalités de SQL Server 2005. - Je renvoyer seulement à une sous-liste de ces documents que la requête est paginé. Cependant, j'aimerais aussi retourner le nombre total d'enregistrements, non seulement le paginé sous-ensemble. L'exécution de la requête effectivement deux fois pour obtenir le nombre est hors de question.

La sélection de la fonction count(*) est également hors de question que la performance est absolument terrible quand je l'ai essayé ce.

Ce que j'aimerais vraiment est @@ROW_NUMBERROWCOUNT :-)

Answer 1

Au fil des années un tas de développeur sweat a disparu dans efficacement la pagination des ensembles de résultats. Pourtant, il n'y a pas de réponse unique, tout dépend de votre cas d'utilisation. Une partie des cas d'utilisation est l'obtention de votre page de manière efficace, la partie est de déterminer le nombre de lignes dans un jeu de résultat complet. Donc désolé si j'ai éloigner un peu de recherche, mais les deux sont assez étroitement associée dans mon esprit.

Il ya beaucoup de stratégies, dont la plupart sont mauvais si vous avez toute sorte de volume de données et à ne rentre pas dans les cas d'utilisation. Si ce n'est pas une liste exhaustive, voici quelques unes des options.....

Séparée Count(*)

• exécuter une requête distincte qui fait un simple "select count(*) from Matable"
• simple et facile pour une petite table de
• bonne sur un non filtrés de la grande table qui est soit étroite ou a un compact index non-cluster, vous pouvez utiliser
• les sauts vers le bas lorsque vous avez un compliqué WHERE/JOIN critères car l'exécution de l' WHERE/JOIN deux fois, c'est cher.
• sauts sur une large indice parce que le nombre de lectures monte.

Combiner ROW_Number() OVER() et COUNT(1) OVER(PARTITION By 1)

• Ceci a été suggéré par @RBarryYoung. Il a l'avantage d'être simple à mettre en œuvre et très flexible.
• L'inconvénient c'est qu'il y a beaucoup de raisons pour lesquelles cela peut devenir extrêmement coûteux rapidement.
• Par exemple, dans une DB, je suis actuellement en train de travailler il y a une table de presse avec environ 6000 lignes. Il n'est pas particulièrement large, a un entier cluster PK et, ainsi que d'un compact index unique. Pourtant, un simple COUNT(*) OVER(PARTITION BY 1) as TotalRows résultats en ~12,000 lit. Comparez cela à un simple SELECT COUNT(*) FROM Media -- 12 lit. Wowzers.

Mise à JOUR -- le lit problème que j'ai mentionné, c'est un peu de diversion. Il s'avère, que fenêtré avec les fonctions de l'unité utilisée pour mesurer le lit est une sorte de mixte. Le résultat net est ce qui semble être un nombre imposant de lectures. Vous pouvez en savoir plus sur la question ici : Pourquoi les lectures logiques fenêtré fonctions d'agrégation si haut?

Temp Tables / Table De Variables

• Il ya beaucoup de stratégies que de prendre un ensemble de résultats et d'insérer des clés pertinentes ou des segments de résultats en temp tables / table de variables.
• Pour les petites/moyennes d'ensembles de résultats cela peut donner de grands résultats.
• Ce type de stratégie fonctionne sur pratiquement n'importe quel plate-forme/version de SQL.
• En opérant sur un ensemble de résultats à plusieurs reprises (assez souvent d'une exigence) est également facile.
• L'inconvénient, c'est lorsque l'on travaille avec de grands ensembles de résultats ... l'insertion de quelques millions de lignes dans une table temporaire a un coût.
• Pour aggraver le problème, en un volume élevé de la pression du système sur la base de données TempDB peut être un facteur, et les tables temporaires sont un travail efficace dans la base de données TempDB.

Gaussien Somme / Double Numéro De Ligne

• Cette idée repose sur le sous-ensemble de quelque chose que le mathématicien Gauss compris (comment la somme d'une série de chiffres). Le sous-ensemble est de savoir comment obtenir le nombre de lignes à partir de n'importe quel point dans le tableau.
• À partir d'une série de nombres (Row_Number()) le nombre de lignes de 1 à N est - (N + 1) - 1. Plus d'explications dans les liens.
• La formule semble que ce serait nette de juste N, mais si vous vous en tenez à la formule de choses intéressantes qui se passe, vous pouvez déterminer le nombre de lignes à partir d'une page dans le milieu de la table.
• Le résultat net est que vous n' ROW_Number() OVER(Order by ID) et ROW_Number() OVER(Order by ID DESC) , la somme des deux nombres et soustrayez 1.
• À l'aide de ma table de presse comme un exemple, mon lit a chuté de 12 000 à environ 75.
• Dans une grande page que vous avez fini de répéter les données de très nombreuses fois, mais le décalage dans le lit peut être vaut la peine.
• Je n'ai pas testé ce trop grand nombre de scénarios, de sorte qu'il peut tomber en morceaux dans d'autres scénarios.

Haut (@n) / SET ROWCOUNT

• Ce ne sont pas des stratégies spécifiques per se, mais sont optimisations basées sur ce que nous savons sur l'optimiseur de requête.
• L'utilisation créative de Haut(@n) [haut de page peut être une variable dans SQL 2008] ou SET ROWCOUNT peut réduire votre jeu de travail ...même si vous êtes en tirant un moyen de la page de résultat vous pouvez encore affiner le résultat
• Ces idées de travail en raison de l'optimiseur de requête comportement ...un service pack ou correctif peut modifier le comportement (bien que probablement pas).
• Dans certian instances SET ROWCOUNT peut-être un peu de précision
• Cette stratégie ne prend pas en compte pour le calcul du nombre de lignes, tout à fait de la pagination plus efficace

Donc qu'est ce qu'un développeur pour le faire?

Lire mon homme de bien, de lire. Voici quelques articles que j'ai appuyé sur...

• La Méthode la Plus Efficace pour la Pagination à Travers de Grands Ensembles de résultats
• L'Optimisation De La Pagination Côté Serveur - Partie I
• L'optimisation de la Pagination Côté Serveur - Partie II
• Explication de la Somme de Gauss
• Le retour des Résultats Classés avec Microsoft SQL Server 2005
• ROW_NUMBER() OVER n'est Pas Assez Rapide Avec le Grand Jeu de résultats
• Récupérer les N Premiers Enregistrements à partir d'une Requête SQL
• Côté serveur de Pagination à l'aide de SQL Server 2005
• Pourquoi les lectures logiques fenêtré fonctions d'agrégation si haut?

Espérons que cela aide.

Answer 2

Consultez l'agrégat COUNT (*) lorsqu'il est utilisé avec OVER (PARTITON BY ..), comme ceci:

     SELECT
     ROW_NUMBER() OVER(ORDER BY object_id, column_id) as RowNum
    , COUNT(*) OVER(PARTITION BY 1) as TotalRows
    , * 
    FROM master.sys.columns
 

C'est à mon humble avis la meilleure façon de le faire sans avoir à faire deux requêtes.

Answer 3

Si count(*) est lent, vous avez vraiment besoin de résoudre cette question d'abord en examinant attentivement votre index et de vous assurer que vos statistiques sont à jour.

Dans mon expérience, il n'y a rien de mieux que de faire deux requêtes distinctes, l'une pour obtenir la page de données, et un pour obtenir le nombre total. À l'aide d'une table temporaire afin d'obtenir le nombre total de comptes est une stratégie perdante comme votre nombre de lignes augmente. E. g., le coût de l'insertion de 10 000 000 millions de lignes dans une table temporaire simplement de les compter évidemment à être excessif.

Answer 4

Je fais cela en mettant l'ensemble de résultats avec le row_number dans une table temporaire, puis en utilisant le @@ rowcount de cela et en utilisant la requête à ce sujet pour retourner la page de données dont j'ai besoin.