La différence de performances massives SQL avec SELECT TOP x même lorsque x est beaucoup plus élevé que les lignes sélectionnées

Je suis la sélection des lignes à partir d'une fonction à valeur de table, mais ont trouvé un inexplicable énorme différence de performance en mettant SÉLECTIONNEZ HAUT dans la requête.

SELECT   col1, col2, col3 etc
FROM     dbo.some_table_function
WHERE    col1 = @parameter
--ORDER BY col1

est de prendre plus de 5 ou 6 minutes pour terminer.

Cependant

SELECT   TOP 6000 col1, col2, col3 etc
FROM     dbo.some_table_function
WHERE    col1 = @parameter
--ORDER BY col1

dure environ 4 ou 5 secondes.

Cela ne m'étonne pas si le retour de l'ensemble de ces données a été énorme, mais la requête spécifique impliqué retourne ~5000 lignes de 200 000 habitants.

Donc dans les deux cas, l'ensemble de la table est traitée, comme SQL Server continue jusqu'à la fin, à la recherche de 6000 lignes qui il n'y arriverez jamais. Pourquoi l'énorme différence alors? Est-ce quelque chose à voir avec la façon dont SQL Server alloue de l'espace dans l'anticipation du résultat de la taille de l'ensemble (HAUT 6000 ce qui lui confère un faible exigence qui est plus facilement affectés dans la mémoire)?
Quelqu'un d'autre a vu quelque chose comme cela?

Grâce

source d'informationauteur Ray | 2009-09-08

  1. 6

    Fonctions à valeur de Table peut avoir une non-linéaire du temps d'exécution.

    Considérons la fonction équivalente pour cette requête:

    SELECT  (
        SELECT  SUM(mi.value)
        FROM    mytable mi
        WHERE   mi.id <= mo.id
        )
FROM    mytable mo
ORDER BY
        mo.value

    Cette requête qui calcule la SUM) est rapide au début et à la lenteur, à la fin, puisque, sur chaque ligne de mo il doit la somme de toutes les valeurs précédentes qui faut rembobiner le contenu (rowsource).

    Temps mis pour calculer SUM pour chaque ligne augmente à mesure que les numéros de ligne augmenter.

    Si vous faites mytable assez grand (par exemple, 100,000 des lignes, comme dans votre exemple) et exécutez cette requête, vous verrez que cela prend beaucoup de temps.

    Toutefois, si vous appliquez TOP 5000 à cette requête, vous verrez que cela se termine bien plus vite que 1/20 du temps requis pour la table entière.

    Plus probablement, quelque chose de semblable se passe dans votre cas aussi.

    - À-dire quelque chose de plus certainement, j'ai besoin de voir la définition de la fonction.

    Mise à jour:

    SQL Server peut pousser des prédicats dans la fonction.

    Par exemple, je viens de créer ce TVF:

    CREATE FUNCTION fn_test()
RETURNS TABLE
AS
RETURN  (
        SELECT  *
        FROM    master
        );

    Ces requêtes:

    SELECT  *
FROM    fn_test()
WHERE   name = @name

SELECT  TOP 1000 *
FROM    fn_test()
WHERE   name = @name

    de rendement des différents plans d'exécution (la première utilise l'analyse en cluster, le second utilise un index de recherche avec un TOP)

  2. 3

    Votre HAUT n'a pas de COMMANDE, il est donc tout simplement la même que ROWCOUNT 6000 première. Une COMMANDE PAR exigerait que toutes les lignes à être évaluées en premier, et il faudrait beaucoup plus de temps.

    Si dbo.some_table_function est un inline table udf, alors que c'est simplement une macro qui est agrandie de façon à ce qu'il retourne la première 6000 lignes comme mentionné dans aucun ordre particulier.

    Si l'udf est multi évalués, c'est une boîte noire, et sera toujours tirer dans l'ensemble des données avant de filtrer. Je ne pense pas que ce qui se passe.

    Ne sont pas directement liés, mais un autre DONC, la question sur les Tvf

  3. 2

    J'ai eu le même problème, une simple requête de rejoindre cinq tables de retour de 1000 lignes a fallu deux minutes pour terminer. Quand j'ai ajouté "TOP 10000", c'terminé en moins d'une seconde. Il s'est avéré que l'index cluster sur l'une des tables a été fortement fragmenté.

    Après la reconstruction de l'index de la requête complète en moins d'une seconde.

  4. 1

    Vous exécutez peut-être dans quelque chose d'aussi simple que la mise en cache ici - peut-être, pour quelque raison que ce soit), le "TOP" de la requête est mise en cache? À l'aide d'un indice que l'autre ne l'est pas?

    En tout cas le meilleur moyen pour étancher votre curiosité est d'examiner la totalité du plan d'exécution pour les deux requêtes. Vous pouvez faire de ce droit dans SQL Management Console et je vais vous dire EXACTEMENT ce que les opérations sont terminées et pour combien de temps est prévu pour prendre.

    Toutes les implémentations de SQL sont bizarres dans leur propre mode de SQL Server se fait pas exception. Ce genre de "whaaaaaa?!" les moments sont assez communs. ;^)

  5. 1

    Ce n'est pas nécessairement vrai que l'ensemble de la table est traitée, si col1 est un indice.

    SQL optimisation de choisir si ou de ne pas utiliser un index. Peut-être que votre "TOP" est en l'obligeant à utiliser l'index.

    Si vous utilisez le service MSSQL l'Analyseur de Requêtes (Le nom m'échappe), appuyez sur Ctrl-k. Cela permettra d'afficher le plan d'exécution de la requête au lieu de l'exécuter. Passez la souris sur les icônes pour voir les IO/utilisation de l'UC, je crois.

    Je parie que l'un est à l'aide d'un index de recherche, tandis que l'autre ne l'est pas.

    Si vous avez un client générique:
    SHOWPLAN_ALL;
    ALLER
    sélectionnez ...;
    aller

    voir http://msdn.microsoft.com/en-us/library/ms187735.aspx pour plus de détails.

  6. 1

    Je pense que Quassnois suggestion semble très plausible. Par l'ajout de HAUT 6000 vous sont implicitement donner l'optimiseur une indication qu'une assez faible partie des 200 000 lignes vont être retournées. L'optimiseur utilise ensuite un index de recherche au lieu d'un index cluster d'analyse ou analyse de la table.

    Une autre explication possible pourrait la mise en cache, comme Jim davis suggère. C'est assez facile de la règle par l'exécution de requêtes de nouveau. Essayez d'exécuter l'une avec TOP 6000 première.