Aide avec ORDERING ROW_NUMBER OVER the Count in Descending Order ?

Question

J'utilise la fonction ROW_NUMBER() introduite dans SQL SERVER 2005 pour renvoyer un ensemble de résultats paginés. La requête fonctionne comme prévu, mais j'ai un problème. Ce que j'aimerais faire, c'est renvoyer les résultats classés par ordre décroissant. Voici la requête et ci-dessous une petite description :

DECLARE @StartRowIndex INT
DECLARE @MaximumRows INT

SET @StartRowIndex = 1
SET @MaximumRows = 10

;WITH Data AS (SELECT
        ROW_NUMBER() OVER (ORDER BY a.column1) as RowNum,
        a.column1, a.column2, dbo.f_GetDataCount(a.column3) as cnt
        FROM dbo.table1 a
        INNER JOIN dbo.table2 b on b.column4 = a.column4
        INNER JOIN dbo.table3 c on c.column5 = a.column5
        LEFT OUTER JOIN dbo.table4 d on d.column6 = a.column6
        WHERE 1=1  AND a.column7 IN (1)
)

SELECT RowNum,column1,
   column2,cnt
FROM
Data
WHERE RowNum BETWEEN @StartRowIndex AND (@StartRowIndex + @MaximumRows) - 1
ORDER BY cnt desc

1. Je sais que le nombre le plus élevé se situe aux alentours de 100 000.
2. ORDER By cnt desc ordonne les résultats par leur nombre dans l'ordre décroissant, mais seulement pour les 10 enregistrements qu'il récupère. Je sais que c'est parce que RowNum est ordonné par a.Column1 qui n'est pas le compte. Idéalement, j'aimerais ordonner RowNum par le compte, quelque chose comme ceci :

ROW_NUMBER() OVER (ORDER BY Count(*)) as RowNum

Le programme ci-dessus s'exécute, mais il prend une éternité (plus de 17 minutes).

Par ailleurs, dbo.f_GetDataCount(a.column3) as cnt renvoie le nombre total d'enregistrements basé sur a.column3, j'ai donc essayé :

ROW_NUMBER() OVER (ORDER BY dbo.f_GetDataCount(a.column3) as RowNum, mais cela a également pris une éternité.

Si je suis en train d'embrouiller les choses plus que de raison, je vais donner un petit exemple. Si je fixe l'indice StartRowIndex à 1, le système renverra 10 enregistrements et le premier enregistrement contiendra 10 000.

Si je fixe ensuite StartRowIndex à 11, les 10 enregistrements suivants seront renvoyés et le premier enregistrement contiendra 15 000.

Ce qu'il devrait en fait renvoyer, c'est l'enregistrement avec 15 000 en premier, quel que soit l'indice StartRowIndex.

L'aide est très appréciée.

Voici le code de la fonction :

CREATE FUNCTION [dbo].[f_GetDataCount] 
(
-- Add the parameters for the function here
@column3 nvarchar(10)
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @Result int

-- Add the T-SQL statements to compute the return value here
SELECT @Result = COUNT(a.column3) FROM dbo.table1 a
where a.column3 = @column3

-- Return the result of the function
RETURN @Result

END

Answer 1

Merci d'avoir posté le code UDF - essayez-le plutôt que d'utiliser un UDF scalaire en ligne. Cela devrait être beaucoup plus rapide que d'ordonner par le ROWNUBMER(OVER dbo.fn_GetDataCount()).

Si vous avez toujours des performances sous-optimales, il est probable que l'indexation entre en jeu, ou vous pourriez envisager de matérialiser le nombre de valeurs de la colonne3 de façon intermédiaire (nous pouvons discuter des options ici si le problème doit encore être abordé, il suffit de le renvoyer).

EDIT : Whoops, j'ai ajouté le "desc" à l'ordre par dans la clause over puisque vous voulez qu'ils soient décroissants - aussi, j'ai modifié les scalaires un peu...

DECLARE @StartRowIndex INT
DECLARE @MaximumRows INT
DECLARE @EndRowIndex INT

SELECT @StartRowIndex = 1, @MaximumRows = 10
SELECT @EndRowIndex = (@StartRowIndex + @MaximumRows) - 1

;WITH Data1 as (
    SELECT  a.column3 as c3, count(*) as frequency
    from    dbo.table1 a
    group by a.column3
),
Data AS (SELECT
        ROW_NUMBER() OVER (ORDER BY coalesce(d.frequency,0) desc) as RowNum,
        a.column1, a.column2, d.frequency as cnt
        FROM dbo.table1 a
        INNER JOIN dbo.table2 b on b.column4 = a.column4
        INNER JOIN dbo.table3 c on c.column5 = a.column5
        LEFT OUTER JOIN dbo.table4 d on d.column6 = a.column6
        LEFT OUTER JOIN Data1 d
        on a.column3 = d.c3
        WHERE 1=1  AND a.column7 IN (1)
)
SELECT RowNum,column1,
   column2,cnt
FROM
Data
WHERE RowNum BETWEEN @StartRowIndex AND @EndRowIndex
ORDER BY cnt desc