SQL Sub-query ou INNER-JOIN?
j'ai les deux requêtes suivantes:
declare @UserId as int
set @UserId = 1
-- Query #1: Sub-query
SELECT
u.[Id] ,
u.[Name] ,
u.[OrgId] AS Organization,
(SELECT o.[Name] FROM Org o WHERE o.Id = u.OrgId) As OrganizationName,
[UserRoleId] AS UserRole,
[UserCode] AS UserCode,
[EmailAddress] As EmailAddress,
(SELECT SearchExpression FROM SearchCriteria WHERE UserId = @UserId AND IsDefault=1 ) AS SearchCriteria,
(SELECT PageSize FROM UserPreferences WHERE UserId = @UserId) AS UserPreferencePageSize,
(SELECT DrilldownPageSize FROM UserPreferences WHERE UserId = @UserId) AS UserPreferenceDrilldownPageSize
FROM [User] as u
WHERE u.Id = @UserId
-- Query #2: LEFT OUTER JOIN-query
SELECT
u.[Id] ,
u.[Name] ,
u.[OrgId] AS Organization,
(SELECT o.[Name] FROM Org o WHERE o.Id = u.OrgId) As OrganizationName,
[UserRoleId] AS UserRole,
[UserCode] AS UserCode,
[EmailAddress] As EmailAddress,
sc.SearchExpression As SearchExpression,
up.PageSize As PageSize,
up.DrilldownPageSize As DrilldownPageSize
FROM [User] as u
LEFT OUTER JOIN [UserPreferences] as up ON u.id = up.UserId
LEFT OUTER JOIN [SearchCriteria] as sc ON u.id = sc.UserId
WHERE ISNULL(sc.IsDefault,1)=1 AND u.Id = @UserId
statistiques du Plan D'exécution des requêtes: (coût des requêtes par lot)
- Requête#1 (Sous-Requête) : 56% De
- Query#2 (JOIN): 44%
je thot la sous-requête serait optimale en raison de la sous-requête sera exécutée après la OÙ le filtre est appliqué. Les statistiques disent que la requête#2-JOIN approach est mieux.
suggère Pls. Aussi comme un utilisateur de SQL-Server modéré Comment puis-je dériver quelle requête est meilleure (autre chose que l'exécution-plan, si elle est plus utile)
Merci.
4 réponses
jointure est plus rapide que subquery.
subquery rend pour l'accès occupé de disque, pensez à l'aiguille de de disque dur de lecture-écriture (tête?) qui va et vient quand il accès: l'Utilisateur, SearchExpression, PageSize, DrilldownPageSize, Utilisateur, SearchExpression, PageSize, DrilldownPageSize, de l'Utilisateur... et ainsi de suite.
jointure fonctionne par concentration l'opération sur le résultat des deux premières tables, tout les jointures se concentreraient sur le résultat en mémoire (ou caché sur le disque) des premières tables jointes, et ainsi de suite. moins de mouvement d'aiguille en lecture-écriture, donc plus rapide
la meilleure chose que vous pouvez faire est d'essayer les deux et comparer ce qui vous donne la meilleure performance. Il est difficile de deviner ce que l'optimiseur de requête va faire (vous pourriez écrire 2 requêtes différentes qui finissent par être optimisées pour le même plan d'exécution).
pour comparer les performances équitablement, vous devriez vous assurer de les essayer sur un pied d'égalité en nettoyant le plan d'exécution et le cache de données avant d'essayer chacun. Ceci peut être fait en utilisant le suivant commandes, mais seulement sur un serveur de développement / test db:
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
l'approche que je prends habituellement est d'exécuter chaque requête 3 fois, avec le profileur SQL tournant de sorte que je puisse surveiller la durée, lit, CPU et écrit de la requête sur laquelle je base alors ma décision.
p.ex.
1) effacer le cache en utilisant les commandes ci-dessus
2) exécuter la requête et enregistrer des statistiques
3) effacer le cache
4) exécuter à nouveau query
5) exécuter la requête à nouveau (ceci utilisera le plan d'exécution en cache/les données)
puis répéter pour la deuxième requête à comparer.
il dépendra en grande partie de la cardinalité de vos données: si vos recherches en ligne sont minimales par rapport à la tête de join ing de grandes quantités de données (lorsque vous avez seulement besoin d'extraire une petite sous-section de ce résultat de jointure), alors l'option en ligne sera plus rapide. Mais si vous avez des frais généraux substantiels avec des sélections en ligne (c.-à-d. si votre résultat a un grand nombre de lignes, et vous appelez un select en ligne pour chaque ligne), alors la jointure sera plus rapide.
Je ne vois pas à partir de votre question les nombres impliqués (c.-à-d. combien de lignes) il est donc difficile de faire des commentaires qualitatifs.
par exemple, si votre ensemble de résultats a 10 lignes, alors les sélections en ligne seront effectuées pour chacune de ces dix lignes seulement, alors que la jointure pourrait impliquer beaucoup plus de lignes, qui sont ensuite sélectivement réduites par des clauses où. Mais si vous avez un ensemble de résultats de 10 millions de lignes, les sélections en ligne tueront probablement la performance, depuis sa ligne par ligne.
exemple : imaginez que vous ayez à collecter une charge de briques (spécifiée par taille, etc.) de partout dans la Cour d'un bâtiment et les peindre en bleu.
sélectionner = sélectionner toutes les briques dont vous avez besoin, puis les peindre à la main.
joindre = jeter toutes les briques dans un énorme seau de peinture, anf puis choisir ceux dont vous avez besoin
si vous voulez seulement finir avec 10 briques, il est beaucoup plus rapide de choisir et puis peindre à la main. Si vous voulez un million de briques, les peindre en masse dans une baignoire est la solution.
le coût relatif d'un plan d'exécution n'est pas toujours un indicateur fiable du rendement.
j'assume de votre sql que seulement 1 ligne devrait être rendue. Dans la mesure où L'UserId est une clé unique sur L'utilisateur, la performance de vos 2 approches sera similaire sur la plupart des bases de données relationnelles.
Choses à garder à l'esprit serait:
- si les références utilisatrices ou les critères de recherche renvoient plus d'une rangée, la première approche va soulever une erreur sql, la seconde approche va retourner plus d'une rangée.
- la recherche supplémentaire apparente dans la première approche (références utilisatrices sélectionnées deux fois) n'a pas d'effet réel parce que pour la Seconde recherche, l'enregistrement sera déjà dans un tampon
- si pour une raison quelconque la table D'utilisateur est scannée tablespace, la première approche sera beaucoup plus rapide