SQL pourquoi est-SELECT COUNT(*) , MIN(col), MAX(col), plus rapide, puis SÉLECTIONNEZ MIN(col), MAX(col)

Nous voyons une énorme différence entre ces requêtes.

La requête lente

SELECT MIN(col) AS Firstdate, MAX(col) AS Lastdate 
FROM table WHERE status = 'OK' AND fk = 4193

La Table 'table'. Nombre de Scan 2, lectures logiques 2458969, lectures physiques 0, lectures anticipées 0, lectures logiques lob 0, lectures physiques lob 0, lectures anticipées lob 0.

D'Exécution de SQL Server Fois: temps CPU = 1966 ms, temps écoulé = 1955 ms.

Le rapide de la requête

SELECT count(*), MIN(col) AS Firstdate, MAX(col) AS Lastdate 
FROM table WHERE status = 'OK' AND fk = 4193

La Table 'table'. Analyse nombre 1, lectures logiques 5803, lectures physiques 0, lectures anticipées 0, lectures logiques lob 0, lectures physiques lob 0, lectures anticipées lob 0.

D'Exécution de SQL Server Fois: temps CPU = 0 ms, le temps écoulé = 9 ms.

Question

Quelle est la raison, entre l'énorme différence de performances entre les requêtes?

Mise à jour
Une petite mise à jour basé sur des questions étant donné que les commentaires:

L'ordre de l'exécution ou de l'exécution répétée ne change en rien les performances de sage.
Il n'y a pas d'autres paramètres utilisés et le (test)de la base de données n'est pas faire autre chose pendant l'exécution.

Requête lente

|--Nested Loops(Inner Join)
 |--Stream Aggregate(DEFINE:([Expr1003]=MIN([DBTest].[dbo].[table].[startdate])))
   |    |--Top(TOP EXPRESSION:((1)))
   |         |--Nested Loops(Inner Join, OUTER REFERENCES:([DBTest].[dbo].[table].[id], [Expr1008]) WITH ORDERED PREFETCH)
   |              |--Index Scan(OBJECT:([DBTest].[dbo].[table].[startdate]), ORDERED FORWARD)
   |              |--Clustered Index Seek(OBJECT:([DBTest].[dbo].[table].[PK_table]), SEEK:([DBTest].[dbo].[table].[id]=[DBTest].[dbo].[table].[id]),  WHERE:([DBTest].[dbo].[table].[FK]=(5806) AND [DBTest].[dbo].[table].[status]<>'A') LOOKUP ORDERED FORWARD)
   |--Stream Aggregate(DEFINE:([Expr1004]=MAX([DBTest].[dbo].[table].[startdate])))
        |--Top(TOP EXPRESSION:((1)))
             |--Nested Loops(Inner Join, OUTER REFERENCES:([DBTest].[dbo].[table].[id], [Expr1009]) WITH ORDERED PREFETCH)
                  |--Index Scan(OBJECT:([DBTest].[dbo].[table].[startdate]), ORDERED BACKWARD)
                  |--Clustered Index Seek(OBJECT:([DBTest].[dbo].[table].[PK_table]), SEEK:([DBTest].[dbo].[table].[id]=[DBTest].[dbo].[table].[id]),  WHERE:([DBTest].[dbo].[table].[FK]=(5806) AND [DBTest].[dbo].[table].[status]<>'A') LOOKUP ORDERED FORWARD)

Rapide de la requête

 |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1012],0)))
   |--Stream Aggregate(DEFINE:([Expr1012]=Count(*), [Expr1004]=MIN([DBTest].[dbo].[table].[startdate]), [Expr1005]=MAX([DBTest].[dbo].[table].[startdate])))
        |--Nested Loops(Inner Join, OUTER REFERENCES:([DBTest].[dbo].[table].[id], [Expr1011]) WITH UNORDERED PREFETCH)
             |--Index Seek(OBJECT:([DBTest].[dbo].[table].[FK]), SEEK:([DBTest].[dbo].[table].[FK]=(5806)) ORDERED FORWARD)
             |--Clustered Index Seek(OBJECT:([DBTest].[dbo].[table].[PK_table]), SEEK:([DBTest].[dbo].[table].[id]=[DBTest].[dbo].[table].[id]),  WHERE:([DBTest].[dbo].[table].[status]<'A' OR [DBTest].[dbo].[table].[status]>'A') LOOKUP ORDERED FORWARD)

SQL pourquoi est-SELECT COUNT(*) , MIN(col), MAX(col), plus rapide, puis SÉLECTIONNEZ MIN(col), MAX(col)

Réponse

La réponse donnée ci-dessous par Martin Smith semble expliquer le problème. La super version courte est que le MS-SQL de la requête-analyser, à tort, utilise un plan de requête dans la requête lente qui provoque une complète analyse de la table.

L'ajout d'un Count(*), l'indicateur de requête avec(FORCESCAN) ou un indice combiné sur la date de début,FK et le statut des colonnes résout le problème de performances.

si vous exécutez le 1er requête après la 2ème requête de nouveau?
Peut-être parce que lorsque vous utilisez un count(*) vous n'avez pas de vérifier tous les dossiers pour fk=4193?
Êtes-vous de l'exécution de ces l'un après l'autre? Si oui: qu'advient-il si vous mettez DBCC DROPCLEANBUFFERS et DBCC FREEPROCCACHE avant que les deux requêtes? Qu'advient-il si vous changez la séquence - exécuter la requête fast en premier, puis le lent?
Smith: je pensais à 1. Une mise à jour des stats 2. Reproducability
Votre plan ne correspond pas à la requête. -1 pour la trompeuse nous.
Gbn, pouvez-vous expliquer de quoi de différent? Les deux requêtes sont juste lavés et débarrassés de

InformationsquelleAutor CodingBarfield | 2011-09-20

25

Le Serveur SQL de la cardinalité de l'estimateur de formuler diverses hypothèses de la modélisation comme
- Indépendance: les Données des distributions sur les différentes colonnes sont indépendantes, à moins que les informations de corrélation est disponible.
- Uniformité: au Sein de chaque objet de statistiques de l'histogramme de l'étape, les valeurs distinctes sont répartis de façon uniforme et chaque valeur est la même fréquence.
Source
Il y a 810,064 lignes dans la table.

Vous avez la requête
```
SELECT COUNT(*),
       MIN(startdate) AS Firstdate,
       MAX(startdate) AS Lastdate
FROM   table
WHERE  status <> 'A'
       AND fk = 4193 
```
1,893 (0.23%) lignes répondent à la fk = 4193 prédicat, et de ces deux échouer le status <> 'A' partie de manière globale 1,891 match et doivent être agrégées.

Vous avez également deux indices ni de qui couvrent la totalité de la requête.

Pour votre rapide requête qu'il utilise un index sur fk directement trouver les lignes où fk = 4193 doit ensuite faire 1,893 les recherches parmi de trouver chaque ligne dans l'index cluster de vérifier la status prédicat et de récupérer les startdate pour l'agrégation.

Lorsque vous retirez le COUNT(*) de la SELECT liste de SQL Server n'est plus a pour traiter chaque ligne éligible. En conséquence, il considère une autre option.

Vous avez un index sur startdate de sorte qu'il pourrait commencer la numérisation à partir du début, en faisant les recherches parmi à la table de base et dès qu'il trouve la première ligne correspondante arrêter car il a trouvé le MIN(startdate), de Même la MAX peut être trouvé avec un autre scan de départ à l'autre bout de l'index et de travailler vers l'arrière.

SQL Server estime que chacun de ces scans en fin de traitement 590 lignes avant de toucher à un qui correspond au prédicat. Donner 1,180 total des recherches vs 1,893 il choisit donc de ce plan.

590 figure est juste table_size /estimated_number_of_rows_that_match. c'est à dire la cardinalité de l'estimateur suppose que les lignes correspondantes seront distribués de façon régulière tout au long de la table.

Malheureusement, le 1,891 les lignes qui satisfont le prédicat sont pas distribués de façon aléatoire à l'égard de startdate. En fait, ils sont tous regroupés dans un seul 8,205 ligne segment vers la fin de l'index, ce qui signifie que l'analyse de façon à obtenir à la MIN(startdate) finit par faire 801,859 clé des recherches avant de pouvoir l'arrêter.

Cela peut être reproduite ci-dessous.
```
CREATE TABLE T
(
id int identity(1,1) primary key,
startdate datetime,
fk int,
[status] char(1),
Filler char(2000)
)

CREATE NONCLUSTERED INDEX ix ON T(startdate)

INSERT INTO T
SELECT TOP 810064 Getdate() - 1,
                  4192,
                  'B',
                  ''
FROM   sys.all_columns c1,
       sys.all_columns c2  


UPDATE T 
SET fk = 4193, startdate = GETDATE()
WHERE id BETWEEN 801859 and 803748 or id = 810064

UPDATE T 
SET  startdate = GETDATE() + 1
WHERE id > 810064


/*Both queries give the same plan. 
UPDATE STATISTICS T WITH FULLSCAN
makes no difference*/

SELECT MIN(startdate) AS Firstdate, 
       MAX(startdate) AS Lastdate 
FROM T
WHERE status <> 'A' AND fk = 4192


SELECT MIN(startdate) AS Firstdate, 
       MAX(startdate) AS Lastdate 
FROM T
WHERE status <> 'A' AND fk = 4193
```
Vous pourriez envisager d'utiliser des indicateurs de requête pour forcer le régime à utiliser l'index sur fk plutôt que startdate ou ajouter le suggère index manquant mis en évidence dans le plan d'exécution sur (fk,status) INCLUDE (startdate) pour éviter ce problème.
- Déposer la colonne statut de la requête ralentit les deux requêtes de quelques pour cent.
- Créer automatiquement des Statistiques/mise à Jour Automatique des Statistiques est Vrai
- Plan d'exécution XML pastebin.com/mBcgHYkN Nous vérifions maintenant si il pourrait avoir été un non-exécution du plan de maintenance.
- De ré-exécuter le plan de maintenance n'a pas améliorer les performances. Ajout de l'indicateur de requête avec (FORCESEEK) fixe le problème de performances. Je vais l'accepter comme une grande réponse, le query analyser clairement deviné, à tort, la façon d'exécuter cette requête.
- Oui, le problème n'est pas à faire avec la qualité des statistiques. La repro dans ma réponse arrive, même quand ils sont mis à jour avec FULLSCAN. Le problème est que SQL Server ne dispose actuellement d'aucune logique pour détecter la corrélation entre startdate et fk
InformationsquelleAutor Martin Smith

Vous devez vous connecter pour publier un commentaire.