Discussion :

[voyageur]

Je mets en place un système d’import de table à table sur une base de données SQL Server 2000 de 60 GO, plusieurs dizaines de millions de lignes à importer.

Le problème qui se pose lorsque je fais la requête en un bloc est une saturation du journal de transaction.
Une solution serait d’importer en plusieurs blocs plus petits (par bloc d’un million).
L’instruction BULK INSERT avec le paramètre BATCH SIZE ne fonctionne pas pour l’importation de table à table.
J’ai utilisé l’astuce avec ROWCOUNT en faisant bien attention de ne pas réinsérer deux fois la même ligne si elle a déjà été insérée, mais j’ai comme même des erreurs de violation de clé primaires, je ne comprends pas :

Code :

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

1
2
3
4
5
6
7
8
9
SET ROWCOUNT 1000000 
            Encore: 
             BEGIN TRANSACTION      
                 Insert tabledestination(
                           Select * from tablesource
                            Where ID not in ( Select ID from tabledestination)
                           )
            END TRANSACTION             IF @@ROWCOUNT > 0 GOTO Encore:
             SET ROWCOUNT 0

J'ai trouvé cette technique en parcourant le forum, cependant le ROWCOUUNT est encapsulé dans la transaction ce qui ne semble pas fonctionner, il passe une seule fois dans la boucle:

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 @startId numeric
declare @rows int
 
SELECT @rows = 10000
while @rows > 0
begin
BEGIN TRANSACTION
SET rowcount 10000
    SELECT @startId = isnull(max(id), 0) FROM nouvelle_table
    INSERT nouvelle_table SELECT * FROM ancienne_table WHERE id > @startId
    SELECT @rows = @@rowcount
    dump transaction ma_db TO "/chemin....."
COMMIT TRANSACTION
SET rowcount 10000
end

Si vous avez une autre technique optimisée pour l’importation de gros volumes de données qui ne sature pas le journal de transaction (pas avec DTS mais avec des requête T-SQL) je suis preneur.

[kagemaru]

bonsoir,

le bulk insert est souvent la méthode la plus efficace pour extraire ou insérer des données en masse dans ou hors d'une base.
Pourquoi pas ne pas sortir vos données dans un fichier avec BCP OUT puis les insérer par BULK INSERT pour éviter la saturation du log ?

Vous pouvez également passer votre base en mode de récupération bulk-logged pour limiter la quantité de données inscrites dans le journal, les gains sont souvent interessants.
Et pour finir avant l'import, supprimer les index nonclustered et recréez les après, dans bcp de cas, les temps sont meilleurs plutôt qu'en faisant le bulk insert.

Pour mieux choisir en toute connaisance de cause :

http://www.microsoft.com/technet/pro...kload.mspx#E2D

[voyageur]

le bulk insert est souvent la méthode la plus efficace pour extraire ou insérer des données en masse dans ou hors d'une base.
Pourquoi pas ne pas sortir vos données dans un fichier avec BCP OUT puis les insérer par BULK INSERT pour éviter la saturation du log ?

j'ai zappé cette méthode car :
1/ je n'ai pas les droits sur la base pour executer cette commande shell
2/ ca ne risque pas de mettre plus de temps car deux fois l'opération ?
(table-->fichier-->table au lieu de table-->table)

Vous pouvez également passer votre base en mode de récupération bulk-logged pour limiter la quantité de données inscrites dans le journal, les gains sont souvent interessants

c'est le mode de récuperation simple ? si oui je suis deja avec ce mode.

Et pour finir avant l'import, supprimer les index nonclustered et recréez les après, dans bcp de cas, les temps sont meilleurs plutôt qu'en faisant le bulk insert

OK, mais ca ne semble pas suffisant.

[kagemaru]

2/ ca ne risque pas de mettre plus de temps car deux fois l'opération ?
(table-->fichier-->table au lieu de table-->table)

Le bulk insert passe forcément par un fichier, qu'utilisez-vous pour faire du bulk ? un lot DTS ?

c'est le mode de récuperation simple ? si oui je suis deja avec ce mode.

C'est un qui loggue plus que le mode simple, donc si vous êtes en mode simple, c'est le mode le moins contraignant (enfin, la différence est très mince)

Par contre si vous êtes en mode simple, je ne vois pourquoi dans le dernier exemple il y a un DUMP TRAN to fichier. En mode de récup simple, les transactions committées sont supprimées du journal à chaque checkpoint.

[voyageur]

Le bulk insert passe forcément par un fichier, qu'utilisez-vous pour faire du bulk ? un lot DTS ?

Oui dans mon cas copie de table à table le bulk insert ne peut pas marché.
Je pourrais faire comme vous l'avez mentionné d'abort un export vers un fichier avec la commande bcp , et ensuite un import avec bulk insert à partir de ce fichier.
Mais outre le fait que je ne peux pas executer la commande bcp, le fait de passer par un fichier intermediaire risque de mettre plus de temps ( export vers fichier puis import de fichier avec bulk insert ).
Autre probleme qui me vient à l'esprit à quel endroit creer ce fichier d'export quand on n'a pas les droits sur le serveur ?

C'est un qui loggue plus que le mode simple, donc si vous êtes en mode simple, c'est le mode le moins contraignant (enfin, la différence est très mince)

OK

Par contre si vous êtes en mode simple, je ne vois pourquoi dans le dernier exemple il y a un DUMP TRAN to fichier. En mode de récup simple, les transactions committées sont supprimées du journal à chaque checkpoint.

Ca ne sert à rien , c'etais dans le cas ou il n'y avait pas la transaction.

Code :

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

1
2
3
4
5
6
7
8
9
SET ROWCOUNT 1000000 
            Encore: 
             BEGIN TRANSACTION      
                 INSERT tabledestination(
                           SELECT * FROM tablesource
                            WHERE ID NOT IN ( SELECT ID FROM tabledestination)
                           )
            END TRANSACTION             IF @@ROWCOUNT > 0 GOTO Encore:
             SET ROWCOUNT 0

j'ai trouvé pourquoi j'avais des violations de clé primaires à l'insertion dans la table destination : présence de doublons dans la table source.

Mais j'ai toujours mon probleme avec rowcount il y'a toujours qu'un seul passage dans la boucle.

[voyageur]

J'ai trouvé d'ou venait le probleme qu'il n'e bouclait pas :
les instructions BEGIN TRANSACTION, COMMIT, ROLLBACK reinitialise @@rowcount à 0 !!!
D'ou l'interet de prend une variable intermediaire pour ne pas perdre la valeur de @@rowcount aprés l'instruction SQL.

Merci a tous.