Discussion :

[jeeps64]

Bonjour

J'ai un probleme de changement de plan d'une requete suivant l'ordre dans lequel je place les valeurs dans une clause 'in'

La requete en question est celle présentée ci dessous
Le requête avec comme owner_name DARAI en premier dans la premiere clause in prend un mauvais plan d’exécution et remplit au final tempdb. Dans le plan, il fait des rapprochements de jointure « MANY to MANY » apparemment très consommateurs et tout cela dans tempdb.

Si l’on place ‘DARAI’ en second dans la clause in, le plan devient très bon et la requête est très rapide.


Je pense que le problème vient de la façon dont l’optimiseur SQLSERVER gère les clauses in. J’ai l’impression qu’il ne considère les statistics que sur la première valeur de la clause in.

En effet, nous avons 87 lignes correspondant à DARAI dans la table dossier. Pour ‘dm_wolrd’ (seconde valeur), nous n’avons aucune valeur.

J’ai fait le teste en remplacant DARAI par GODET (15 lignes dans la table dossier), le plan est bon qu’il soit en première ou deuxième position dans la clause ‘in’

J’ai fait les tests suivants :
- création d’index à Non pertinent
- reconstruction des indexes à Non pertinent
- mise à jour des stats à Non pertinent

Il faut savoir que la requête est dynamique et qu'on ne peut la réécrire


Si l'un d'entre vous a une piste ou un point qui m'aurais échappé

Merci d'avance

Jeeps64

Requête :

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
 
select *
from
 co_fld_dossier_sp dossier,
 co_fld_comite_sp comite,
 co_doc_odj_sp odj,
 dbo.dm_sysobject_r odj_r,
 co_fld_dossier_rp dm_repeating1_0
where  ((comite.r_object_id=dossier.co_nom_comite_dossier)
and (odj_r.i_folder_id=comite.r_object_id)
and (odj.r_object_id=odj_r.r_object_id)
and dm_repeating1_0.r_object_id=dossier.r_object_id
and (comite.co_typ_comite=N'CCG')
and (comite.co_dat_comite>= convert(datetime, '01/11/2008 0:0:0', 103))
and (dossier.co_top_confidentiel=N'N'))
and (dossier.i_has_folder = 1 and dossier.i_is_deleted = 0)
and (comite.i_has_folder = 1 and comite.i_is_deleted = 0)
and (
(dossier.owner_name in ('DARAI','dm_world','reporting','rol_creation_repo','rol_admin','rol_creation_doss','rol_creation_comi','administrateur'))
or (exists(select 1 from dm_acl_s ACL_S, dm_acl_r ACL_R 
            where ACL_S.r_object_id = ACL_R.r_object_id and ossier.acl_domain = ACL_S.owner_name and dossier.acl_name = ACL_S.object_name and ((ACL_R.r_accessor_name in ('DARAI','dm_world') or (ACL_R.r_is_group = 1 and (ACL_R.r_accessor_name in ('DARAI','dm_world','reporting','rol_creation_repo','rol_admin','rol_creation_doss','rol_creation_comi','administrateur'))))
and ((ACL_R.r_permit_type = 0 or ACL_R.r_permit_type is null)
and (((ACL_R.r_accessor_permit >= 2))))))))
and ((comite.owner_name in ('dm_world','DARAI','reporting','rol_creation_repo','rol_admin','rol_creation_doss','rol_creation_comi','administrateur'))
or (exists(select 1 from dm_acl_s ACL_S, dm_acl_r ACL_R where ACL_S.r_object_id = ACL_R.r_object_id and comite.acl_domain = ACL_S.owner_name and comite.acl_name = ACL_S.object_name and (( ACL_R.r_accessor_name in ('DARAI','dm_world') or (ACL_R.r_is_group = 1 and (ACL_R.r_accessor_name in ('DARAI','dm_world','reporting','rol_creation_repo','rol_admin','rol_creation_doss','rol_creation_comi','administrateur')))) and ( (ACL_R.r_permit_type = 0 or ACL_R.r_permit_type is null) and (((ACL_R.r_accessor_permit >= 2)))))))) and ((odj.owner_name in('DARAI','dm_world','reporting','rol_creation_repo','rol_admin','rol_creation_doss','rol_creation_comi','administrateur')) or (exists(select 1 from dm_acl_s ACL_S, dm_acl_r ACL_R where ACL_S.r_object_id = ACL_R.r_object_id and odj.acl_domain = ACL_S.owner_name and odj.acl_name = ACL_S.object_name  and ((ACL_R.r_accessor_name in ('DARAI','dm_world') or (ACL_R.r_is_group = 1 and (ACL_R.r_accessor_name in ('DARAI','dm_world','reporting','rol_creation_repo','rol_admin','rol_creation_doss','rol_creation_comi','administrateur')))) and ((ACL_R.r_permit_type = 0 or ACL_R.r_permit_type is null) and (((ACL_R.r_accessor_permit >= 2))))))))
order by 6 desc

[elsuket]

Bonjour,

Peut-on voir les structures des tables, et les deux plans d'exécution ?

@++

[jeeps64]

Voici les plans d'execution
Par contre, dans la requete, il s'agit de vues. La requete se fait donc sur une vingtaine de tables.Je ne peux pas vous ennuyer pour débugger un probleme d'optim de requete sur 20 tables

Pour info, je débute sur l'administration SQLSERVER donc je cherche surtout une méthode pour travailler et dans ce cas ci une explication de comment travaille l'optimiseur sur une clause 'in'

Je te remercie de ton aide

jeeps64

[elsuket]

Bonjour,

Ce n'est pas les plans d'exécution; Faites CTRL+L et donnez nous des captures d'écran.

@++

[jeeps64]

en fait, c'est un fichier excel et tu vois les plans dans les onglets (onglet GODET : plan bon, onglet DARAI : plan pas bon)
Par contre, ce sont les plans estimés

Cdlt
jeeps64

[elsuket]

OK, toutes mes excuses.

J'ai essayé de reprendre votre requête en indentant le code, mais j'obtiens des problèmes de syntaxe lors de l'analyse.

Toutefois pour expliquer la différence de performances, vous avez raison de considérer que le IN est le lieu de l'étranglement, parce que de base ce n'est pas un SARG.
Comme vous le dites, si vous commencez votre liste par une valeur qui en fait n'existe pas, alors le moteur de base de données est obligé de scanner la table ou l'index cluster (souvent de la clé primaire), ce qui s'avère être lent et coûteux. Plus vos valeurs dans la clause IN sont classés dans l'ordre décroissant de sélectivité, plus votre requête s'avèrera être rapide, parce que les statistiques portent sur les valeurs qui sont dans la colonne, par sur celles qui n'y sont pas
Le problème bien sûr, c'est qu'on ne peut pas toujours connaître la cardinalité d'une valeur dans la colonne ...

Passons maintenant à ce qui ne concerne pas directement votre question.
Outre ce problème de comportement de l'optimiseur, vous avez spécifié votre requête avec des jointures dans la clause WHERE.
Ce n'est pas incorrect, en revanche cela peut dans certains cas empêcher l'optimiseur de requêtes de voir que vous souhaitez effectuer une jointure et non pas un filtrage comme il s'y attend dans la clause WHERE.

Vous ne spécifiez pas non plus la liste exacte des colonnes qui doit être retournée par votre requête, ce qui pose approximativement les mêmes problèmes.
Si cette requête est extraite d'une procédure stockée, alors remplacez le "*" par la liste exacte des colonnes dont vous avez besoin.
Si en revanche il s'agit d'un requête que vous êtes en train de mettre au point, alors n'oubliez pas la remarque précédente.

Ensuite vous faites des jointures triangulaires.
Vous pouvez lire pourquoi cela est contre-performant dans le billet que j'ai écrit sur ce sujet.
Pour les éviter, vous pouvez utiliser les expressions de table commune (CTE) et réaliser des jointures sur cette CTE.

Dans une clause EXISTS (comme pour la fonction COUNT), il est mieux de spécifier "*" qu'une valeur : le moteur de base de données est optimisé pour cela.

Il est dommage que vous ne nous ayez pas fourni le DDL de vos tables, car cela aurait permis de voir où sont les indexes, et éventuellement s'il en manque.

Enfin, si vous souhaitez que je réécrive votre requête pour voir, faites-moi signe.

@++