Discussion :

[hatman-UTC]

Bonjour,

Je travaille actuellement sur une base de données SQL Server 2008 contenant différentes données. Cette base contient entre autre des objets complexes sous la forme suivante :
- une table contient les caractéristiques basiques de l'objet (nom, type, etc..)
- six autres tables contiennent les éléments susceptibles d'être contenus dans l'objet complexe (une table contenant tous les éléments de type A, une autre tous les éléments de types B, etc...)
- enfin, six tables de jointure font le lien entre l'objet complexe et les éléments qu'il contient

Ces 13 tables permettent donc de savoir qu'un objet complexe X contient 10 éléments A, 50 éléments B, etc...

J'ai mis en place une procédure stockée permettant de supprimer un objet complexe. Cette procédure va donc supprimer l'objet et va aussi effectuer un nettoyage dans les tables d'éléments et les tables de jointures. Les références vont être supprimées des tables de jointures et les éléments n'étant contenus que dans l'objet X que l'on cherche à supprimer le seront également. Les éléments partagés par un objet X et un objet Y resteront dans la base jusqu'à ce que l'objet Y soit supprimé à son tour.

J'ai également mis en places des procédures permettant d'insérer facilement dans la base de nouveaux objets.

J'ai expérimentés différents scénarios et j'avoue que certains d'entre eux me laissent perplexes :
- lorsque je supprime à l'aide de ma procédure un objet X de la base puis un objet Y, la procédure s'exécute à chaque fois en 1s.
- lorsque je supprime à l'aide de ma procédure un objet X, que je le réinsère, puis que je supprime un objet Y, la procédure de suppression s'exécute à chaque fois en 1s.
- lorsque je supprime à l'aide de ma procédure un objet X, que je le réinsère, puis que je supprime à nouveau cet objet X, la procédure de suppression s'exécute cette fois-ci durant un laps de temps compris entre 1 et 4mn !

J'avoue ne pas comprendre cette lenteur soudaine. J'ai tenté de laisser "souffler" le serveur entre ma suppression/insertion et ma nouvelle suppression, rien n'y a fait. Reconstruire les index n'a rien changé non plus. Auriez-vous une idée de ce qui pourrait être la cause de ce ralentissement soudain ?

Merci d'avance pour votre aide
Cdlt

Hatman

[kagemaru]

bonjour,

il faudrait poster le code et la structure des tables si possible. A vue de nez, ces lenteurs pourraient venir du fait que les index clustered doivent être ordonnés lors de cette réinsertion (si le même identifiant est conservé).... mais c'est difficile sans idée précise de votre situation.

merci

[hatman-UTC]

Bonjour,

Je ne peux malheureusement pas poster le schéma de ma base en elle même car il contient des informations confidentielles. Voici un schéma "type" s'en approchant :

Différents type d'éléments existent, chacun contenus dans une table les jointure se faisant par le biais de tables de jointure comme sur le schéma. Votre remarque sur les index me semble pertinente mais j'avoue ne pas être spécialisé en SGBD (mon profil est plus "générique"). Mes tables utilisent quasiment toutes des clefs artificielles donc les mêmes identifiants ne sont pas réutilisés. J'ai par contre des index autres que ceux de clefs primaires sur certaines tables. Par exemple, sur la table ElementB, j'ai ajouté un index (non unique, non cluster) sur les champs Name & Number. J'ai également sur chaque clef des index cluster (mis en place automatiquement par le système).

J'ai essayé, entre la ré-insertion et la seconde suppression, de reconstruire les index de mes tables à l'aide d'instructions du type

Code :

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

1
2
ALTER INDEX ALL ON ElementA
REBUILD;

mais cela n'a changé en rien ma lenteur d'exécution.
Ma procédure de suppression a donc pour but de supprimer un ObjetComplex de la base, de supprimer toutes les informations de jointure et de supprimer des tables d'éléments, les éléments qui n'étaient contenus que dans l'Objetcomplex que l'on supprime. Les éléments contenus à la fois dans une Objet A et un Objet B ne doivent bien entendu pas être supprimées lorsque l'on supprime A mais pas B. La procédure de suppression utilise des tables temporaire pour stocker des informations à supprimer du type :

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
 
IF OBJECT_ID(N'tempdb..#ElementB', N'U') IS NOT NULL 
		DROP TABLE #ElementB;
 
		SELECT Oe.ElementBKey
		INTO #ElementB
		FROM JoinObjetComplexElementB AS Oe
		WHERE NOT EXISTS (
			SELECT o.ObjetComplexKey
			FROM JoinObjetComplexElementB AS 0
			WHERE o.ObjetComplexKey <> @ClefDeLobjetASupprimer AND o.ElementBKey= Oe.ElementBKey
		)
 
		DELETE FROM JoinObjetComplexElementB 
		WHERE ElementBKey IN (
			SELECT ElementBKey
			FROM #ElementB
		)
 
		DELETE FROM ElementB
		WHERE ElementBKey IN (
			SELECT ElementBKey
			FROM #ElementB
		)

mais il ne me semble pas que la lenteur provienne de ces tables puisque lorsque je supprime à la suite deux Objets Complexes différents, l'exécution se passe rapidement.

Cela vous éclaire t-il ? Comment puis améliorer mes index ? J'avoue que la notion de cluster/non-cluster/XML Primaire/Spatial m'est quelque peu absconse. Je vais essayer de me renseigner de mon coté mais si vous pouviez m'éclairer...

Merci d'avance pour votre temps et votre aide,
cdlt

Hatman

[Sergejack]

Pourquoi ne pas employer ON DELETE CASCADE sur ces différentes table liés par des clés étrangères ?

[hatman-UTC]

Effectivement, je pourrais mettre en place des ON DELETE CASCADE sur les tables d'éléments afin d'éviter l'utilisation des tables temporaires. Cela permettra t-il d'éviter les ralentissements ? Je vais tester cette solution.

[hatman-UTC]

J'ai mis en place des ON DELETE CASCADE sur toutes les tables concernées, ai pu donc supprimer les tables temporaires de ma procédure, mais le problème persiste quand même avec ma nouvelle procédure ! Si quelqu'un à une piste, je suis preneur !
Merci

Hatman

[ATMorphine]

As tu envisagé :
De regarder du coté des lock ?
En gros tu locks toutes tes tables le temps du delete.

[hatman-UTC]

Je vais regarder de ce coté... Mais je ne pense pas que cela soit un problème d'accès concurrents, le problème survient alors que je suis le seul à interagir avec ma base de test !
Merci pour le conseil,
cdlt

Hatman

[kagemaru]

hello

Quelques questions/suggestions :

En terme de volumes, combien de lignes dans les tables ?

Est-ce que tu peux identifier si la procédure ce sont les DELETE ou le SELECT qui sont lents ? Tu peux utiliser le profiler ou la commande sp_who2 pour regarder quelle commande prend du temps.

Pour finir, est-ce que les tables JoinObjetComplexElementX ont un index avec
ElementXKey comme première colonne ? Cela peut également jouer sur la rapidité de la suppression...

Code :

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

1
2
3
4
5
6
DELETE FROM JoinObjetComplexElementB 
		WHERE ElementBKey IN (
			SELECT ElementBKey
			FROM #ElementB
		)

[hatman-UTC]

Bonjour,

Nombres de lignes dans la table :
- environ 150 dans la table Objetcomplex
- entre 4000 et 100000 dans les tables de jointures
- entre 150 et 8000 dans les tables d'éléments

Les tables JoinObjetComplexElement ont un index sur deux colonnes, la première étant la colonne ObjetComplexKey, la seconde étant l'ElementKey.

Je vais tenter d'identifier le lieu du problème à la main, le profiler ayant lamentablement planté Management Studio à chaque fois que j'ai tenté de l'utiliser sur ce problème...
Merci

Hatman

[SQLpro]

1) Supprimez l'usage de la table temporaire et utilisez la clause OUTPUT pour récupérer les lignes impactées par la première suppression.
2) Vérifiez que vous avez bien les bons index
3) préfixez vos tables.

Exemple :

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
        DECLARE @T TABLE (ElementBKey ???);
 
		DELETE FROM dbo.JoinObjetComplexElementB
        FROM dbo.JoinObjetComplexElementB AS JCE
             INNER JOIN dbo.JoinObjetComplexElementB AS Oe
                   ON  JCE.ElementBKey = Oe.ElementBKey
        WHERE NOT EXISTS (SELECT *
			              FROM   dbo.JoinObjetComplexElementB AS o
			              WHERE  o.ObjetComplexKey <> @ClefDeLobjetASupprimer 
                            AND  o.ElementBKey= Oe.ElementBKey)
        OUTPUT deleted.ElementBKey INTO @T;
 
		DELETE FROM dbo.ElementB
		WHERE ElementBKey IN (SELECT ElementBKey
                              FROM   @T);

Index (s'il n'y sont pas déjà) :

Code :

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

1
2
CREATE INDEX X123 ON dbo.JoinObjetComplexElementB (ObjetComplexKey, ElementBKey);
CREATE INDEX X234 ON dbo.ElementB (ElementBKey);

A +

[hatman-UTC]

Oups, il semblerait que je n'ai pas donné les bon derniers renseignements :
- suite aux conseils reçus sur ce forum, j'utilise des DELETE ON CASCADE SUR mes tables et non plus des tables temporaires
- sur toutes les tables, j'ai des index cluster sur la/les clefs primaire, et donc sur toutes les tables de jointure, j'ai un index du type dbo.JoinObjetComplexElementX (ObjetComplexKey, ElementXKey).

Merci en tout cas pour la syntaxe de la clause OUTPUT, je ne connaissais pas, et j'utiliserais à l'avenir.
Malgré tous vos bons conseils, le problème perdure toujours, je vais tenter le profiling précis pour trouver de quelle(s) instruction(s) précise(s) vient le problème et vous tient au courant.
Cdlt

Hatman

[SQLpro]

Y a t-il des déclencheurs DELETE sur ces tables ?
La base de données est-elle en mode AUTOSHRINK ?

A +

[hatman-UTC]

Aucun trigger "on delete". La base n'est pas en autoshrink..
J'ai exécuté un "ALTER DATABASE MaBase SET AUTO_SHRINK OFF" par acquis de conscience et ai re-testé mais rien n'y a fait...

J'aurais aimé pourtant que ce soit ça !

[SQLpro]

Avez-vous généré les fichiers de la base sur un répertoire compressé ?
Avez-vous généré les fichiers de la base sur une ressources distante (lecteur mappé/NAS) ?
Utilisez vous un anti virus sur la machine ?

A +

[SQLpro]

Autre chose :
êtes vous en serveur virtuel ?
êtes vous en RAID 5 ?

A +

[hatman-UTC]

Le problème survient alors que ma base de test tourne sous SQL Server 2008, instance lancée sur mon poste local, disque dur SCSI 10 000trs/min unique et OS XP SP3 natif.
Merci

Hatman

[kagemaru]

Je vais tenter d'identifier le lieu du problème à la main, le profiler ayant lamentablement planté Management Studio à chaque fois que j'ai tenté de l'utiliser sur ce problème...

Essayer d'utiliser le profiler depuis une autre machine sinon, il serait vraiment interressant de voir la liste des requêtes et leur consommation.

[SQLpro]

Vous ne pouvez pas baser vos tests sur une machine desktop. En effet SQL Server comme tout bon SGBDR doit être installé sur une machine dédié sans aucun autre service, application ou utilitaire actif : pas d'anti virus, de firewall, de serveur web....

De plus le fait que le profiler SQL déconne indique vraisemblablement pour le moins un manque de ressources système (RAM notament), et pour le pire des dégâts au niveau fichiers ou autres.
Sachez que pour faire tourner SQL Server + son environnement de développement sur un desktop, il faut un minimum de 2 Go, mais si vous avez ouvert des applications gourmandes (Outlook, Word, Visual Studio...) alors c'est plutôt 4 à 8 Go de RAM qu'il faut !!!!!!!!

A +

[hatman-UTC]

Je suis bien conscient des limitations de mon environnement de test actuel. Celui-ci n'est que temporaire, je suis en attente d'une mise à disposition d'une serveur SQL dédié répondant à tous les pré-requis que vous citez. En attendant, je pensais pouvoir utiliser mon poste local doté d'une configuration décente (4Go de RAM, processeur Xeon, Disque 10 000trs/min).

Le problème est que le serveur ne me sera pas livré avant un mois... Je vais essayer de voir si je peux accélérer les choses !
Quoiqu'il en soit, merci pour vos réponses
Cdlt

Hatman