Discussion :

[mgonc]

Bonjour,

J'ai un petit souci de temps de réponse avec une vue utilisant une CTE, je m'explique.
Ci-joint ma vue:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
create view ViewXXX
as
with TableHierarchyCTE (IdTechCleUn, IdTechCleParent, IdClient)
as
(
-- Anchor member definition
select IdTechCleUn, IdTechCleParent, IdClient
from TableA
    union all
select IdTechCleX, IdTechCleUn, IdClient
from Counterparty
    union all
    -- Recursive member definition
    select rg.IdTechCleUn, rg.IdTechCleParent, rghcte.IdClient
    from TableA rg
        inner join TableHierarchyCTE rghcte on rghcte.IdTechCleParent=rg.IdTechCleUn
)

select distinct IdTechCleUn, IdTechCleParent, IdClient
from TableHierarchyCTE
where IdTechCleParent is null

Quand je tape la requête suivante dans le Management Studio, le temps de réponse est inférieur à 1s

Code :

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

1
2
3
select IdClient, IdTechCleUn, IdTechCleParent
from ViewXXX as x
where x.IdClient = 521

Quand j'exécute la même mais à l'aide de sp_executeSql avec un paramètre, le temps de réponse est passé au dela des 5s.

Code :

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

1
2
3
exec sp_executesql N'select IdTechCleUn, IdTechCleParent, IdClient
from ViewXXX as x where x.IdClient = @param_0_CurrentClientId ',N'@param_0_CurrentClientId int',@param_0_CurrentClientId=521

Après analyse sur les plans d'exécution, il s'avère que les deux requêtes n'utilisent pas le même plan d'exécution.
L'une (la première) utilise le filtre sur le client et l'affecte sur la première requête de la CTE en utilisant un index Seek.

La deuxième un peu brutal fait un index Scan sur toutes les tables et filtres le résultats final avec le code Client.

Pourquoi les plans diffères?
Est ce que quelqu'un a déjà été confronté à ce problème?
Y a t'il un moyen de forcer ce filtre?

Merci de votre aide

NB: Les contraintes m'imposent d'utiliser une vue et non pas une procédure stockée.

[SQLpro]

Vous n'utiliser pas les préfixe des schéma des objets, notamment table => perte du contexte d'exécution + recompilation forcée....

Commencez par mettre dbo. partout sauf si vous avez eu l'intelligence de placer vos tables dans vos propres schémas SQL !

A +

[mgonc]

Ok, bonne remarque.

Mais pourquoi dans un cas le plan est correct et dans l'autre cas il réagit autrement?

Avec la même requête sans les préfixes.

[SQLpro]

Une table c'est pas seuelemt un nom ! C'est un schéma + un nom.
Lorsque vous passez seulement le nom il ne sait pas quel objet c'est. Il lui faut faire une résolution de nom : dans quel schéma SQL est la table bidule ? Dans le schéma par défaut de l'utilisateur ou dans celui de la base ???
De plus après avoir trouvé la bonne table avec dbo => ce n'est donc plus le même "motif" (caractères) dans la chaine de requête SQL placée dans le cache. Donc bien que la requête ait déjà été exécutée et son plan mis en cache, il ne vas pas pouvoir la retrouver du fait que les noms des tables avec et sans préfixe ne sont pas les mêmes...

A +

[mgonc]

Merci SQLPro.

Voila, j'ai tout préfixé tel quel mais sans amélioration de résultat....

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
create view dbo.ViewXXX
    as
    with TableHierarchyCTE (IdTechCleUn, IdTechCleParent, IdClient)
    as
    (
    -- Anchor member definition
    select IdTechCleUn, IdTechCleParent, IdClient
    from dbo.TableA
    union all
    select IdTechCleX, IdTechCleUn, IdClient
    from dbo.Counterparty
    union all
    -- Recursive member definition
    select rg.IdTechCleUn, rg.IdTechCleParent, rghcte.IdClient
    from dbo.TableA rg
    inner join TableHierarchyCTE rghcte on rghcte.IdTechCleParent=rg.IdTechCleUn
    )

    select distinct IdTechCleUn, IdTechCleParent, IdClient
    from TableHierarchyCTE
    where IdTechCleParent is null
----------------------------------------------------------------------

    select IdClient, IdTechCleUn, IdTechCleParent
    from dbo.ViewXXX as x
    where x.IdClient = 521

----------------------------------------------------------------------

    exec sp_executesql N'select IdTechCleUn, IdTechCleParent, IdClient
    from dbo.ViewXXX as x where x.IdClient = @param_0_CurrentClientId ',N'@param_0_CurrentClientId int',@param_0_CurrentClientId=521

[clement.bouillier]

Bonjour,

On a essayé de préfixer avec dbo...ça ne change rien. A mon sens, le problème reste inexplicable : pourquoi le plan d'une même requête en paramétrée et en non paramétrée n'est pas le même ! A noter que cela est le cas pour une requête ne contenant pas de CTE récursive, pour une requête "classique", il arrive bien à optimiser le plan d'exécution dans les deux cas.

Pour réproduire la chose (sans les mêmes écarts de temps de réponse mais pour observer la différence de plan d'exécution), il suffit de créer une simple table auto-référencée, d'y insérer quelques tuples et de faire une requête avec CTE récursive permettant par exemple de récupérer l'ensemble de la hiérarchie d'un tuple donné.

La différence de plan d'exécution se situe au niveau du filtre sur la valeur du paramètre. En non paramétré, le filtre est appliqué via un predicate sur le premier Index Scan (ou via un Index Seek) réalisé pour le membre d'ancrage de la CTE récursive. En paramétré, cette optimisation n'est pas réalisée, il y a simplement un Filter à la fin du plan d'exécution, d'où un nombre de read impressionnant pour le membre d'ancrage et par suite sur le membre récursif, et donc un temps de réponse hallucinant...

Ne serait-ce donc pas un problème du moteur SQL Server qui arrive à faire ce genre d'optimisation lorsqu'il n'y a pas de CTE récursive et n'y arrive pas avec une CTE récursive.
En cherchant, on a vu sur Microsoft Connect un feedback qui avait le même symptome (temps de réponse inégal entre non paramétré et paramétré), mais ce n'était pas la même cause : c'était lié à l'utilisation du CASE (il n'y avait pas suffisamment de détails pour être sûr que c'était un problème d'optimisation du plan d'exécution)...
Cependant, ça laisse penser qu'il peut y avoir des différences entre la manière dont le plan d'exécution dans les deux cas, alors qu'on s'attendrait à ce que ce soit identique (de manière simpliste, en paramétré, via du parameter sniffing lors du calcul du plan d'exécution, on retombe dans le cas non paramétré...). Si ce n'est pas le cas, alors j'aimerais bien connaître la décision prise dans le design qui fait que ce n'est pas possible.

Cordialement
Clément Bouillier

[Ptit_Dje]

Bonjour,

Bien qu'a ne pas utiliser a outrance, je me demande si dans ce cas la creation d'un plan guide et l'utilisation du query hint optimized for pourraient vous aider a obtenir de meilleures performances et forcer l'utilisation de l'execution plan souhaite.
Cependant il faut noter que l'utilisation de cette option va forcer l'optimisateur de requete a ignorer les changements de statistiques et d'index, et que donc il faut bien savoir ce que l'on fait.

[SQLpro]

Avez vous placé votre base et paramétrisation forcée ?

Code :

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

ALTER DATABASE ... SET PARAMETERIZATION FORCED

A +

[mgonc]

Oui je l'avais déjà fait sans succès.

[SQLpro]

Pouvez-vous poster l'intégralité du DDL de vos tables et tout ce qui va avec (index, contraintes...) ainsi qu'un jeu d'essais ?

Avez vous respecter la casse originale des objets de la base dans votre requête ?

A +

[clement.bouillier]

En pièce jointe, l'exemple dont je parlais : une table auto-référencée avec peu de données.
Je fournis également un jeu de requête permettant d'observer le problème, ainsi que les plans d'exécution associés.
Évidemment, vu la volumétrie, on ne constate pas le problème de performance mais on observe la différence de plan d'exécution décrite ci-dessus.

A noter sur l'exemple donné par mgonc, nous avons réalisé l'équivalent de ce que fait la CTE via une requête à base de UNION et INNER JOIN (un peu fastidieux). On observe bien les mêmes plans d'exécution en paramétrée ou non paramétrée (je n'ai pas fait cette requête dans l'exemple donné en pièce jointe).

Clément

[SQLpro]

Même s'il y a différence de plans, l'optimiseur ne se trompe pas pour autant puisque le seconds plans sont plus économes en IO que les premiers :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
exec sp_executesql N'with CTE (Id, IdParent, Name)
as
(
	select Id, IdParent, Name
	from SelfReferencingTable
	union all
	select a.Id, b.IdParent, a.Name
	from SelfReferencingTable a
		inner join CTE b on a.IdParent = b.Id
)
select Id, IdParent, Name
from CTE
where IdParent = 1'

(12*ligne(s) affectée(s))
Table 'Worktable'. Nombre d'analyses 2, lectures logiques 69...
Table 'SelfReferencingTable'. Nombre d'analyses 2, lectures logiques 27...

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
exec sp_executesql N'with CTE (Id, IdParent, Name)
as
(
	select Id, IdParent, Name
	from SelfReferencingTable
	union all
	select a.Id, b.IdParent, a.Name
	from SelfReferencingTable a
		inner join CTE b on a.IdParent = b.Id
)
select Id, IdParent, Name
from CTE
where IdParent = @id', N'@id int', @id = 1

(12*ligne(s) affectée(s))
Table 'Worktable'. Nombre d'analyses 2, lectures logiques 199...
Table 'SelfReferencingTable'. Nombre d'analyses 2, lectures logiques 77...

Code :

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

1
2
3
4
5
6
7
8
9
exec sp_executesql N'select Id, IdParent, Name
from 
(select a.Id, a.IdParent, a.Name
from SelfReferencingTable a
union all
select a.Id, b.IdParent, a.Name
from SelfReferencingTable a
	inner join SelfReferencingTable b on a.IdParent = b.Id) recursiveWith2Levels
where IdParent = 1'

(9*ligne(s) affectée(s))
Table 'SelfReferencingTable'. Nombre d'analyses 2, lectures logiques 28...

Code :

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

1
2
3
4
5
6
7
8
9
exec sp_executesql N'select Id, IdParent, Name
from 
(select a.Id, a.IdParent, a.Name
from SelfReferencingTable a
union all
select a.Id, b.IdParent, a.Name
from SelfReferencingTable a
	inner join SelfReferencingTable b on a.IdParent = b.Id) recursiveWith2Levels
where IdParent = @id', N'@id int', @id = 1

(9*ligne(s) affectée(s))
Table 'SelfReferencingTable'. Nombre d'analyses 2, lectures logiques 28...

A +

[clement.bouillier]

Je ne suis pas sûr de comprendre...

Dans le cas de la CTE, le premier plan fait moins de lectures logiques et non pas plus, donc les seconds plans NE sont PAS plus économes en IO que les premiers, non ? Et c'est juste bien le problème...
Dans le cas de la requête simple, les deux plans sont absolument équivalent.

Non ?

[SQLpro]

Il est difficile de comparer des choses non comparables... Une requête récursive ne fait pas le même boulot qu'une requête non récursive.

Une requête paramétrée n'est pas optimisée systématiquement de la même façon q'une requête adhoc.

A +

[mgonc]

Bonjour,

Votre conclusion est exactement ma question, mais cela ne répond toujours pas à ma question qui était:

Pourquoi une requête RECURSIVE avec paramètre ou sans paramètre ne se comporte pas pareil ??

[clement.bouillier]

Bonjour,

Le bug a été confirmé par l'équipe Microsoft de l'Optimizer de SQL Server.

Pour info, le problème est également reproductible sur 2008.

Le correctif ne sera pas disponible tout de suite.

Cdlt,
Clément