Discussion :

[olysmar2]

Bonjour,

J'ai la requète suivante:

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
32
33
34
35
36
37
38
39
40
 
select top 25 * from 
(select tab,id,[cle],[id_utilisateur],[id_suivi],vue.[dcr],[id_champ_valeur_adresse],[ville],[cp],[region],[pays],[adresse_long],[adresse_court],[email],[emadir],[telephone],[teldir],[mobile],[mobdir],[site],[sitedir],vue.[libelle],[isole],[pseudo],[fonction],[libelle_suivi],dbo.F_INDEX_GROUP_DATE(vue.dcr) as groupdate,0 as nbregroupe from 
(
select * from (
SELECT dbo.vue_grille_structures_contact.tab, dbo.vue_grille_structures_contact.id, dbo.vue_grille_structures_contact.cle,
 dbo.vue_grille_structures_contact.dcr,dbo.vue_grille_structures_contact.ad1, dbo.vue_grille_structures_contact.ad2, dbo.vue_grille_structures_contact.ad3, 
 dbo.vue_grille_structures_contact.cp, dbo.vue_grille_structures_contact.ville, dbo.vue_grille_structures_contact.cedex, 
 dbo.vue_grille_structures_contact.region, dbo.vue_grille_structures_contact.pays, dbo.vue_grille_structures_contact.geo_latitude, 
 dbo.vue_grille_structures_contact.geo_longitude, dbo.vue_grille_structures_contact.geo_confidence, dbo.vue_grille_structures_contact.geo_matchcode, 
 dbo.vue_grille_structures_contact.adresse_court, dbo.vue_grille_structures_contact.adresse_long,vue_grille_structures_contact.adresse_direct,
 dbo.vue_grille_structures_contact.email, 
 dbo.vue_grille_structures_contact.emadir, dbo.vue_grille_structures_contact.telephone, dbo.vue_grille_structures_contact.teldir, 
 dbo.vue_grille_structures_contact.mobile, dbo.vue_grille_structures_contact.mobdir,dbo.vue_grille_structures_contact.libelle, 
 dbo.vue_grille_structures_contact.isole, dbo.vue_grille_structures_contact.fonction, dbo.vue_grille_structures_contact.id_suivi, 
 dbo.vue_grille_structures_contact.libelle_suivi, dbo.vue_grille_structures_contact.id_utilisateur AS strcon_id_utilisateur, 
 dbo.vue_grille_structures_contact.pseudo, dbo.vue_grille_structures_contact.id_utilisateur, dbo.vue_grille_structures_contact.id_champ_valeur_adresse,
 dbo.vue_grille_structures_contact.site, dbo.vue_grille_structures_contact.sitedir,strconcom.id_communication,strconcom.id_structures_contact_communication 
 FROM      dbo.vue_grille_structures_contact LEFT OUTER JOIN 
 (select id_communication,id_structures_contact_communication,id_structure from dbo.vue_structures_contact_communication where id_communication=19558
 AND id_type_destinataire = 1) as strconcom ON 
 dbo.vue_grille_structures_contact.id = strconcom.id_structure WHERE	  
 dbo.vue_grille_structures_contact.tab = 'str'
UNION 
SELECT dbo.vue_grille_structures_contact.tab, dbo.vue_grille_structures_contact.id, dbo.vue_grille_structures_contact.cle, dbo.vue_grille_structures_contact.dcr,dbo.vue_grille_structures_contact.ad1, dbo.vue_grille_structures_contact.ad2, dbo.vue_grille_structures_contact.ad3, dbo.vue_grille_structures_contact.cp, dbo.vue_grille_structures_contact.ville, dbo.vue_grille_structures_contact.cedex, dbo.vue_grille_structures_contact.region, dbo.vue_grille_structures_contact.pays, dbo.vue_grille_structures_contact.geo_latitude, dbo.vue_grille_structures_contact.geo_longitude, dbo.vue_grille_structures_contact.geo_confidence, dbo.vue_grille_structures_contact.geo_matchcode, dbo.vue_grille_structures_contact.adresse_court, dbo.vue_grille_structures_contact.adresse_long,vue_grille_structures_contact.adresse_direct, dbo.vue_grille_structures_contact.email, dbo.vue_grille_structures_contact.emadir, dbo.vue_grille_structures_contact.telephone, dbo.vue_grille_structures_contact.teldir, dbo.vue_grille_structures_contact.mobile, dbo.vue_grille_structures_contact.mobdir,dbo.vue_grille_structures_contact.libelle, dbo.vue_grille_structures_contact.isole, dbo.vue_grille_structures_contact.fonction, dbo.vue_grille_structures_contact.id_suivi, dbo.vue_grille_structures_contact.libelle_suivi, dbo.vue_grille_structures_contact.id_utilisateur AS strcon_id_utilisateur, dbo.vue_grille_structures_contact.pseudo, dbo.vue_grille_structures_contact.id_utilisateur, dbo.vue_grille_structures_contact.id_champ_valeur_adresse,dbo.vue_grille_structures_contact.site, dbo.vue_grille_structures_contact.sitedir,strconcom.id_communication,strconcom.id_structures_contact_communication 
 FROM      dbo.vue_grille_structures_contact LEFT OUTER JOIN 
 (select id_communication,id_structures_contact_communication,id_contact from dbo.vue_structures_contact_communication where id_communication=19558
 AND id_type_destinataire = 1) as strconcom ON 
 dbo.vue_grille_structures_contact.id = strconcom.id_contact WHERE	  
 dbo.vue_grille_structures_contact.tab = 'con'
UNION 
SELECT dbo.vue_grille_structures_contact.tab, dbo.vue_grille_structures_contact.id, dbo.vue_grille_structures_contact.cle, dbo.vue_grille_structures_contact.dcr,dbo.vue_grille_structures_contact.ad1, dbo.vue_grille_structures_contact.ad2, dbo.vue_grille_structures_contact.ad3, dbo.vue_grille_structures_contact.cp, dbo.vue_grille_structures_contact.ville, dbo.vue_grille_structures_contact.cedex, dbo.vue_grille_structures_contact.region, dbo.vue_grille_structures_contact.pays, dbo.vue_grille_structures_contact.geo_latitude, dbo.vue_grille_structures_contact.geo_longitude, dbo.vue_grille_structures_contact.geo_confidence, dbo.vue_grille_structures_contact.geo_matchcode, dbo.vue_grille_structures_contact.adresse_court, dbo.vue_grille_structures_contact.adresse_long,vue_grille_structures_contact.adresse_direct, dbo.vue_grille_structures_contact.email, dbo.vue_grille_structures_contact.emadir, dbo.vue_grille_structures_contact.telephone, dbo.vue_grille_structures_contact.teldir, dbo.vue_grille_structures_contact.mobile, dbo.vue_grille_structures_contact.mobdir,dbo.vue_grille_structures_contact.libelle, dbo.vue_grille_structures_contact.isole, dbo.vue_grille_structures_contact.fonction, dbo.vue_grille_structures_contact.id_suivi, dbo.vue_grille_structures_contact.libelle_suivi, dbo.vue_grille_structures_contact.id_utilisateur AS strcon_id_utilisateur, dbo.vue_grille_structures_contact.pseudo, dbo.vue_grille_structures_contact.id_utilisateur, dbo.vue_grille_structures_contact.id_champ_valeur_adresse,dbo.vue_grille_structures_contact.site, dbo.vue_grille_structures_contact.sitedir,strconcom.id_communication,strconcom.id_structures_contact_communication 
 FROM      dbo.vue_grille_structures_contact LEFT OUTER JOIN 
 (select id_communication,id_structures_contact_communication,id_strcon from dbo.vue_structures_contact_communication where id_communication=19558
 AND id_type_destinataire = 1) as strconcom ON 
 dbo.vue_grille_structures_contact.id = strconcom.id_strcon WHERE	  
 dbo.vue_grille_structures_contact.tab = 'stc') as vv
   where id_communication is null
 )
  as vue where ((tab='stc') or (tab='con' and isole=1) or (tab='str' and isole=1))) as res  order by libelle asc

Ce qui est trés curieux, c'est que quand celle-ci ne me retourne aucun résultat, s'il n'y a pas le TOP 25 au début, tout roule
Si je met le TOP 25, je suis en time out...

Si la requète me retourne des résultats, la tout roule.

Est ce que quelqu'un aurait une piste pour corriger ou contourner le problème (le TOP 25 est important) ?

Merci
Sylo

[olysmar2]

Est ce que utilisé le row_number est une bonne idée pour contourner le problème
Est ce que ce n'est pas plus lourd ?
Merci

[pcaboche]

Est ce que utilisé le row_number est une bonne idée pour contourner le problème
Est ce que ce n'est pas plus lourd ?

Non, ce n'est pas une bonne idée.
Oui, c'est plus lourd.

Je pense que l'on peut grandement simplifier votre requête.

En effet, il me semble que vous faites des UNION entre 3 requêtes quasiment identiques, seule la condition change :

Code :

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

1
2
3
dbo.vue_grille_structures_contact.tab = 'str'
dbo.vue_grille_structures_contact.tab = 'con'
dbo.vue_grille_structures_contact.tab = 'stc'

Ça peut être grandement simplifié.
De plus, UNION ajoutera une opération supplémentaire : la suppression d'éventuels doublons (inexistants dans ce cas), à l'inverse d'un UNION ALL

De même on peut simnplifier les LEFT OUTER JOIN (pas besion de faire une sous-requète) en modifiant la condition de jointure.

Maintenant, quel est le problème avec votre "TOP 25" ?
À mon avis, la multiplication des UNION et sous-requêtes complique la tâche de SQL Server. Il essaye sans doute d'optimiser le TOP, mais retourne un plan d'exécution sub-optimal.

En simplifiant votre requête, il y a des chances que SQL Server s'y retrouve un peu mieux et que les performances soient au rendez-vous.


En tenant compte des remarques précédentes (notamment l'UNION qui ne semble pas nécessaire), j'ai essayé de simplifier votre requête.
Au final, j'arrive à cela :

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
SELECT TOP 25
    tab, id, [cle], [id_utilisateur], [id_suivi], vue.[dcr], [id_champ_valeur_adresse], [ville], [cp], [region], [pays], [adresse_long], [adresse_court], [email], [emadir], [telephone], [teldir], [mobile], [mobdir], [site], [sitedir], [libelle], [isole], [pseudo], [fonction], [libelle_suivi], 
    dbo.F_INDEX_GROUP_DATE(dcr) as groupdate, 
    0 as nbregroupe
FROM dbo.vue_grille_structures_contact
LEFT OUTER JOIN dbo.vue_structures_contact_communication AS strconcom
  ON (  dbo.vue_grille_structures_contact.id = strconcom.id_contact
    AND strconcom.id_communication = 19558
    AND strconcom.id_type_destinataire = 1
  )
WHERE strconcom.id_communication is null
AND (
     (tab = 'stc')
  OR (isole = 1 AND tab IN ('str',  'con')
)
ORDER BY libelle asc

J'en viens à me demander si je ne l'ai pas trop simplifiée et si ça donne le bon résultat.

Mais bon, ça vous donne une idée...

[olysmar2]

Bonjour,

Merci beaucoup de vous être penché en détail sur mon problème...
En fait si j'ai ces 3 UNION, c'est parce que le lien entre les deux tables n'est pas le même.
J'ai bien essayer de faire un ON avec des OR mais les résultat retourné n'était pas cohérent.

1er UNION : dbo.vue_grille_structures_contact.id = strconcom.id_structure
2ème UNION: dbo.vue_grille_structures_contact.id = strconcom.id_contact
3ème UNION: dbo.vue_grille_structures_contact.id = strconcom.id_strcon

Si je fait une seule requète avec

Code :

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

1
2
select * from vue_grille_structures_contact LEFT OUTER JOIN strconcom
ON (dbo.vue_grille_structures_contact.id = strconcom.id_structure) OR (dbo.vue_grille_structures_contact.id = strconcom.id_contact) OR (dbo.vue_grille_structures_contact.id = strconcom.id_strcon)

Le résultat est totalement incohérent et je ne comprend pas pourquoi

MErci bcp pour votre aide
Sylo

[olysmar2]

Je précise que le rownum me pose le même problème.

Puisque le "select *" me donne bien ma liste (vide), je me suis dit que faire un "select top 25 * from (select*)" devrait résoudre le problème mais hélas non...

Je cherche toujours
Merci
Sylo

[iberserk]

statistiques et indexes sont-ils à jour.

Avez vous simplifié la requête comme vous l'a indiqué pcaboche?

[pcaboche]

Note : j'ai essayé de répondre à plusieurs reprises (principalement entre 06:30 et 08:00, UTC+1), mais ce forum est de plus en plus lent...
Et impossible de prévisualiser le message.

Avez vous simplifié la requête comme vous l'a indiqué pcaboche?

En fait, ce n'est pas si simple, comme il le montre dans sa réponse.

En fait si j'ai ces 3 UNION, c'est parce que le lien entre les deux tables n'est pas le même.

Ok, je me disais bien qu'il devait y avoir quelque chose de différent, mais je n'arrivais pas à voir quoi (la requête n'est pas super lisible).

Le résultat est totalement incohérent et je ne comprend pas pourquoi

C'est normal, vous faites 3 jointures différentes suivant les cas (conditions du WHERE).
Donc en théorie, ça devrait plutôt ressembler à ça :

Code :

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

1
2
3
4
5
6
ON (
  (    dbo.vue_grille_structures_contact.tab = 'str'
  AND dbo.vue_grille_structures_contact.id = strconcom.id_structure) 
  OR
   ...
)

Malheureusement, ce type de jointure n'est pas performant.
En effet, le fait d'avoir un OR dans la condition de jointure, SQL Server ne peut plus optimiser.
Pour pallier au problème, on remplace ceci par... une UNION !

Vous pouvez toujours essayer, mais à mon avis, les performances seront mauvaises.


À part ça :
- quelle est la performance de chaque sous-requête prise séparément ?
- existe-t-il des index sur les différentes colonnes sur lesquelles on fait une restriction (WHERE) ou une jointure ?


Sinon, je pense à quelque chose...
On recherche 25 premiers éléments de l'ensemble. Il vous faut donc au maximum les 25 premiers éléments de chaque sous-ensemble.

On pourrait donc tenter quelque chose comme ça :

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
SELECT TOP 25 ...
FROM
(
  SELECT *
  FROM
  (
    SELECT TOP 25 ...
    FROM ...
    WHERE ...
    ORDER BY ...
  ) A
 
  UNION ALL
  ...
) V

Le but ici serait de limiter la taille de chaque sous-ensemble.

Puisque le "select *" me donne bien ma liste (vide), je me suis dit que faire un "select top 25 * from (select*)" devrait résoudre le problème mais hélas non...

Comparez les plans d'exécution entre les deux. Ça vous donnera une piste ce qu'il se passe.

[SQLpro]

De toute facons, la discussion est stérile parce que le gars ne respecte pas la charte de postage. Tout ce que vous pouvez faire pour l'aider c'est de simple conjectures.

RESPECTEZ LA CHARTE DE POSTAGE :
http://club.developpez.com/regles/

1) postez le DDL des vues et des tables
2) postez les contraintes et index sous forme DDL

D'autre part, un effort minimum est nécessaire pour la compréhension de tous... Présenter correctement ses requêtes :

• en indentant le code (si le gars est une feignasse, il peut utiliser un outil en ligne...)
• en utilisant systématiquement des alias de table

A +

[olysmar2]

Bonjour,

Merci beaucoup pour toute vos réponse.
Je fait au mieux pour être le plus clair possible dans mes questions, car je sais que cela est nécessaire pour avoir une réponse de qualité.
Désolé que cela ne soit pas encore assez clair.

Merci en tout cas pour vos réponse détaillé.

Je ne peux pas faire ce que tu me propose pcaboche car j'ai besoin du TOP 25 de la concaténation du résultat...
Mais votre aide m'a apporté de nombreuse piste que j'ai pu étudier.

Bref, j'ai remarqué que c'était le

Code :

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

idcommunication is null

qui semblait tout ralentir.

J'ai donc fait un coalesce dessus pour avoir 0 au lieu de null.
Et la, miracle, cela fonctionne...

Donc pour moi, problème résolu même si je ne m'explique toujours cette lenteur...

Merci beaucoup à tous
Sylo

[aieeeuuuuu]

Bonjour,

La réponse à la question se trouve très certainement dans les plans d’exécution des deux requêtes.
Si vous les postiez, nous pourrions vous apporter des réponses plus concrètes et plus pertinentes...

Il est fort probable que le TOP(25) engendre une modification du plan d’exécution.
Le moteur, sachant qu'il ne devra fournir que les 25 premières lignes, pourrait opter pour des boucles imbriquées plutôt que des hash match.
Tant que le nombre de d’itérations reste faible pour la boucle, cette option est en effet plus rapide qu'une jointure complète.

Or :

c'est que quand celle-ci ne me retourne aucun résultat,

Dans ce cas en effet, le nombre d’exécutions de la boucle doit être au final bien plus élevè que prévu par l'optimiseur : tant qu'aucune ligne ne répond aux critères de la recherche, il doit boucler encore et encore.

De fait, le plan qui semblait meilleur est au final catastrophique car les données réelles ne correspondent pas à ce qui avait était prévu, prévisions faites à partir des statistiques dont dispose l'optimiseur (d'où la question : vos statistiques sont-elles à jour ?)

Bref, j'ai remarqué que c'était le idcommunication is null

On peut supposer que le moteur a donc décidé d'utiliser un index sur cette colonne pensant réduire le volume de données à traiter, et de boucler sur chaque ligne qui sortiront de cet index pour voir si elles répondent aux autres critères.
De fait, remplacer cette partie par COALESCE(idcommunication, 0) = 0 rend l'utilisation de l'index impossible, et on repart donc sur un plan plus proche de celui de la requete sans TOP(25).

J'ai cependant un doute sur cette théorie en raison de votre ORDER BY libelle qui suppose donc de récupérer toutes les lignes pour pouvoir les trier avant de ne prendre que les 25 premières... Je n'ai cependant pas eu le courage d'essayer de lire la requete que vous avez postée : elle est illisible !

Voici un petit exemple pour montrer comment un TOP dans une requete peut engendrer un plan d’exécution bien pire que la même requete sans TOP :

1/ prenons deux tables relirées par une FK, peuplée de 5000 lignes pour la table reférencée, et de 5000 X 1000 lignes pour la table référençante

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
 
 
create table mere (
	id int not null primary key clustered
	,c char(10)
)
;
INSERT INTO mere (id)
	select top(5000) row_number() over(order by (select null))
	from sys.all_objects A, sys.all_objects B
;
 
UPDATE mere set c = 'A' WHERE id%2 = 1
;
 
create table fille (
	id int not null primary key clustered identity
	,c varchar(50)
	, m int not null foreign key references mere(id)
)
;
GO
 
insert into fille (m, c)
	select id , NEWID() from mere
go 1000

modifions un peu les données, afin que les requêtes ci-dessous ne sortent aucune ligne puis indexons :

Code :

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

1
2
3
4
5
6
7
8
 
 
delete from fille where c LIKE 'AA%' AND m%2=1
 
update fille set c = 'AAlkf' where m%4=0
 
 
CREATE INDEX IX_C ON fille(c) include(m)

lançons maintenant une requete :
R1 : sans clause TOP
R2 : TOP(10)
R3 : TOP(1000)

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
 
--R1
select mere.c
from mere
inner join fille
	on fille.m = mere.id
where fille.c LIKE 'AA%'
and mere.c = 'A'
 
--R2
select top(10) mere.c
from mere
inner join fille
	on fille.m = mere.id
where fille.c LIKE 'AA%'
and mere.c = 'A'
 
--R3
select top(1000) mere.c
from mere
inner join fille
	on fille.m = mere.id
where fille.c LIKE 'AA%'
and mere.c = 'A'

résultat :

	tps UC(ms)	IO
R1	63	3704
R2	1965	2513434
R3	515	3617

Les trois plans d’exécution sont bien différents :


on voit que pour R2, l'optimiseur a opté pour une boucle imbriquée. Il pensait exécuter 21 recherches d'index dans la table mere (estimant donc qu'une ligne sur deux dans la table mère n'allait pas satisfaire la condition). Comme aucune ligne dans la table mère ne convient, il a en fait exécuté 1254917 recherche dans cette table, d'où le nombre important de lecture et le temps d'éxécution.

Pour R3, le plan est plus proche de R1 (mais sans parallélisation). Du fait du nombre plus important de lignes (TOP 1000), l'optimiseur n'a pas opté pour la boucle imbriquée.

[olysmar2]

Merci beaucoup pour tous ces éclaircissement.
C'est trés clair et je comprend mieux pourquoi le coalesce résoud le problème.
Désolé de ne pas avoir été plus clair dans ma question et je ferais attention la prochaine fois.
Sylo