Pourquoi il y a une ÉNORME différence de performance entre la table temporaire et sous-sélection

C'est une question à propos de SQL Server 2008 R2

Je ne suis pas administrateur de base de données, et de loin. Je suis un développeur java, qui a écrire de SQL, de temps à autre. (pour la plupart intégrées dans le code). Je veux savoir si j'ai fait quelque chose de mal ici, et si oui, ce que je peux faire pour éviter qu'il se produise de nouveau.

Q1: 

SELECT something FROM (SELECT * FROM T1 WHERE condition1) JOIN ...

T1 dispose de 14 rejoint

T2 est le même que T1, avec une exception. (SELECT * from T1 OÙ condition1) est exécutée avant, et stockée dans une table temp.

Ce n'est pas une sous-requête corrélée.

T2:

SELECT * INTO #tempTable FROM T1 WHERE condition1
SELECT something FROM #tempTable  JOIN ...

de nouveau, 14 jointures.

La chose qui m'intrigue, c'est maintenant que T1 a pris > 2min, (il a essayé quelques fois, pour éviter la mise en cache pour jouer un rôle), tandis que T2 (les deux requêtes combinées) prend 2sec!!!! Ce qui donne?

  • J'imagine que le nombre estimé de lignes pour SELECT * FROM T1 WHERE condition1 sont très imprécise. La matérialisation dans un #tempTable signifie que SQL Server ne sait exactement combien de lignes vous sera retourné. Vous pouvez poster la version XML de réels plans d'exécution?
InformationsquelleAutor Ward | 2013-05-27
  1. 48

    Pourquoi il n'est pas recommandé d'utiliser des sous-requêtes?

    Optimiseur de base de données (indépendamment de la base de données que vous utilisez) ne peut pas toujours correctement optimiser une telle requête (avec les sous-requêtes). Dans ce cas, le problème de l'optimiseur est de choisir la bonne façon de rejoindre les ensembles de résultats. Il existe plusieurs algorithmes pour la liaison de deux ensembles de résultats. Le choix de l'algorithme dépend du nombre d'enregistrements qui sont contenues dans l'un et dans l'autre ensemble de résultats. Dans le cas où si vous jointure de deux tables (sous-requête n'est pas une table physique), la base de données peut facilement déterminer la quantité de données dans les deux ensembles de résultats par les statistiques disponibles. Si l'un des ensembles de résultats est une sous-requête, puis de comprendre comment de nombreux enregistrements qu'il renvoie est très difficile. Dans ce cas, la base de données peut choisir le mauvais plan de requête de jointure, donc, qui va conduire à une réduction spectaculaire de la performance de la requête.

    La réécriture de la requête avec l'utilisation de tables temporaires sont destinés à simplifier l'optimiseur de base de données. Dans la requête réécrite tous les jeux de résultats participant à des jointures être physique des tables et de la base de données permet de déterminer facilement la longueur de chaque ensemble de résultats. Cela permettra à la base de données pour choisir la garantie la plus rapide de tous les plans de requête possibles. En outre, la base de données permettra de faire le bon choix, peu importe quelles sont les conditions. La requête réécrite avec des tables temporaires serait bien travailler sur une base de données, ce qui est particulièrement important dans le développement de solutions portables. En outre, la requête réécrite est plus facile à lire, facile à comprendre et à déboguer.

    Il est entendu que la réécriture de la requête avec les tables temporaires peuvent conduire à un certain ralentissement en raison des dépenses supplémentaires: création de tables temporaires. Si la base de données ne seront pas confondre avec le choix du plan de requête, il va effectuer la vieille requête plus vite que un nouveau. Toutefois, ce ralentissement sera toujours négligeable. Généralement, la création d'une table temporaire prend que quelques millisecondes. Qui est, le retard ne peut pas avoir un impact significatif sur les performances du système, et peuvent généralement être ignorées.

    Important! Ne pas oublier de créer des index pour les tables temporaires. Les champs d'index doit inclure tous les champs qui sont utilisés dans des conditions de jointure.

    • Moteur de requête SQL Server crée en interne les tables temporaires et la raison pour laquelle vous fournis ci-dessus n'est pas toujours vrai.IL dépend de plusieurs autres facteurs comme les Indexes,la Fragmentation,Statastics etc.
    • la création d'index sur les tables temporaires augmente les performances de la requête.
    • Vous réponse est tout à fait erronée et trompeuse, la création d'une table temporaire ne doit être envisagée dans certains cas: stackoverflow.com/questions/42772428/...
    • Ce n'est pas "mauvais", bien que je suis en désaccord avec le conseil. Si vous lisez attentivement, à la fois Karthik et je recommande l'utilisation des index sur les tables temporaires pour améliorer les performances. Dans mon expérience, le problème est presque toujours jointures de boucles imbriquées, et elles peuvent être évitées avec des indicateurs de requête. Je trouve que les indicateurs de requête sont beaucoup plus faciles à entretenir que beaucoup de tables temporaires.
    • La conclusion de la première et deuxième paragraphe, c'est que les sous-requêtes doivent être réécrites avec des tables temporaires parce que: "la base de données peut facilement déterminer la quantité de données dans les deux ensembles de résultats par les statistiques disponibles", ce qui est une hypothèse erronée et peut conduire à des conclusions erronées.
    InformationsquelleAutor Karthik AMR
  2. 9

    Il y a beaucoup de choses à aborder ici, les index, les plans d'exécution, etc. Les tests et la comparaison des résultats est le chemin à parcourir.

    Vous pouvez prendre un coup d'oeil à la usual suspects, des indices. Jetez un oeil dans le plan d'exécution et de les comparer. Assurez-vous que le WHERE clause est à l'aide de celles qui sont correctes. Assurez-vous d'utiliser l'index sur votre JOINs.

    Ces réponses est sûr, vous aider beaucoup.

    InformationsquelleAutor Yaroslav