none
TimeOut lors de gros traitement RRS feed

  • Question

  • Bonjour,

    Lors du passage d’un script effectuant de nombreux « insert » (via un cursor dans du transac) a partir d’une application VB.NET (framework 2.0) une erreur TimeOut apparait au bout de 10 minutes environ. Le même traitement sur la même base sur notre serveur ne pose pas de problème.

    La base a été migré de SQL Server 2000 vers SQL 2005. Il  ne semble pas que le problème soit du à la puissance du serveur celui-ci étant plus puissant que le notre. De plus, le problème est apparu chez plusieurs clients. Enfin, les valeurs des propriétés « ConnectionTimeout » et « CommandTimeout » utilisé sur la connexion ADO.NET sont énorme.

    Avez-vous des pistes qui pourraient expliquer le problème ?

    D’avance merci.

    Cordialement,

    lundi 24 août 2009 12:57

Réponses

  • Effectivement comme le dit Papy Normand, l'utilisation des curseurs est à éviter car les performances ne sont pas au rendez vous. Dans la plupart des cas avec SQL il est possible de remplacer un traitement itératif à base de curseur par un traitement ensembliste beaucoup plus performant.

    En reprenant votre code une solution serait :

    DECLARE @id INT;
    -- Table de travail pour génération des ID 
    DECLARE @work_table TABLE
    (
     ID_DROIT_DOC_GROUPE NUMERIC(16) IDENTITY(1,1),
     ID_GROUPE numeric(16),
     ID_DOCUMENT numeric(16)
    );
    -- Récupération ID
    SELECT @id = VAL_SEQUENCE + 1
    FROM Z_SEQUENCE
    WHERE N_SEQUENCE = 'SEQ_DROIT_DOC_GROUPE';
    -- Génération des incréments d'ID dans une table de travail
    INSERT INTO @work_table (ID_GROUPE,ID_DOCUMENT)
    SELECT ID_GROUPE,ID_DOCUMENT
    FROM DOC_DROIT_GRP
    GROUP BY ID_GROUPE,ID_DOCUMENT;
    -- Insertion des enregistrements dans la table de destination finale avec les ID finaux
    INSERT INTO DROIT_DOC_GROUPE(ID_DROIT_DOC_GROUPE,ID_GROUPE,ID_DOCUMENT,B_ADM_DOC_ADM,B_ADM_DOC_MANA,B_CONT_DOC_ADM,B_CONT_DOC_MANA,
            B_ADDFILS_DOC_ADM,B_ADDFILS_DOC_MANA,B_CONS_DOC_ADM,B_CONS_DOC_MANA,B_CONS_DOC_VISU,B_INF_DOC_ADM,
            B_INF_DOC_MANA,B_INF_DOC_VISU,B_NOTIF_DOC_ADM,B_NOTIF_DOC_MANA,B_NOTIF_DOC_VISU,B_IMP_DOC_ADM,
            B_IMP_DOC_MANA,B_IMP_DOC_VISU)
    SELECT ID_DROIT_DOC_GROUPE + @id,ID_GROUPE,ID_DOCUMENT,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
    FROM @work_table;
    -- Mise à jour ID de séquence
    UPDATE Z_SEQUENCE 
    SET VAL_SEQUENCE = (SELECT MAX(ID_DROIT_DOC_GROUPE) + @id FROM @work_table)
    WHERE N_SEQUENCE = 'SEQ_DROIT_DOC_GROUPE';

    ++


    MCDBA | MCITP SQL Server 2005 | MCTS SQL Server 2008 | LPI Linux 1

    • Marqué comme réponse Mich44 mercredi 2 septembre 2009 07:08
    mardi 1 septembre 2009 18:37
    Modérateur

Toutes les réponses

  • Bonjour,

    Pour pouvoir vous répondre, pouvez nous nous donner le bout de code qui vous pose problème ?

    ++


    MCDBA | MCITP SQL Server 2005 | MCTS SQL Server 2008 | LPI Linux 1
    jeudi 27 août 2009 16:52
    Modérateur
  • Bonjour,

    Le Timeout sur des séries d'insert est un problème courant.
    Il peut avoir plusieurs origines dont les 2 principales sont :
    - une valeur insuffisante pour CommandTimeout ( ce qui ne semble pas être le cas pour vous )
    - plus vicieux, mais très simple à résoudre : un problème sur votre fichier log de transaction.
    Chaque insert est inscrit dans votre fichier log ( SQL Server extension .ldf ). Or par défaut, l'accroissement de taille ( pour SQL Server 2005 et 2008 ) est par défaut de 2 Mo ( en réalité, il est égal à celui défini dans la base systeme model ). Chaque augmentation de taille est une opération coûteuse en temps et ce temps est pris en compte dans le CommandTimeout. De plus, le temps nécessaire pour faire cette augmentation est très dépendant des IO sur le disque physique contenant ce log file et peut être très long , suffisamment pour entrainer un timout sur des inserts

    Pourriez-vous regarder la valeur d'accroissement de taille de votre logfile et aussi la taille initiale de votre logfile avant le lancement de votre script et après le timeout ?
    Il y a eu un thread assez célèbre dans les forums us à ce sujet avec plus de 40 posts en 2008 ( en fait , il a même été coupé en 2 pour le rendre plus clair )
    Si cela vous intéresse, je peux retrouver  la référence de ce thread

    Au fait, pourquoi utilisez-vous un curseur ? Ce n'est pas forcément très conseillé ( je crois même que c'est déconseillé avec SQL Server 2008 )

    Nous attendons votre feedback pour essayer de vous aider plus efficacement

    Bonne journée

    PS : La demande de mikedavem est très importante car elle permettrait d'expliquer la différence d'execution entre les 2 versions 2000 et 2005 de vos SQL Server 
    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    vendredi 28 août 2009 22:20

  • Bonjour,

    Merci pour vos réponses, cela me donne déjà quelques pistes.

    Malgré tout, le « CommandTimeout » devait être de l’ordre de 1 heure et le timeout chez le client apparaissait toujours au environ de 10 minutes.

    Malheureusement la migration de la base étant maintenant effectuée je n’ai pas les informations souhaitées (valeur d'accroissement de taille du logfile et aussi la taille initiale du logfile avant le lancement du script et après le timeout)

    Comme vous me l’avez demandé, voici le code exécuté (je ne sais pas exactement pourquoi il a été écrit comme cela (INSERT puis mise a jour de la valeur de chaque champ ?). La volumétrie est de plusieurs centaines de millier de lignes.

    DECLARE @vID_GROUPE numeric(16), @vID_DOCUMENT numeric(16), @id numeric(16)
    DECLARE cur_grp CURSOR LOCAL FOR
      select ID_GROUPE,ID_DOCUMENT
      		 from DOC_DROIT_GRP
    		 group by ID_GROUPE,ID_DOCUMENT
    select @id = VAL_SEQUENCE + 1
      from Z_SEQUENCE
     where N_SEQUENCE = 'SEQ_DROIT_DOC_GROUPE'
    OPEN cur_grp
      FETCH NEXT FROM cur_grp
          INTO @vID_GROUPE,@vID_DOCUMENT
      WHILE @@FETCH_STATUS = 0
      BEGIN
        insert into DROIT_DOC_GROUPE
                    (ID_DROIT_DOC_GROUPE,ID_GROUPE,ID_DOCUMENT,B_ADM_DOC_ADM,B_ADM_DOC_MANA,B_CONT_DOC_ADM,B_CONT_DOC_MANA,B_ADDFILS_DOC_ADM,B_ADDFILS_DOC_MANA,B_CONS_DOC_ADM,B_CONS_DOC_MANA,B_CONS_DOC_VISU,B_INF_DOC_ADM,B_INF_DOC_MANA,B_INF_DOC_VISU,B_NOTIF_DOC_ADM,B_NOTIF_DOC_MANA,B_NOTIF_DOC_VISU,B_IMP_DOC_ADM,B_IMP_DOC_MANA,B_IMP_DOC_VISU)
            values(@id, @vID_GROUPE, @vID_DOCUMENT,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
        set @id = @id + 1
      FETCH NEXT FROM cur_grp
          INTO @vID_GROUPE, @vID_DOCUMENT
      END
    CLOSE cur_grp
    DEALLOCATE cur_grp
     UPDATE Z_SEQUENCE SET VAL_SEQUENCE = @id WHERE N_SEQUENCE = 'SEQ_DROIT_DOC_GROUPE'
    go
    
    


    Le message d’erreur était :

    Erreur >= Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

    Sur un serveur Français :

    Erreur >= Expiration du délai d'attente. Le délai d'attente s'est écoulé avant la fin de l'opération ou le serveur ne répond pas.


    lundi 31 août 2009 09:41
  • Effectivement comme le dit Papy Normand, l'utilisation des curseurs est à éviter car les performances ne sont pas au rendez vous. Dans la plupart des cas avec SQL il est possible de remplacer un traitement itératif à base de curseur par un traitement ensembliste beaucoup plus performant.

    En reprenant votre code une solution serait :

    DECLARE @id INT;
    -- Table de travail pour génération des ID 
    DECLARE @work_table TABLE
    (
     ID_DROIT_DOC_GROUPE NUMERIC(16) IDENTITY(1,1),
     ID_GROUPE numeric(16),
     ID_DOCUMENT numeric(16)
    );
    -- Récupération ID
    SELECT @id = VAL_SEQUENCE + 1
    FROM Z_SEQUENCE
    WHERE N_SEQUENCE = 'SEQ_DROIT_DOC_GROUPE';
    -- Génération des incréments d'ID dans une table de travail
    INSERT INTO @work_table (ID_GROUPE,ID_DOCUMENT)
    SELECT ID_GROUPE,ID_DOCUMENT
    FROM DOC_DROIT_GRP
    GROUP BY ID_GROUPE,ID_DOCUMENT;
    -- Insertion des enregistrements dans la table de destination finale avec les ID finaux
    INSERT INTO DROIT_DOC_GROUPE(ID_DROIT_DOC_GROUPE,ID_GROUPE,ID_DOCUMENT,B_ADM_DOC_ADM,B_ADM_DOC_MANA,B_CONT_DOC_ADM,B_CONT_DOC_MANA,
            B_ADDFILS_DOC_ADM,B_ADDFILS_DOC_MANA,B_CONS_DOC_ADM,B_CONS_DOC_MANA,B_CONS_DOC_VISU,B_INF_DOC_ADM,
            B_INF_DOC_MANA,B_INF_DOC_VISU,B_NOTIF_DOC_ADM,B_NOTIF_DOC_MANA,B_NOTIF_DOC_VISU,B_IMP_DOC_ADM,
            B_IMP_DOC_MANA,B_IMP_DOC_VISU)
    SELECT ID_DROIT_DOC_GROUPE + @id,ID_GROUPE,ID_DOCUMENT,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
    FROM @work_table;
    -- Mise à jour ID de séquence
    UPDATE Z_SEQUENCE 
    SET VAL_SEQUENCE = (SELECT MAX(ID_DROIT_DOC_GROUPE) + @id FROM @work_table)
    WHERE N_SEQUENCE = 'SEQ_DROIT_DOC_GROUPE';

    ++


    MCDBA | MCITP SQL Server 2005 | MCTS SQL Server 2008 | LPI Linux 1

    • Marqué comme réponse Mich44 mercredi 2 septembre 2009 07:08
    mardi 1 septembre 2009 18:37
    Modérateur