1. #1
    Nouveau Candidat au Club
    Homme Profil pro
    Chef de projet MOA
    Inscrit en
    mars 2012
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Homme

    Informations professionnelles :
    Activité : Chef de projet MOA
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : mars 2012
    Messages : 4
    Points : 1
    Points
    1

    Par défaut Comment optimiser un insert en masse à partir d'une table volumineuse

    bonjour à tous ,

    j'ai une table "table_src" avec des index qui fait + 40 millions de lignes et je dois modifier la structure de la table en ajoutant des colonnes.
    j'ai de gros problèmes de performance ça met 4 heures et ça plante car je rencontre des problèmes d'espace disque avec la base tempdb

    version sql = sql server 2008 R2 , la mémoire allouée à sql est de 2 Go et il reste 20 Go sur le serveur

    voici ce que j'ai fait :
    - la base est en mode simple
    - j'ai créé une nouvelle table table_dest avec la nouvelle structure avec la clé primaire

    SET NOCOUNT ON
    insert into dbo.table_dest (...) select .... from dbo.table_src

    - après l'insert j'ai créé les index sur la table table_dest.

    je ne sais pas si par exemple il est préférable de créer les index sur la table finale avant ou après l'insert
    j'aurai voulu faire un test en faisant des insertions par bloc pour maîtriser la taille de la base tempdb mais je ne sais pas comment faire.
    au final je ne sais pas trop comment m'y prendre , à part sinon de demander à l'infra d'augmenter la mémoire et l'espace disque du serveur mais ça va prendre des semaines.


    merci d'avance pour aide

  2. #2
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    16 876
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 16 876
    Points : 39 175
    Points
    39 175

    Par défaut

    Avec si peu de ram vous n'arriverez jamais. Les sgbdr traitant les données en mémoire il faut que l'intégralité des données de la table puise figurer en mémoire ainsi que la journalisation ... Commencez par mettre au moins 16 go de ramener à votre serveur et vous verrez que vous allez résoudre tous les problèmes : vitesse, temps et crash...
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  3. #3
    Nouveau Candidat au Club
    Homme Profil pro
    Chef de projet MOA
    Inscrit en
    mars 2012
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Homme

    Informations professionnelles :
    Activité : Chef de projet MOA
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : mars 2012
    Messages : 4
    Points : 1
    Points
    1

    Par défaut

    merci pour votre réponse , j'ai déjà fait la demande à notre infra pour avoir plus de mémoire...
    mais avant que ça soit fait ça va prendre du temps
    j'essaye de trouver une solution en parallèle

  4. #4
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    16 876
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 16 876
    Points : 39 175
    Points
    39 175

    Par défaut

    alors il faut le en splittant la table.

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  5. #5
    Nouveau Candidat au Club
    Homme Profil pro
    Chef de projet MOA
    Inscrit en
    mars 2012
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Homme

    Informations professionnelles :
    Activité : Chef de projet MOA
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : mars 2012
    Messages : 4
    Points : 1
    Points
    1

    Par défaut

    je suis parti sur cette idée

    merci pour votre aide

  6. #6
    Modérateur
    Avatar de elsuket
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    janvier 2005
    Messages
    5 737
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 35
    Localisation : France

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : janvier 2005
    Messages : 5 737
    Points : 11 713
    Points
    11 713

    Par défaut

    Bonjour,

    Effectivement avec 2Go cela va être difficile. Une fois que vous aurez plus de RAM, les performances (outre cet INSERT) s'en ressentiront immédiatement : les applications qui accèdent à la base de données vont "respirer"

    Vous pouvez procéder au peuplement de la nouvelle table par les lignes de l'ancienne en copiant les lignes par lots de 10 000, par 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
    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
    58
    59
    60
    61
    -- Extraction des valeurs de clé primaire subrogée de la table source
    SELECT	id
    INTO	dbo.table_auxiliaire
    FROM	dbo.table_source
     
    -- Ceci crée implicitement un index cluster
    -- qui va bien supporter les ORDER BY qui suivent
    ALTER TABLE dbo.table_auxiliaire
    ADD CONSTRAINT PK_table_auxiliaire PRIMARY KEY(id)
     
    ------------------------------------------------------------
     
    DECLARE @i int = 0
    	, @dt datetime
    	, @msg varchar(1024)
     
    WHILE EXISTS
    (
    	SELECT	*
    	FROM	dbo.table_auxiliaire
    )
    BEGIN
    	SELECT	@i += 1
    		, @dt = GETDATE();
     
    	-- On copie les 10000 lignes suivantes dans la table cible ...
    	WITH
    		CTE AS
    		(
    			SELECT	TOP 10000 id
    			FROM	dbo.table_auxiliaire
    			ORDER	BY id
    		)
    	INSERT INTO dbo.table_cible
    	(
    		id
    		, les_autres_colonnes
    	)
    	SELECT		S.id
    			, S.les_autres_colonnes
    	FROM		CTE AS C
    	INNER JOIN	dbo.table_source AS S
    				ON C.id = S.id;
     
    	-- ... une fois qu'elles sont traitées, on peut les supprimer de la table auxiliaire
    	WITH
    		CTE AS
    		(
    			SELECT	TOP 10000 id
    			FROM	dbo.table_auxiliaire
    			ORDER	BY id
    		)
    	DELETE FROM	dbo.table_auxiliaire
    	FROM		CTE AS C
    	INNER JOIN	dbo.table_auxiliaire AS A
    				ON C.id = A.id;
     
    	-- Sortie dans la console (Onglet Messages de SQL Server Management Studio)
    	SET @msg = 'Batch #' + CAST(@i AS varchar(10)) + '	 -	' + CAST(DATEDIFF(millisecond, @dt, GETDATE()) AS varchar(20));
    	RAISERROR(@msg, 0, 1) WITH NOWAIT;
    END
    Et pour suivre le déplacement des lignes :

    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		S.name + '.' + T.name AS table_name
    		, PS.row_count
    FROM		sys.dm_db_partition_stats AS PS
    INNER JOIN	sys.tables AS T
    			ON T.object_id = PS.object_id
    INNER JOIN	sys.schemas AS S
    			ON S.schema_id = T.schema_id
    WHERE		S.name = 'dbo'
    AND		T.name IN
    		(
    			'table_source'
    			, 'table_cible'
    			, 'table_auxiliaire'
    		)
    Il est préférable de créer les index une fois que la nouvelle table est complètement peuplée.

    @++

  7. #7
    Nouveau Candidat au Club
    Homme Profil pro
    Chef de projet MOA
    Inscrit en
    mars 2012
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Homme

    Informations professionnelles :
    Activité : Chef de projet MOA
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : mars 2012
    Messages : 4
    Points : 1
    Points
    1

    Par défaut

    merci bq pour votre réponse

    j'ai splité la table , ça dure 3h maintenant

    je vais attendre maintenant d'avoir de la ram en plus et du cpu


    merci encore à vous tous pour votre aide

  8. #8
    Expert Oracle confirmé

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    mars 2003
    Messages
    335
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Conseil

    Informations forums :
    Inscription : mars 2003
    Messages : 335
    Points : 638
    Points
    638

    Par défaut

    Bonjour,

    Je ne connais vraiment pas grand chose à SQL Server, mais je suis assez surpris par les réponses proposées ici qui ne portent que sur :
    - l'augmentation de la RAM
    - le traitement par paquet de lignes

    Travaillant sur Oracle, j'aurai utilisé les techniques suivantes :

    - table cible créée en NOLOGGING pour qu'il n'y ait pas de journalisation. Bien sur, pour sécuriser l'opération, on effectuera une sauvegarde de la base avant de détruire la table source.
    - données insérées dans la table cible en mode Bulk et en une seule fois (pas de lotissement donc) pour ne pas consommer d'espace de Rollback
    - utilisation du parallélisme à la fois en lecture sur la table source, et aussi sur la table cible. Le parallélisme est d'autant plus utile si les tables source et cible sont partitionnées.

    Du coup, en utilisant ces techniques, je n'ai pas besoin d'allouer de la mémoire à mon instance, car je n'ai majoritairement que des I/O disques.

    Par exemple, j'ai pu recréer une table (51 millions de lignes et 22 Go) en seulement 30 minutes, incluant la recréation de 36 index (4 index B*Tree et 32 index Bitmap pour des requêtes en étoile).

    Si je n'ai pas eu besoin de plus de RAM, c'est que la table cible avait qq nouvelles colonnes, peuplées par la jointure de la table source avec 2 petites tables qui servaient de Lookup et tenaient donc en mémoire. Quant à l'instance Oracle, elle n'avait que 4 Go d'allocation mémoire au total.

    Ma question : n'est-il pas possible de faire qq chose de similaire sous SQL Server ?

    Merci

  9. #9
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    16 876
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 16 876
    Points : 39 175
    Points
    39 175

    Par défaut

    Citation Envoyé par rouardg Voir le message
    Bonjour,

    Je ne connais vraiment pas grand chose à SQL Server, mais je suis assez surpris par les réponses proposées ici qui ne portent que sur :
    - l'augmentation de la RAM
    - le traitement par paquet de lignes

    Travaillant sur Oracle, j'aurai utilisé les techniques suivantes :

    - table cible créée en NOLOGGING pour qu'il n'y ait pas de journalisation. Bien sur, pour sécuriser l'opération, on effectuera une sauvegarde de la base avant de détruire la table source.
    SQL Server ne permet pas de ne pas journaliser. SQL Server transactionne tout (come le prévoit la norme SQL) y compris le DDL et le DCL


    - données insérées dans la table cible en mode Bulk et en une seule fois (pas de lotissement donc) pour ne pas consommer d'espace de Rollback
    SQL Server étant par défaut en autocommit, toute insertion est faite par défaut en mode "bulk".

    - utilisation du parallélisme à la fois en lecture sur la table source, et aussi sur la table cible. Le parallélisme est d'autant plus utile si les tables source et cible sont partitionnées.
    SQL Server travaille massivement en parallèle de manière automatique, sans même que vous ayez à installer un module (parallel query d'Oracle par exemple) et sans que vous ayez besoin de le préciser dans la requête.

    Du coup, en utilisant ces techniques, je n'ai pas besoin d'allouer de la mémoire à mon instance, car je n'ai majoritairement que des I/O disques.

    Par exemple, j'ai pu recréer une table (51 millions de lignes et 22 Go) en seulement 30 minutes, incluant la recréation de 36 index (4 index B*Tree et 32 index Bitmap pour des requêtes en étoile).

    Si je n'ai pas eu besoin de plus de RAM, c'est que la table cible avait qq nouvelles colonnes, peuplées par la jointure de la table source avec 2 petites tables qui servaient de Lookup et tenaient donc en mémoire. Quant à l'instance Oracle, elle n'avait que 4 Go d'allocation mémoire au total.

    Ma question : n'est-il pas possible de faire qq chose de similaire sous SQL Server ?

    Merci
    En journalisant en mode SIMPLE et en découpant votre maj par paquet de lignes.

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  10. #10
    Expert Oracle confirmé

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    mars 2003
    Messages
    335
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Conseil

    Informations forums :
    Inscription : mars 2003
    Messages : 335
    Points : 638
    Points
    638

    Par défaut

    Merci pour ces précisions. Je note donc que SQL Server fait du parallélisme de manière automatique, et que par défaut, il travaille en Bulk.

    Maintenant, je ne suis pas sur que la définition du "Bulk" soit la même entre Oracle et SQL Server.

    En Oracle, on demande à l'optimiseur SQL de faire du chargement en Bulk en utilisant le Hint APPEND, comme cela :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    insert /*+ APPEND */ into table_dest (...) select .... from table_src ;
    commit ;
    Par rapport à un chargement conventionnel, cela veut dire 2 choses :

    1) les données sont rajoutées à la fin de la table, en utilisant un segment temporaire (lequel contient les blocs de donnnées). Si l'insertion réussie, alors le segment temporaire est rattaché au segment de la table. Mais si l'insertion échoue, on rollbacke. C'est très rapide car il suffit de supprimer ce segment temporaire et on récupère l'espace disque occupé.

    Dans un chargement conventionnel, le Rollback est "lent", car il faut aller supprimer chaque ligne insérée. De plus, on ne récupère pas l'espace disque consommée par l'injection des nouvelles données.

    Je pense que SQL Server fonctionne de manière similaire.




    2) le chargement en Bulk veut dire aussi que les données (enfin plutôt les blocs de données qui font 8 Ko par défaut, ce que vous devez appeler des pages sous SQL Server), sont directement écrits dans les fichiers physique de la base, sans passer par le cache de données.

    Au final, Oracle sait faire de la lecture et de l'écriture dans les tables, sans avoir parfois besoin de passer par la mémoire allouée à l'instance. C'est ce qu'on appelle du "Direct Path Read" et "Direct Path Write", et c'est notamment utilisé dans les opération de CTAS (Create Table As Select) ou bien d'Insert /*+ APPEND */, lorsque l'on veut réorganiser des tables par exemple.

    Je me demande donc si SQL Server bénéficie d'un mécanisme similaire. A vous lire, j'ai l'impression que non, puisque vous parler d'augmenter la mémoire de l'instance.

    Mais je me trompe surement.

  11. #11
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    16 876
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 16 876
    Points : 39 175
    Points
    39 175

    Par défaut

    Citation Envoyé par rouardg Voir le message
    Merci pour ces précisions. Je note donc que SQL Server fait du parallélisme de manière automatique, et que par défaut, il travaille en Bulk.

    Maintenant, je ne suis pas sur que la définition du "Bulk" soit la même entre Oracle et SQL Server.
    Tout dépend du mode de journalisation : si SIMPLE ou BULK_LOGGED alors un INSERT INTO ... SELECT ... n'est journalisé que pour la requête et pas pour les données. Il y a écriture minimale dans le journal de transaction (juste la commande SQL, pas les données) et les nouvelles pages sont mise en mémoire (donc un seul accès disque pour journaliser la commande INSERT... Mais en mode FULL, l'intégralité des données de l'INSERT est journalisé dans le JT.

    Je pense que SQL Server fonctionne de manière similaire.
    En fait non, voir ci-avant.


    2) le chargement en Bulk veut dire aussi que les données (enfin plutôt les blocs de données qui font 8 Ko par défaut, ce que vous devez appeler des pages sous SQL Server), sont directement écrits dans les fichiers physique de la base, sans passer par le cache de données.
    C'est l'inverse. Les données sont oujours mise en cache. En cas de pression mémoire (pas assez de cache), SQL Server déclenche une opération CHECKPOINT pour forcer l'écriture des données. C'est cela qui vous arrive...

    Au final, Oracle sait faire de la lecture et de l'écriture dans les tables, sans avoir parfois besoin de passer par la mémoire allouée à l'instance. C'est ce qu'on appelle du "Direct Path Read" et "Direct Path Write", et c'est notamment utilisé dans les opération de CTAS (Create Table As Select) ou bien d'Insert /*+ APPEND */, lorsque l'on veut réorganiser des tables par exemple.

    Je me demande donc si SQL Server bénéficie d'un mécanisme similaire. A vous lire, j'ai l'impression que non, puisque vous parler d'augmenter la mémoire de l'instance.

    Mais je me trompe surement.
    Effectivement c'est l'inverse.

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  12. #12
    Expert Oracle confirmé

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    mars 2003
    Messages
    335
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Conseil

    Informations forums :
    Inscription : mars 2003
    Messages : 335
    Points : 638
    Points
    638

    Par défaut

    Un grand merci SQLpro.

    C'est très clair.

    Oracle et SQL Server fonctionnent de manière très différente sur ce sujet, et je comprends pourquoi une telle allocation mémoire est demandée et nécessaire, et pourquoi on fait une MAJ par paquets en attendant de disposer de tant de mémoire.

    Encore merci.

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Réponses: 5
    Dernier message: 29/07/2016, 22h06
  2. Réponses: 7
    Dernier message: 04/08/2009, 15h29
  3. [Conception] Menu deroulant à partir d'une table volumineuse
    Par newbycool dans le forum Modélisation
    Réponses: 15
    Dernier message: 20/04/2007, 11h26
  4. Comment faire pour générer un fichier à partir d'une BD MySQL
    Par dessinateurttuyen dans le forum Requêtes
    Réponses: 2
    Dernier message: 06/07/2006, 20h39
  5. comment creer un treeview a partir d une table
    Par steeves5 dans le forum VB 6 et antérieur
    Réponses: 1
    Dernier message: 28/06/2006, 18h03

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo