Pourquoi mon SQL Server ORDER BY ralentit-il malgré l'indexation de la colonne commandée?

J'ai une requête SQL (généré par LINQ to entities), qui est à peu près comme suit:

SELECT * FROM [mydb].[dbo].[employees]
JOIN [mydb].[dbo].[industry]
  ON jobs.industryId = industry.id
JOIN [mydb].[dbo].[state]
  ON jobs.stateId = state.id
JOIN [mydb].[dbo].[positionType]
  ON jobs.positionTypeId = positionType.id
JOIN [mydb].[dbo].[payPer]
  ON jobs.salaryPerId = payPer.id
JOIN [mydb].[dbo].[country]
  ON jobs.countryId = country.id
WHERE countryName = 'US'
ORDER BY startDatetime

La requête retourne environ 1200 lignes, je ne pense pas une somme énorme. Malheureusement, il prend également ~16 secondes. Sans la COMMANDE PAR, la requête prend <1 seconde.

J'ai utilisé SQL Server Management Studio pour mettre un index sur la startDatetime colonne, et aussi d'un index cluster sur "cityId, industryId, startDatetime, positionTypeId, payPerId, stateId" (c'est à dire toutes les colonnes de "l'emploi" que nous utilisons dans les Jointures et sur la colonne, nous utilisons la COMMANDE PAR sur). J'ai déjà des indices individuels sur chacune des colonnes que nous utilisons dans les Jointures. Malheureusement, il n'a pas fait la requête vite.

J'ai couru un plan d'exécution et a obtenu:

   |--Nested Loops(Inner Join, OUTER REFERENCES:([mydb].[dbo].[jobs].[cityId]))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([mydb].[dbo].[jobs].[stateId]))
       |    |--Nested Loops(Inner Join, OUTER REFERENCES:([mydb].[dbo].[jobs].[industryId]))
       |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([mydb].[dbo].[jobs].[positionTypeId]))
       |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([mydb].[dbo].[jobs].[salaryPerId]))
       |    |    |    |    |--Sort(ORDER BY:([mydb].[dbo].[jobs].[issueDatetime] ASC))
       |    |    |    |    |    |--Hash Match(Inner Join, HASH:([mydb].[dbo].[currency].[id])=([mydb].[dbo].[jobs].[salaryCurrencyId]))
       |    |    |    |    |         |--Index Scan(OBJECT:([mydb].[dbo].[currency].[IX_currency]))
       |    |    |    |    |         |--Nested Loops(Inner Join, WHERE:([mydb].[dbo].[jobs].[countryId]=[mydb].[dbo].[country].[id]))
       |    |    |    |    |              |--Index Seek(OBJECT:([mydb].[dbo].[country].[IX_country]), SEEK:([mydb].[dbo].[country].[countryName]='US') ORDERED FORWARD)
       |    |    |    |    |              |--Clustered Index Scan(OBJECT:([mydb].[dbo].[jobs].[PK_jobs]))
       |    |    |    |    |--Clustered Index Seek(OBJECT:([mydb].[dbo].[payPer].[PK_payPer]), SEEK:([mydb].[dbo].[payPer].[id]=[mydb].[dbo].[jobs].[salaryPerId]) ORDERED FORWARD)
       |    |    |    |--Clustered Index Seek(OBJECT:([mydb].[dbo].[positionType].[PK_positionType]), SEEK:([mydb].[dbo].[positionType].[id]=[mydb].[dbo].[jobs].[positionTypeId]) ORDERED FORWARD)
       |    |    |--Clustered Index Seek(OBJECT:([mydb].[dbo].[industry].[PK_industry]), SEEK:([mydb].[dbo].[industry].[id]=[mydb].[dbo].[jobs].[industryId]) ORDERED FORWARD)
       |    |--Clustered Index Seek(OBJECT:([mydb].[dbo].[state].[PK_state]), SEEK:([mydb].[dbo].[state].[id]=[mydb].[dbo].[jobs].[stateId]) ORDERED FORWARD)
       |--Clustered Index Seek(OBJECT:([mydb].[dbo].[city].[PK_city]), SEEK:([mydb].[dbo].[city].[id]=[mydb].[dbo].[jobs].[cityId]) ORDERED FORWARD)

La ligne importante semble être "|--Tri(ORDER BY:([mydb].[dbo].[travaux].[issueDatetime] ASC))" — sans aucune mention d'un index sur cette colonne.

Pourquoi ma COMMANDE est EN faisant ma requête beaucoup plus lent, et comment puis-je augmenter la vitesse de ma requête?

source d'informationauteur George

10

Si votre requête ne contient pas un ordre d'ici là, il sera de retour les données quelle que soit la complilation il a été trouvé. Il n'y a aucune garantie que les données vont même être retournés dans le même ordre lorsque vous exécutez à nouveau la requête.

Lorsque vous incluez une clause order by, le dabatase a à construire une liste de lignes dans le bon ordre avant de renvoyer les données dans l'ordre. Cela peut prendre beaucoup de traitement supplémentaire qui se traduit par du temps supplémentaire.

Il prend sans doute plus de temps pour trier un grand nombre de colonnes, votre requête peut être de retour. À un certain point, vous serez à court d'espace de mémoire tampon et de la base de données devra commencer l'échange et de perfromance va descendre.

Essayer de retourner moins de colonnes (spécifier les colonnes dont vous avez besoin, au lieu de Sélectionner *) et de voir si la requête s'exécute plus rapidement.
6

Parce que votre requête des projets de toutes les colonnes (*), il a besoin de 5 colonnes pour les conditions de jointure et a un peu sélectif WHERE clause sur ce qui est probablement une colonne de table jointe, il la fait pour frapper les L'Indice De Point De Basculement: l'optimiseur décide qu'il est moins coûteux de numériser l'ensemble du tableau, de le filtrer et de trier ce qu'il serait à la portée de numérisation de l'index, puis de recherche de chaque clé du tableau pour récupérer le besoin d'ajouter des colonnes supplémentaires (le 5 pour les jointures et le reste pour le *).

Un meilleur indice pour couvrir partiellement cette requête pourrait être:
```
CREATE INDEX ... ON .. (countryId, startDatetime);
```
Jeffrey suggestion à faire l'index cluster serait de couvrir la requête de 100% et serait certainement améliorer les performances, mais l'évolution de l'index cluster a beaucoup d'effets secondaires. Je voudrais commencer avec un index non-cluster comme ci-dessus. Sauf s'ils sont nécessaires à d'autres requêtes, vous pouvez supprimer tous les autres index non cluster que vous avez créé, ils n'aideront pas à cette requête.
2

Quel ordre sont les champs de l'index cluster inclus dans? Vous aurez envie de mettre la startDateTime premier champ, pour que le ORDER BY pour le match, ou dans ce cas (countryId, startDateTime) à l'avant, dans l'ordre, puisque vous voulez sélectionner une seule countryId (indirectement, via countryName) et de les ordonner par startDateTime.
1

News flash: l'Indexation d'une colonne n'a pas aider à faire le tri plus rapide.

Si vous voulez faire de votre requête BEAUCOUP plus vite inverser l'ordre de vos tables. Plus précisément, la table de la liste de country d'abord dans vos tables jointes. La raison? La clause where peut filtrer les lignes de la première table au lieu d'avoir à faire toutes ces jointures, puis de filtrage les lignes.

Vous devriez essayer de code ci-dessous également

Insérer des enregistrements dans table temporaire Sans l'aide de la clause Order by

SELECT * into #temp FROM [mydb].[dbo].[employees]
JOIN [mydb].[dbo].[industry]
  ON jobs.industryId = industry.id
JOIN [mydb].[dbo].[state]
  ON jobs.stateId = state.id
JOIN [mydb].[dbo].[positionType]
  ON jobs.positionTypeId = positionType.id
JOIN [mydb].[dbo].[payPer]
  ON jobs.salaryPerId = payPer.id
JOIN [mydb].[dbo].[country]
  ON jobs.countryId = country.id
WHERE countryName = 'US'

Maintenant, exécutez l'instruction à l'aide de la Clause Order By

Select * from #temp ORDER BY startDatetime

Vous devez vous connecter pour publier un commentaire.