Discussion :

[imikado]

Bonjour,
VOici le problème rencontré aujourd'hui:

Soit la requête suivante:

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT top 1 monChamp,monchamp2 from maVue where maFK = 10 and maFK2 = 12 order by maPkey desc

Si on enlève le order by ou le top, la requête est quasi instantanée.

En cherchant un peu, on est tombé sur cet article:
http://www.mssqltips.com/sqlserverti...in-sql-server/

Et en modifiant ainsi la requête, elle devient instantannée :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
DECLARE @i INT
SET @i=1
SELECT top(@i) monChamp,monchamp2 from maVue where maFK = 10 and maFK2 = 12 order by maPkey desc

Des commentaires ?, des astuces à partager sur le sujet ?

[SQLpro]

Le problème du TOP a été abordé maintes fois. Ce n'est pas une opération relationnelle et par conséquent ses performances dépendant grandement de paramètres physiques différents ainsi que de statistiques et d'indexation...

A +

[imikado]

Relisez mieux mon post:
Pourquoi

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

top(1)

Est plus lent que

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

top(@i)

(Avec @i égal à 1)

[aieeeuuuuu]

Bonjour,

Et en modifiant ainsi la requête, elle devient instantannée :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
DECLARE @i INT
SET @i=1
SELECT top(@i) 1 monChamp,monchamp2 from maVue where maFK = 10 and maFK2 = 12 order by maPkey desc

Est-ce que le 1 après le TOP(@i) est une erreur de copier/coller ???
Sinon, ça peut expliquer la rapidité : la requête n'est plus la même, elle ne remonte plus le contenu de la colonne "monChamp", et peut éventuellement profiter d'un index couvrant, ou autre...

Avez-vous comparé les plans d’exécution des deux requêtes ?

[imikado]

Oui c'est une erreur de copier coller (j'ai corrigé dans le post d'origine)

L'idée c'est la différence entre le TOP 1 vs le TOP @i (avec @i égal à 1)

Non je n'ai pas regardé le plan d'éxécution, mais l'article cité est intéressant à lire

[elsuket]

Bonjour,

Ici je pense qu'il s'agit d'un petit problème d'estimation de cardinalités / reniflage de paramètres : il faudrait étudier le plan d'exécution pour s'en convaincre.

Lorsque le code est TOP 1, SQL Server réalise une estimation de cardinalité, et utilise en conséquence et par exemple, un index qui ne couvre pas la requête, et qui produit bien plus de key lookups qu'escompté, soit du fait d'une répartition inégale des cardinalités dans la plage de valeurs de maFK et maFK2.

En revanche en paramétrant la requête comme vous le faites avec une variable locale, on outrepasse l'estimation de cardinalités, ce qui fait que SQL Server utilise des règles d'estimation de cardinalités par défaut qui produisent un plan de requête plus efficace, par exemple qui utilise une index cluster qui couvre bien la requête.

Il serait intéressant de savoir également si vous avez un index sur les colonnes maFK et maFK2, si elles appartiennent à la même table, et si ledit index contient les colonnes monChamp et monChamp2. L'histogramme de la statistique sous-jacente sera également utile.

Il vous est donc nécessaire de collecter les deux plans réels d'exécution, et de les comparer pour voir où se situe la différence.
Une telle comparaison est facilitée par l'outil SQL Sentry Plan Explorer

[imikado]

Voila les 2 plans d'executions

[elsuket]

Merci. Malheureusement cela est insuffisant puisque les cardinalités ne sont pas exposées.

Le mieux est d'utiliser SQL Sentry Plan Explorer, qui comprend une fonctionnalité d'anonymisation de fichiers de plan d'exécution, et de mettre ces fichiers (pas les capture d'écran) en pièce attachée. Voici ce que l'on obtient par exemple :



@++

[imikado]

Je regarde, en passant sympa l'article

[imikado]

Voici les deux fichiers + les images:

[elsuket]

C'est dommage, vous ne donnez pas le plan d'exécution réel : pour l'obtenir il faut faire CTRL + M ou bien cliquer sur l'icône suivante dans SQL Server Management Studio :



Le plan d'exécution réel permet de déceler la différence entre le nombre de lignes estimé à partir des statistiques de colonnes et le nombre réel de lignes récupérées.

Cela étant, même avec le plan d'exécution estimé, on voit clairement que les cardinalités à la racine du plan, ainsi que l'ordre et les algorithmes d'accès aux tables sont nettement différents :



Vous pouvez obtenir un seul fichier de plan d'exécution pour les deux requêtes : il vous suffit pour cela de la exécuter dans la même fenêtre de requête, en intercalant un GO entre les deux pour qu'il n'y ait pas "d'interférences" entre les deux requêtes au niveau des variables.

Merci pour l'article; si vous souhaitez aller plus loin, je vous conseille de lire les deux ouvrages que je référence dans ce billet.

Enfin si vous en avez le temps, venez aux Journées SQL Server : c'est la 3e édition du plus gros événement dédié à SQL Server en France, qui se déroulera les 2 et 3 décembre.

Dans l'attente des plans d'exécution réels,

@++

[imikado]

Je ne peux pas faire " ctrl + M " j'ai la version gratuite et non la payante

[imikado]

Avec l'option cochée (j'avais mal lu)

[elsuket]

Ha, les deux plans sont toujours des plans estimés.

Il vous faut faire CTRL + M dans SQL Server Management Studio avant d'exécuter les requêtes. Une fois qu'elles sont exécutées, un 3e onglet va apparaitre dans le panneau de résultats, après celui pour le résultat et celui pour les messages : c'est ce plan qu'il nous faut

@++

[imikado]

Mais j'execute toujours dans votre logiciel ou dans le client sql "normal" ?

[elsuket]

Héhé, c'est ce que je pensais : désolé pour le manque de précision

Donc vous devez :

1. faire CTRL + M (ou cliquer sur l'icône que je vous ai indiqué plus haut) dans SQL Server Management Studio
2. Exécuter la requête, toujours dans SQL Server Management Studio
3. A la fin de l'exécution de la requête, un onglet Plan d'exécution va s'afficher : si vous avez le plugin SQL Server Management Studio de SQL Sentry, il vous suffit de faire un clic-droit dans une zone vierge du plan de requête pour voir celui-ci exposé dans SQL Sentry Plan Explorer. De là, vous pouvez l'enregistrer comme fichier tout en observant les cardinalités

@++

[imikado]

Ok, merci je regarderais demain pour installer le plugin

[imikado]

Ci-joint comme demandé

Merci encore de votre aide.

[elsuket]

Étonnamment les deux fichiers font la même taille. Je viens de les comparer avec Beyond Compare, et ils sont identiques

Dans l'attente de ces plans

@++

[imikado]

Je referais une génération la semaine prochaine, j'ai du me tromper à la génération.

Question: ces résultats permettront d'expliquer pourquoi le plan d'execution diffère entre un TOP 1 et un TOP @i (@i=1 également) ?