SQL “AVEC” de la Performance et de la Table temporaire (possible “Indicateur de Requête” pour simplifier)
Donné l'exemple des requêtes ci-dessous (Simplifié exemples seulement)
DECLARE @DT int; SET @DT=20110717; -- yes this is an INT
WITH LargeData AS (
SELECT * -- This is a MASSIVE table indexed on dt field
FROM mydata
WHERE dt=@DT
), Ordered AS (
SELECT TOP 10 *
, ROW_NUMBER() OVER (ORDER BY valuefield DESC) AS Rank_Number
FROM LargeData
)
SELECT * FROM Ordered
et ...
DECLARE @DT int; SET @DT=20110717;
BEGIN TRY DROP TABLE #LargeData END TRY BEGIN CATCH END CATCH; -- dump any possible table.
SELECT * -- This is a MASSIVE table indexed on dt field
INTO #LargeData -- put smaller results into temp
FROM mydata
WHERE dt=@DT;
WITH Ordered AS (
SELECT TOP 10 *
, ROW_NUMBER() OVER (ORDER BY valuefield DESC) AS Rank_Number
FROM #LargeData
)
SELECT * FROM Ordered
La fois de produire les mêmes résultats, ce qui est un bien limité et une liste de classement des valeurs à partir d'une liste basée sur un champs de données.
Lorsque ces requêtes obtenir beaucoup plus compliqué (beaucoup plus de tables, beaucoup de critères, plusieurs niveaux de "avec" table alaises, etc...) le fond de la requête s'exécute BEAUCOUP plus rapidement que la première. Parfois, dans l'ordre de 20x-100x plus rapide.
La Question est...
Est-il une sorte d'INDICATEUR de requête ou d'autres option SQL qui serait de dire le Serveur SQL server pour effectuer le même type d'optimisation automatique, ou d'autres formats de ce qui impliquerait un nettoyeur de l'approche (en essayant de garder le format un peu comme de la requête 1 possible) ?
Noter que le "Classement" ou secondaire requêtes est bouffer pour cet exemple, la réalité des opérations effectuées ne sont pas vraiment trop d'importance.
C'est le genre de ce que j'espérais (ou similaire mais l'idée est claire, je l'espère). Rappelez-vous cette requête ci-dessous n'est pas réellement le travail.
DECLARE @DT int; SET @DT=20110717;
WITH LargeData AS (
SELECT * -- This is a MASSIVE table indexed on dt field
FROM mydata
WHERE dt=@DT
**OPTION (USE_TEMP_OR_HARDENED_OR_SOMETHING) -- EXAMPLE ONLY**
), Ordered AS (
SELECT TOP 10 *
, ROW_NUMBER() OVER (ORDER BY valuefield DESC) AS Rank_Number
FROM LargeData
)
SELECT * FROM Ordered
EDIT: Importante de suivi de l'information!
Si dans votre sous-requête vous ajoutez
TOP 999999999 -- improves speed dramatically
Votre requête sera de se comporter de façon similaire à l'utilisation d'une table temporaire dans une requête précédente. J'ai trouvé le temps d'exécution améliorée dans presque exactement de la même manière. Ce QUI EST BEAUCOUP plus simple ensuite à l'aide d'une table temp et c'est en fait ce que je cherchais.
Cependant
TOP 100 PERCENT -- does NOT improve speed
N'effectue PAS de la même façon (vous devez utiliser le Numéro statique HAUT de style 999999999 )
Explication:
De ce que je peux dire d'après le plan d'exécution de la requête dans les deux formats (originale avec la normale CTE de et un avec chaque sous-requête ayant la haute 99999999)
La normale requête rejoint tout ensemble comme si toutes les tables sont dans un massif de requête, qui est ce qui est prévu. Les critères de filtrage est appliqué presque à le rejoindre des points dans le plan, ce qui signifie beaucoup plus de lignes sont en cours d'évaluation et réunis tous à la fois.
Dans la version avec TOP 999999999, le plan d'exécution sépare clairement les sous-requêtes sur de la requête principale afin d'appliquer le TOP déclarations de l'action, forçant ainsi la création d'une mémoire "Image" de la sous-requête qui est ensuite jointe à la requête principale. Cela semble effectivement faire exactement ce que je voulais, et en fait, il peut même être plus efficace puisque les serveurs avec de grandes quantités de RAM sera en mesure de faire la requête d'exécution entièrement en MÉMOIRE, sans e /s de disque. Dans mon cas, nous avons 280 GO de RAM donc bien plus peut jamais vraiment être utilisé.
DECLARE @DT int; SET @DT=20110717
vraiment ce que vous faites ou est un paramètre à la place. C'est important parce que CTE peuvent effectuer différemment en raison de la détection des paramètres alors que les tables temporaires ne semblent pas (dans mon expérience en tout cas)Vous voudrez peut-être jeter un oeil à cette question la détection des paramètres (ou d'Usurpation d'identité) dans SQL Server
C'est juste la façon dont j'écris mes requêtes pour les essais dans le gestionnaire de serveur. La requête ne contient pas de paramètres de cette manière. Dans mon monde réel SQL est la réelle logique derrière cela est peut-être trop ahurissant de comprendre, j'ai regardé certains des plans de requête et ils ont tendance à finir tellement énorme que vous ne pouvez vraiment pas comprendre le goulot d'étranglement.
À la suite de votre modifier ressemble à Fournir une indication de la force intermédiaire de la réalisation des expressions de table communes ou les tables dérivées
Oui exactement @MartinSmith
OriginalL'auteur DarrenMB | 2011-07-18
Vous devez vous connecter pour publier un commentaire.
Non seulement vous pouvez utiliser les index sur les tables temporaires, mais elles permettent l'utilisation de la statistique et de l'utilisation des indices. Je ne trouve pas de refernce de pouvoir utiliser les statistiques dans la documentation sur les expressions de table communes et il dit spécifiquement que vous cann utilisez pas de conseils.
Temp tables sont souvent les plus performantes de chemin à faire quand vous avez un grand ensemble de données, lorsque le choix est entre temp et des tables de variables, même lorsque vous n'utilisez pas les index (possobly, car il va utiliser des statistiques pour élaborer le plan) et je peut soupçonner la mise en œuvre de la CTE est plus comme le tableau de la grandeur de la table temporaire.
Je pense que la meilleure chose à faire est de voir comment la excutionplans sont différents pour déterminer si c'est quelque chose qui peut être fixé.
Quel est exactement votre objection à l'utilisation de la table temp quand vous savez qu'il fonctionne mieux?
Va marquer ce que la réponse, c'est correct dans le fait qu'il ne semble pas être une option pour des conseils ou d'autres pragma à l'intérieur d'un CTE de la structure.
Je viens d'édité mon post original avec ce qui semble faire exactement ce que je cherchais à trouver. Pensé que vous pourriez trouver utiles ou intéressants.
OriginalL'auteur HLGEM
Le problème est que dans la première requête optimiseur de requête SQL Server est capable de générer un plan de requête. Dans la deuxième requête d'un bon plan de requête ne peut pas être généré en raison de l'insertion de valeurs dans une nouvelle table temporaire. Ma conjecture est qu'il ya un full table scan passe quelque part que vous ne voyez pas.
Ce que vous voulez faire dans la deuxième requête est insérer les valeurs dans la #LargeData table temporaire comme vous le faites déjà, et ensuite créer un index non ordonné en clusters sur le "valuefield" de la colonne. Cela peut vous aider à améliorer vos performances.
OriginalL'auteur Paul Mendoza
Il est tout à fait possible que SQL est l'optimisation pour le mal de la valeur des paramètres.
Il ya un couple d'options
Essayez d'utiliser
option(RECOMPILE)
. Il y a un coût à ce qu'il recompile la requête à chaque fois mais si les différents plans sont nécessaires, il pourrait être en vaut la peine.Vous pouvez également essayer d'utiliser
OPTION(OPTIMIZE FOR @DT=SomeRepresentatvieValue)
Le problème avec ceci est que vous choisissez la bonne valeur.Voir Je sens l'Odeur d'un Paramètre! de L'Optimisation des Requêtes SQL Server blog de l'Équipe
OriginalL'auteur Conrad Frix