Conseil sur un traitement

**lerieure** · 29/03/2011, 10h12

Bonjour,

Je voudrais quelques conseilles sur un traitement que je dois effectuer.

un fichier Excel qui contient environ 5000 lignes doit être comite sur une table qui execute un trigger lourd (normalement quand j'ajoute une ligne le comite de la ligne dans la table qui execute le trigger dur environ 15-20 secondes) donc je me dis que l'insertion des 5000 lignes va être coton...

Ainsi je voudrais savoir si le mieux était de faire un agent SQL server (job) qui execute automatiquement l'importation du fichier excel dans cette table qui executera donc le trigger... Est-ce exacte ?

le problème est si le trigger dur 15-20secondes pour une ligne un comite de 5000 va le faire saturer...?

Peut-on faire une exécution ligne par ligne en temporisant pour ne pas avoir de problème d'importation ?? Si oui comment faire ?

Avez vous des tutoriels ou de la documentation pour Newbie sur les jobs ?
car j'ai rien trouvé de tuto pour les jobs... En revanche j'ai trouvé pour le SSIS.

Merci par avance pour vos réponses !

**aieeeuuuuu** · 29/03/2011, 12h54

15-20 secondes, c'est beaucoup pour un trigger...
La première chose à faire est de voir si vous ne pouvez vous en passer, au moins l'optimiser

Faire ligne par ligne sera sûrement pire que mieux, d'ailleurs, il est possible (en fonction de ce qu'il fait) que votre trigger mette a peine plus de temps pour 5000 lignes que pour une seule... avez vous testé avec un echantillon (une centaine de ligne par exemple, pour voir le temps, et évaluer le comportement du trigger lors de l'insertion de plusieurs lignes en meme temps)

Actuellement, comment (et par qui) est peuplée la table sur laquelle se trouve le trigger, et que fait le trigger (postez le code si vous le pouvez)

**elsuket** · 29/03/2011, 14h26

Bonjour,

Je suis entièrement d'accord avec aieeeuuuuu.

Votre trigger :

- est peut-être mal codé
- est peut-être optimisable
- doit manquer d'index pour supporter le requêtes qu'il exécute.

Donc, sans le code de celui-ci est la structure de la table à laquelle il est attaché, difficile de vous aider.

@++

**lerieure** · 29/03/2011, 18h15

Je ne peux pas poster le code de celui-ci malheureusement

Cependant, la table contient des règles (certaines règles sont plus spécifiques et d'autre plus générique) de génération de planning qui sont liées entre elles et qui s'autocompletent via le trigger pour générer un planning...

Il faut environ 5000 règles pour générer le planning et à chaque fois que j'ajoute modifie ou supprime une règle, je dois vérifier mes autres règles et regarder les dépendances, etc.

Cela permet d'éviter de la saisie redondante pour l'utilisateur. Et ça fait lui fait gagner des jours de saisies manuelles qui n'étaient pas très lucratives

Le problème n'est pas l'ajout d'une règle mais de l'ajout au départ des 5000 règles pour avoir un planning de base dans la BDD

**iberserk** · 30/03/2011, 08h39

Si votre TRIGGERest bien codé (de manière ensembliste et non par CURSOR par exemple).
Il ne durera peut-être pas beaucoup plus avec 5000 lignes car celles-ci seront traité en une seule fois...

Avez vous un plan d’exécution pour ce trigger?

**iberserk** · 30/03/2011, 11h04

voici le plan en image

Pardon et les stats I/O?

**lerieure** · 30/03/2011, 11h30

voici les plans d'exécution du trigger soit toutes les requêtes qu'il fait.

Je pense que c'est lourd aussi de traiter de ça dans un trigger

donc je me demande si il ne faudrait pas séparer les traitements du trigger en fragmentant la table et créer plusieurs tables et faire un enchainement de trigger.

Encore une fois je pense que c'est la nature de ce que doit faire le trigger qui est long (créer un planning et générer quantités de liaisons et de lignes par rapport à une seule ligne insérer par l'utilisateur).

**iberserk** · 30/03/2011, 11h53

Je pense que c'est lourd aussi de traiter de ça dans un trigger.

Votre TRIGGER est clairement très lourd et s'appuie sur de nombreuses tables temporaire.
Il vous manque également des index (TablesScans, dans une moindre mesure index scan)...

**lerieure** · 30/03/2011, 12h00

le trigger possède deux tables temporaires... pensez vous qu'il serait envisageable de créer ces tables temporaires en tables en créant un domaine "génération de règle" serait-il plus performant ?

Enfin pouvez vous m'en dire plus pour TablesScan scan ?

les tables possèdent des indexes sur les clés primaires et étrangères des tables et un indexe sur l'une des tables temporaires ? Est ce utile de faire un index sur une table temporaire dans un trigger ou la table sera supprimée à la fin de l'exécution du trigger ? A noter que le trigger travaille essentiellement sur les tables temporaires. Ne faudrait il pas mieux travailler sur de réelle table ?

Merci d'avance

**aieeeuuuuu** · 30/03/2011, 12h10

Difficile d'en dire plus sans le code, mais vous faites une boucle. (j'ai l'impression que vous faites pire qu'utiliser un curseur, vous en simulez le fonctionnement)

Je pense qu'il doit être possible de vous passer de votre boucle par quelques requêtes, qui ne seront peut être pas simples à écrire, mais qui seront très probablement beaucoup plus performantes.

**lerieure** · 30/03/2011, 12h11

ben j'ai fait des boucles pour éviter les curseurs en suivant ce tuto

http://sqlpro.developpez.com/cours/s...r_avoidCursor/

**aieeeuuuuu** · 30/03/2011, 12h18

oui, mais vous vous êtes basé sur la partie 3 du tuto j'ai l'impression. Cette partie utilise une table temporaire, pour lancer une commande qui ne peut concerner qu'une table (recalcul des statistiques)

Je ne pense pas que cela soit votre cas, et vous devriez plutôt faire comme dans la partie 1 (ou la partie 2)

**iberserk** · 30/03/2011, 13h43

Je ne peux pas poster le code de celui-ci malheureusement

Pour des raisons de confidentialité?

Dans ce cas modifiez vos requètes en renommant des champs en nommant vos tables A et B etc. que nous puission refaires celles-ci

**lerieure** · 30/03/2011, 14h55

Pour des raisons de confidentialité?

voici une partie je ne peux pas mettre plus.

Mais si vous me dites comment changer la boucle en requête ou d'autre optimisation considérable sur ce code. Je pense que ça sera pas mal

**aieeeuuuuu** · 30/03/2011, 15h46

Envoyé par lerieure

voici une partie je ne peux pas mettre plus.

831 lignes... c'est déjà pas mal

Je ne pensais pas si bien dire quand je disais que ce ne serait sans doute pas simple de passer tout ça en quelques requêtes

Disons qu'il y a un peu de travail...

**iberserk** · 30/03/2011, 15h52

Mais si vous me dites comment changer la boucle en requête ou d'autre optimisation considérable sur ce code. Je pense que ça sera pas mal

O çà tombe mal je dois partir en vacance pendant deux mois

Non sérieusement ça va être coton de "rentrer" dans votre code!
Des INSERT des UPDATE des TOP 1 dans tout les sens sur vos table TEMP...

Et peu de commentaire...

**aieeeuuuuu** · 30/03/2011, 16h18

moi non plus je ne peux pas le faire, je dois partir en vacances dans deux mois

cela dit, j'ai bien l'impression que votre trigger ne gérera pas les insertions multilignes :

Code SQL :

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

1
2
3
4
 
SELECT TOP 1 
...
FROM INSERTED

**iberserk** · 30/03/2011, 16h26

moi non plus je ne peux pas le faire, je dois partir en vacances dans deux mois

Parfait... vous commencez la refonte pendant ces deux prochain mois et je prends la suite à mon retour

... quand on peut s'arranger...

**lerieure** · 31/03/2011, 14h49

Difficile d'en dire plus sans le code, mais vous faites une boucle. (j'ai l'impression que vous faites pire qu'utiliser un curseur, vous en simulez le fonctionnement)

Je pense qu'il doit être possible de vous passer de votre boucle par quelques requêtes, qui ne seront peut être pas simples à écrire, mais qui seront très probablement beaucoup plus performantes.

Je vais regarder si c'est possible d'optimiser le traitement

mais en terme de performance c'est mieux une boucle ou un curseur?
après je me doute que c'est mieux une requête simple mais si c'est pas possible c'est quoi le mieux ?