[SQL 2000] Problème de perf sur Order

**zooffy** · 26/10/2007, 10h01

SAlut

Je suis confronté à un souci que je n'arrive pas à explqieur : j'ai une reuqête de sélection classique qui met 3 secondes à me retourner un jeu de résultat de 26000 lignes avec des clauses WHERE.

Mais lorsque je rajoute un order by sur un champ 'Nom' qui est du texte, ça mets plus de trois minitues à sortir et encore, je ne suis pas allé jusqu'au bout.

J'ai ajouté un Index sur Nom, dans la table d'origine, mais rien n'y fait.

Pouvez vous m'aider ?

**kagemaru** · 26/10/2007, 21h32

bonjour,

cela dépend également de la taille de la table lue. Il faudrait voir la structure de la table, ses index et la requête lancée.

**WOLO Laurent** · 27/10/2007, 15h51

Est ce que vous pouvez poster le plan d'exécution de la requete et le script de création de ola table et des objets rattachés ?

**zooffy** · 29/10/2007, 10h19

Bonjour

Alors voilà la 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
 
SELECT TOP 1000000
M.Id_Locad, M.id_membre,M.zzzage,
M.Nom, M.Prenom, M.Adresse, M.Complement, V.NomVille, V.CPVille,
M.Telephone, M.Portable, M.Date_Inscription, M.Date_Inactivation, M.Date_Last_Modif,
 
ISNULL(DATEDIFF(yyyy, M.Date_Naissance, GETDATE()),0) AS Age, 
M.Sexe, M.Id_Profession, M.Id_Secteur, M.Id_Situation, M.Nb_Enfants, M.Id_Provenance,M.email,M.date_naissance,
 
V.IDDep AS Departement, V.IdRegion as Region
FROM MEMBRES M
INNER JOIN devbv.Centrale.dbo.ListingVille V ON V.IdDecoupage = M.Id_Ville
WHERE M.Id_Locad NOT IN(SELECT DISTINCT Id_Membre FROM Motif_Membre)
ORDER BY Nom, Prenom

Voilà la structure de la table

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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
 
CREATE TABLE [dbo].[MEMBRES] (
	[Id_Locad] [int] NOT NULL ,
	[Id_Membre] [int] NULL ,
	[Id_Civilite] [int] NULL ,
	[Nom] [varchar] (50) COLLATE French_CI_AS NULL ,
	[Prenom] [varchar] (50) COLLATE French_CI_AS NULL ,
	[PrenomSalt] [varchar] (50) COLLATE French_CI_AS NULL ,
	[Adresse] [varchar] (150) COLLATE French_CI_AS NULL ,
	[Complement] [varchar] (150) COLLATE French_CI_AS NULL ,
	[Id_Ville] [int] NULL ,
	[Ville] [varchar] (100) COLLATE French_CI_AS NULL ,
	[VilleSalt] [varchar] (100) COLLATE French_CI_AS NULL ,
	[Telephone] [varchar] (20) COLLATE French_CI_AS NULL ,
	[Portable] [varchar] (20) COLLATE French_CI_AS NULL ,
	[Fax] [varchar] (20) COLLATE French_CI_AS NULL ,
	[Email] [varchar] (50) COLLATE French_CI_AS NULL ,
	[Sexe] [varchar] (1) COLLATE French_CI_AS NULL ,
	[Date_Naissance] [datetime] NULL ,
	[Lieu_Naissance] [varchar] (50) COLLATE French_CI_AS NULL ,
	[Id_Situation] [int] NULL ,
	[Nb_Enfants] [int] NULL ,
	[Id_Profession] [int] NULL ,
	[Id_Secteur] [int] NULL ,
	[Id_Provenance] [int] NULL ,
	[Date_Inscription] [datetime] NULL ,
	[Date_Last_Modif] [datetime] NULL ,
	[Date_Inactivation] [datetime] NULL ,
	[Date_Reactivation] [datetime] NULL ,
	[zzzid_Verification] [int] NULL ,
	[zzzid_erreur_nom] [int] NULL ,
	[zzzid_erreur_region] [int] NULL ,
	[zzzid_erreur_departement] [int] NULL ,
	[zzzid_erreur_pays] [int] NULL ,
	[zzzid_erreur] [int] NULL ,
	[zzzid_erreur_commune] [int] NULL ,
	[zzzCP_commune] [varchar] (20) COLLATE French_CI_AS NULL ,
	[zzzId_Pays] [int] NULL ,
	[zzzid_departement] [int] NULL ,
	[zzzid_region] [int] NULL ,
	[zzzMotif_Inactivation] [varchar] (1000) COLLATE French_CI_AS NULL ,
	[zzzId_Jonction] [int] NULL ,
	[zzzage] [int] NULL ,
	[zzzId_Statut] [int] NULL 
) ON [PRIMARY]
GO
 
 CREATE  INDEX [IX_MEMBRES_Nom] ON [dbo].[MEMBRES]([Nom]) ON [PRIMARY]
GO
 
 
CREATE TRIGGER [UPD_Date_Modif] ON [dbo].[MEMBRES] 
FOR UPDATE
AS
UPDATE MEMBRES SET Date_Last_Modif = GetDate()
FROM MEMBRES M
JOIN INSERTED I ON I.Id_Locad = M.Id_Locad

Et voilà la structure de la Vue ListingVille

Code :

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

1
2
3
4
5
6
7
 
SELECT G.NMC_ID AS IdDecoupage, G.Numero_Decoupage AS CPVille, G.NMC_LIBELLE AS NomVille, 
D.NMC_ID AS IDDep, D.NMC_LIBELLE AS NomDepartement, 
R.NMC_ID AS IDRegion, R.NMC_LIBELLE AS NomRegion FROM GeoPolitique G
INNER JOIN GeoPolitique D ON D.NMC_NIVEAU = 3 AND D.NMC_BG < G.NMC_BG AND D.NMC_BD > G.NMC_BD
INNER JOIN GeoPolitique R ON R.NMC_NIVEAU = 2 AND R.NMC_BG < G.NMC_BG AND R.NMC_BD > G.NMC_BD
WHERE G.NMC_NIVEAU = 5

La table GeoPolitique de cette vue est faite selon le tuto de SQLPro sur la gestion des arbres intervallaires.

Pour le plan d'exécution, je sais pas envoyer une image sur un topic.

Merci pour votre aide.

**Pol63** · 29/10/2007, 11h18

je suis pas expert mais je crois que le TOP n'est pas quelque chose de recommandé, ni de sûr (il prendrait des lignes au hasard sans suivre de tri)

**WOLO Laurent** · 29/10/2007, 14h16

Ajoute un index sur le champ personne et remplace le not in par une jointure normalisée.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT TOP 1000000
M.Id_Locad, M.id_membre,M.zzzage,
M.Nom, M.Prenom, M.Adresse, M.Complement, V.NomVille, V.CPVille,
M.Telephone, M.Portable, M.Date_Inscription, M.Date_Inactivation, M.Date_Last_Modif,
 
ISNULL(DATEDIFF(yyyy, M.Date_Naissance, GETDATE()),0) AS Age, 
M.Sexe, M.Id_Profession, M.Id_Secteur, M.Id_Situation, M.Nb_Enfants, M.Id_Provenance,M.email,M.date_naissance,
 
V.IDDep AS Departement, V.IdRegion AS Region
FROM MEMBRES M JOIN devbv.Centrale.dbo.ListingVille V 
         ON V.IdDecoupage = M.Id_Ville 
                         JOIN MOTIF_MEMBRE MO
         ON M.Id_Locad<>MO.Id_Membre  
ORDER BY Nom, Prenom

Pour ajouter les pièces jointes, allez à gérer les pièces-jointes.

**zooffy** · 29/10/2007, 14h36

Merci pour votre aide.
Alors reprenons les points en détail :
- le TOP est placé à 1000000 car la table ne fera pas autant de ligne avant plusisuers années, il sera de faire aurte chose à ce moment là. En fait il est là pour l'enregistrer dans la vue, sinon, ça marche aps avec le ORDER BY.
- Ajouter un index sur le champ personne : oui, mais j'ai pas de champ "personne". Veux tu parler du champ "Prenom" ?
- Enfin, c'est quoi une jointure normalisée ? J'ai mis un NOT IN car je dois récupérer les enregistrements de MEMBRE dont l'ID_locad n'est pas dans la table Motif_membre et avec une jointure, j'ai pas su faire.

Enfin, pour l'image du plan d'exécution, je vais voir ce que je peux faire, j'ai trouvé le fameux bouton.

Merci

**WOLO Laurent** · 29/10/2007, 15h11

Envoyé par zooffy

Merci pour votre aide.
- Ajouter un index sur le champ personne : oui, mais j'ai pas de champ "personne". Veux tu parler du champ "Prenom" ?

Oui, c'est ce que j'allais dire.

Envoyé par zooffy

- Enfin, c'est quoi une jointure normalisée ? J'ai mis un NOT IN car je dois récupérer les enregistrements de MEMBRE dont l'ID_locad n'est pas dans la table Motif_membre et avec une jointure, j'ai pas su faire.
Merci

Une des règles d'écriture des requêtes hautes performance, est d'évité les not in, et les distinct tant qu'on le peut.
Une jointure normalisée est celle qui se base sur le mot clé JOIN conformement à la norme SQL.
Vous trouverez de plus amples informations sur le site de SqlPro à l'adresse :
Jointures normalisées

**Ptit_Dje** · 29/10/2007, 18h36

Envoyé par zooffy

Merci pour votre aide.
- le TOP est placé à 1000000 car la table ne fera pas autant de ligne avant plusisuers années, il sera de faire aurte chose à ce moment là. En fait il est là pour l'enregistrer dans la vue, sinon, ça marche aps avec le ORDER BY.

Salut,

Je te conseillerais d'utiliser top 100 percent, ce qui, même dans quelques années sera toujours vrai.

Dje

**zooffy** · 30/10/2007, 09h37

Merci pour votre aide.

Je vais changer mon TOP, je ne connaissais pas cette forme.
Et ensuite je vais lire le tuto de SQLPro sur les jointures, mais, aprce que ça je connais un peu, je ne vois vraiment pas comment faire une jointure pour ne travailler que sur les enregistrements qui ne sont pas dans la table de droite.

J'espère qu'aprés cette lecture je trouverais.

**Pol63** · 30/10/2007, 09h44

une jointure avec <> ca devrait aller ...

**zooffy** · 30/10/2007, 09h54

Merci Sperot51.

J'ai modifié ma requête en mettant ça au niveau du FROM

Code :

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

1
2
3
4
 
FROM MEMBRES M
INNER JOIN Motif_Membre MM ON MM.Id_Membre <> M.Id_Locad
INNER JOIN devbv.Centrale.dbo.ListingVille V ON V.IdDecoupage = M.Id_Ville

Et rien d'autre aprés (le ORDER, je verrais plus tard).

Mais je me retrouve avec un produit cartésien pur et dur, il me sort les mêmes enregistrements 180000 fois. Je n'arrive pas à comprendre cette quantité.

Je continue à chercher, mais si une idée vous vient........

**Pol63** · 30/10/2007, 09h56

il te faut un where pour dire les id que tu veux pas quand meme

et j'ai ptete dit une connerie aussi

**zooffy** · 30/10/2007, 10h07

Bon, j'ai trouvé (enfin je crois) pourquoi j'ai autant de truc.
Visiblement, avec la condition de jointure avec <> ça me retourne bien les enregistrements que je veux.
Sauf que ça me les repète 181026 fois chacun, donc des milions de ligne à la fin, puisque j'ai 236045 fiche à remonter et que tout ça se multiplie.

Dans ma table Motif_Membre j'ai 181027 enregistrement, je trouve bizarre cette différence de 1, amis je suis sûr qu'il y a une explication logique.

Enfin, mettre une clause WHERE, je veux bien, mais laquelle ? Parce que, si je suis le raisonnement de Laurent, il faut remplacer ma clause WHERE avec le NOT IN par une Jointure pour faire propre et performant.

Pour tenter de résumer mon besoin :
- j'ai une jointure que une table pour récupérer des info sur la ville du membre, ça marche visiblement bien
- il me faut une autre jointure pour trouver les enregistrements qui sont dans la table Membres ET PAS dans la table Motif_membre.

Le tout optimiser car je bosse pour l'instant sur 230 000 fiches, mais ce nombre va augmenter lorsque le projet sera en production.

Et, cerise sur le pudding, je souhaite un ORDER BY dans la Vue car c'est mon critère de tri de base que mets dans ma page du site qui appelle cette vue, toutefois, si on m'explique qu'il vaut mieux que je le mette dans l'appelle à partir de la page, je m'exécute.

En gros, j'attend une requête qui va me retourner les lignes en moins de 5 secondes et pour l'instant j'en suis à 17. Au niveau du serveur, le requête ne sature pas son process ou sa RAM.

**WOLO Laurent** · 30/10/2007, 12h37

Envoyé par zooffy

je ne vois vraiment pas comment faire une jointure pour ne travailler que sur les enregistrements qui ne sont pas dans la table de droite.

Regarde du côté de

Code :

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

RIGHT OUTER JOIN

**zooffy** · 30/10/2007, 13h45

J'ai essayé avec ça, amis rien à faire.
Soit ça me sort une redondance des fiche qui sont présente dans Motif_Membre, soit ça me sort un troncature d ela même chose, sur des critères que j'ai du mal à comprendre.

Mais jamais ça me sort les fiches qui ne sont pas dans Motif_Membres

Bref, j'y comprend plus rien ou alors il faut utiliser un opérateur que je ne connais pas.

**WOLO Laurent** · 30/10/2007, 14h49

Est ce que tu peux nous afficher le code ?

**zooffy** · 30/10/2007, 16h49

oui, sans problème

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT 
M.Id_Locad, M.id_membre,
--Champs d'affichage des résultats
M.Nom, M.Prenom, M.Adresse, M.Complement, V.NomVille, V.CPVille, M.Id_Ville, 
M.Telephone, M.Portable, M.Date_Inscription, M.Date_Inactivation, M.Date_Last_Modif,
--Champs pour gérer les critères
ISNULL(DATEDIFF(yyyy, M.Date_Naissance, GETDATE()),0) AS Age, 
M.Sexe, M.Id_Profession, M.Id_Secteur, M.Id_Situation, M.Nb_Enfants, M.Id_Provenance,M.email,M.date_naissance,
--Champ repris de GeoPolitique pour la recherche géographique
V.IDDep AS Departement, V.IdRegion as Region
FROM MEMBRES M
RIGHT OUTER JOIN Motif_Membre MM ON MM.Id_Membre <> M.Id_Locad
INNER JOIN devbv.Centrale.dbo.ListingVille V ON V.IdDecoupage = M.Id_Ville

Ce code me fait une duplication des membre que je ne veux pas.
Et si je mets un = à la place du <> dans le right outer join, il me fait une extraction bizarre de membre que je ne veux pas non plus, amis y en a moins.

Bref, je comprends pas.

**zooffy** · 31/10/2007, 12h05

Salut

Je reviens car je viens de mettre le doigt sur un autre souci, certainement lié. Je poste les plan d'exécution avec les requêtes au dessus, les deux iamge sont dans le fichier Word joint.

La différence entre la première et la deuxième est la clausse WHERE rajouté à la fin.
Il me mets plus de temps à remonter moins de lignes, je ne comprend pas pourquoi.

Sur cet exemple, c'est pas trés grave. Par contre, lorsque les critères ramménent plus de 3000 lignes, cela me génère un TimeOut sur le site et donc c'est trés gênant.

Merci pour votre aide.

**SQLpro** · 05/11/2007, 09h50

Vos tables SCAN montrent qu'aucun index n'est utilisé. De plus vous utilisez une table distante : devbv.Centrale.dbo.ListingVille
Or il est impossible d'obtenir une bonne perf avec une requête sur une table distante car les temps les plus longs seront les temps de transit réseau une fois que vous aurez transformé les SCAN en SEEK !

Commencez par mettre votre table diistante en local par un moyen quelconque : copie, réplication...

Ensuite attaquez vous aux lectures en regardant le plan mais aussi et surtout au nombre des IO :
SET SATTISTICS IO ON.

Inspirez vous du papier que je viens de poster sur le site de mon entreprise :
http://www.sqlspot.com/INDEXATION-un...l-exemple.html

Par exemple vérifiez si les index suivants existent :
GeoPolitique :
(NMC_NIVEAU, NMC_BG)
(NMC_BG, NMC_BD)
(NMC_BD)

Membre :
(idVille)
(Id_Locad, IdVille)

Motif_Membre :
(Id_Membre)

Sinon, commencez par les créer !

Enfin, comme il n'y a pas de filtre WHERE ni HAVING cela remonte toute les lignes... Ne vous étonnez pas que cela soit lent.... Qu'allez vous faire de 10000000000 lignes ???

Bref aucune possibilité d'optimiser votre requête sans revoir tout !

A +

[SQL 2000] Problème de perf sur Order

MS SQL Server

Discussions similaires

Partager

Partager