[SQL2K][TSQL] Pb de changement de plan d'execution

Version imprimable

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:

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

03/12/2008, 11h42
elsuket

Bonjour,

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

@++ ;)
03/12/2008, 12h35
jeeps64

1 pièce(s) jointe(s)

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
03/12/2008, 16h56
elsuket

Bonjour,

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

@++ ;)
03/12/2008, 17h28
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
03/12/2008, 23h28
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.

@++ ;)
04/12/2008, 09h03
jeeps64

Bonjour

Merci tout d'abord pour toutes ces explications (notamment la jointure triangulaire)

Juste pour préciser, quand on met en 1ere position dans la clause 'in', une valeur
n'existant pas ou une valeur ayant peu de lignes dans la tables dossier (ex : GODET : 15 lignes), le plan est bon !!!
JE dis cela en réaction à ce que tu disais :

Citation:

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 gros problème, que j'ai, est que je ne peux pas réécrire la requête. C'est un progiciel qui la gère (mal c'est sur mais bon). J'ai remonté d'ailleurs ce problème au support progiciel.

Si je peux abuser, j'ai quelques questions qui pourrai m'aider à comprendre et à résoudre mon affaire :
- Comment peut-on mettre à jour les statistiques sur SQLSERVER
- Y a-i-il moyen, comme SYBASE que je connais beaucoup plus, de calculer des stats avec histogramme plus ou moins profond ? Histoire de voir si l'optimiseur n'a pas assez d'infos.
- Peut-on forcer ou enregistrer un plan d'execution ? Je sais, c'est pas propre mais pour tester, ca m'aiderai
- Peut-on empêcher l'optimiseur de faire tel ou tel type de jointure ?

Merci d'avance

jeeps64
04/12/2008, 10h18
elsuket
Bonjour,

Citation:

JE dis cela en réaction à ce que tu disais :

Citation:

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

Cela veut dire que vos statistiques sont correctement maintenues (l'option de base de données AUTO_UPDATE_STATISTICS est positionnée à ON). Donc le moteur de base de données sait que les données n'existent pas, donc il ne les cherche pas et passe aux suivantes.
C'est pour cela que j'ai écrit :

Citation:

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

Citation:

J'ai remonté d'ailleurs ce problème au support progiciel.

Et vous avez bien fait !

Citation:

Comment peut-on mettre à jour les statistiques sur SQLSERVER

Vérifiez tout d'abord les options de votre base de données à l'aide de la procédure stockée système sp_dboption :
Code:

1 2 3 EXEC master.dbo.sp_dboption @dbname = 'maBD', @optname = 'auto update statistics'
Si la mise à jour automatique n'est pas activée, alors vous pouvez le faire avec :
Code:

1 2 3 4 EXEC master.dbo.sp_dboption @dbname = 'maBD', @optname = 'auto update statistics' @optvalue = 'true'
Vous pouvez éventuellement forcer régulièrement la mise à jour des statistiques avec la procédure stockée système sp_update_statistics.

Citation:

Y a-i-il moyen, comme SYBASE que je connais beaucoup plus, de calculer des stats avec histogramme plus ou moins profond ? Histoire de voir si l'optimiseur n'a pas assez d'infos.

Pas à ma connaissance. En rechanche vous pouvez utiliser la commande DBCC SHOW_STATISTICS combinée avec la fonction STATS_DATE pour consulter leur état.

Code:

Peut-on forcer ou enregistrer un plan d'execution ?

Je crois que l'indicateur de requête USE PLAN a été introduit avec la version 2005 de SQL Server :?
Vous pouvez consulter ce livre blanc à ce sujet.

Citation:

Peut-on empêcher l'optimiseur de faire tel ou tel type de jointure ?

Vous pouvez spécifier les indicateurs de jointure LOOP, HASH ou MERGE.

Je vous invite pour avoir tous les détails à télécharger la documentation de SQL Server 2000 qui est dans ma signature.

@++ ;)
04/12/2008, 10h24
SQLpro

Citation:

- Comment peut-on mettre à jour les statistiques sur SQLSERVER

UPDATE STATISTICS...

Citation:

- Y a-i-il moyen, comme SYBASE que je connais beaucoup plus, de calculer des stats avec histogramme plus ou moins profond ? Histoire de voir si l'optimiseur n'a pas assez d'infos.

Oui, dans le UPDATE STATISTICS jouer sur la table entière et non sur un échantillon.

Citation:

- Peut-on forcer ou enregistrer un plan d'execution ? Je sais, c'est pas propre mais pour tester, ca m'aiderai

Oui, il faut l'écrire en XML. C'est généralement une très mauvaise idée. SQL Server dispose d'un des meilleurs optimiseur du marché et qui plus est dynamique. Forcer un plan de requête est généralement catastrophique...
Mais vous pouvez faire cela via la clause OPTION.

Citation:

- Peut-on empêcher l'optimiseur de faire tel ou tel type de jointure ?

A nouveau vous pouvez préciser l'algorithme de la jointure dans le code de la requête (MERGE, LOOP, HASH). Même remarque que précédemment : à éviter absolument.

En revanche, pensez à l'indexation !

A +
04/12/2008, 10h39
elsuket

:mouarf:
Je crois qu'on était tous les deux en train de vous répondre en même temps ;)
08/12/2008, 12h29
jeeps64

Bonjour

Merci de vos réponses à tous les deux
Je vais travailler sur l'indexation mais sans conviction

Merci encore

jeeps64