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é.

Vous aurez probablement besoin de regarder le plan d'exécution de la figure de tout cela, mais je suis curieux, est 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