Gestion de lots pour l'importation en bloc

Cette section explique comment gérer la manière dont la taille des lots est déterminée pour une opération d'importation en bloc. Par défaut, toutes les lignes d'un fichier de données sont importées sous la forme d'un lot unique de taille inconnue au sein d'une transaction unique. Dans ce cas, si l'opération d'importation échoue avant de se terminer, la totalité de la transaction est annulée et aucune donnée n'est ajoutée à la table de destination. L'opération doit être redémarrée au début du fichier de données.

L'importation d'un fichier de données volumineux sous la forme d'un lot unique peut être problématique, de sorte que bcp et BULK INSERT vous permettent d'importer des données dans une série de lots de taille inférieure à celle du fichier de données. Chaque lot est importé et journalisé dans une transaction distincte et une fois qu'une transaction donnée est validée, les lignes importées par cette transaction sont validées. Si l'opération échoue, seules les lignes importées à partir du lot en cours sont annulées et vous pouvez reprendre l'importation des données au début du lot ayant échoué plutôt qu'au début du fichier de données.

Remarque :
Pour plus d'informations sur le fonctionnement des lots, consultez Lots d'instructions.

Si vous décidez de ne pas limiter la taille des lots, une autre solution permettant d'améliorer les performances consiste à estimer la taille du fichier de données dans la commande. Votre estimation est utilisée par le processeur de requêtes lors de la création du plan de requête pour l'opération.

Remarque :
Lorsque vous spécifiez la taille des lots ou du fichier de données, la précision n'est pas fondamentale.

Le tableau suivant récapitule les qualificateurs qui prennent en charge ces différentes possibilités.

Commande

Taille du lot

Lignes envoyées par lot

Kilo-octets envoyés par lot

bcp1

-bbatch_size

-h "ROWS_PER_BATCH = bb"

-h "KILOBYTES_PER_BATCH = cc"

BULK INSERT2

BATCHSIZE = batch_size

ROWS_PER_BATCH = rows_per_batch

KILOBYTES_PER_BATCH = kilobytes_per_batch

Remarque :

L'utilisation de cette option avec BATCHSIZE génère une erreur.

INSERT ... SELECT * FROM OPENROWSET (BULK...)

—3

ROWS_PER_BATCH = rows_per_batch

—3

1 Dans une commande bcp, n'utilisez pas le commutateur -bbatch_size ni l'indicateur ROWS_PER_BATCH ou KILOBYTES_PER_BATCH ensemble. Cette combinaison génère une erreur.

2 Dans une commande BULK INSERT, si vous utilisez BATCHSIZE avec ROWS_PER_BATCH ou KILOBYTES_PER_BATCH, BATCHSIZE est prioritaire.

3 OPENROWSET n'a pas les options BATCHSIZE et KILOBYTES_PER_BATCH.

Les sections suivantes décrivent l'utilisation de ces qualificateurs.

Spécification de la taille de lot approximative

Si vous importez un nombre très élevé de lignes, la répartition des données en lots peut présenter des avantages. Une fois chaque lot terminé, la transaction est journalisée. Si, pour une raison quelconque, une opération d'importation en bloc est interrompue, seule la transaction en cours (lot) est annulée.

Remarque :
La journalisation en bloc génère une copie des données importées dans le journal des transactions. Cela peut amener le journal à croître rapidement mais, après chaque lot, vous pouvez sauvegarder le journal pour y récupérer de l'espace.

Pour importer le fichier de données dans une série de lots d'une taille approximative spécifiée, utilisez le qualificateur suivant :

• Pour la commande bcp : -b
• Pour l'instruction BULK INSERT : BATCHSIZE

Chaque lot de lignes est inséré en tant que transaction distincte. Si, pour une raison quelconque, l'opération d'importation en bloc est interrompue, seule la transaction en cours est annulée. Par exemple, si un fichier de données comporte 1 000 lignes et qu'une taille de lot de 100 lignes est utilisée, Microsoft SQL Server journalise l'opération sous la forme de 10 transactions distinctes, chacune d'entre elles permettant l'insertion de 100 lignes dans la table de destination. Si l'opération d'importation en bloc se termine au chargement de la ligne 750, seules les 49 lignes précédentes sont supprimées lorsque SQL Server annule la transaction en cours. La table de destination contient toujours les 700 premières lignes.

SQL Server optimise automatiquement le chargement en fonction de la taille du lot, ce qui peut aboutir à de meilleures performances. En règle générale, vous devez faire en sorte que la taille du lot soit aussi élevée que possible. Généralement, plus la taille du lot est élevée, meilleures sont les performances de l'opération d'importation en bloc. Il y a cependant quelques exceptions. Si un ou plusieurs index existent sur la table cible, une taille de lot élevée peut peser sur les ressources mémoire à l'occasion d'un tri. De même, au cours d'un chargement parallèle réalisé sans l'option TABLOCK, une taille de lot élevée peut augmenter le risque de blocage.

Remarque :
Les tailles de lots ne s'appliquent pas lors de l'exportation en bloc des données depuis une instance de SQL Server vers un fichier de données.

Spécification de la taille approximative d'un fichier de données

Si vous ne spécifiez pas une taille de lot pour l'opération d'importation en bloc, vous pouvez indiquer la taille approximative du fichier de données afin que le processeur de requêtes puisse allouer efficacement des ressources dans le plan de requête. Pour indiquer la taille approximative du fichier de données, estimez le nombre de lignes ou la quantité de kilo-octets de données, comme suit :

• Estimation du nombre de lignes par lot
  Pour estimer le nombre de lignes, utilisez l'option ou l'indicateur ROWS_PER_BATCH. Si vous spécifiez une valeur > 0, le processeur de requêtes se base sur la valeur de ROWS_PER_BATCH pour allouer les ressources dans le plan de requête. Cette valeur doit être du même ordre que le nombre réel de lignes.
  Bien que toutes les lignes du fichier de données soient copiées vers une instance de SQL Server en un seul lot, bcp affichera le message « 1000 lignes envoyées à SQL Server » après chaque lot de 1000 lignes. Ce message est donné uniquement à titre d'information et s'affiche quelle que soit la taille de lot utilisée.
• Estimation de la quantité de kilo-octets par lot
  Pour estimer la taille du fichier de données en kilo-octets, utilisez l'option ou l'indicateur KILOBYTES_PER_BATCH. SQL Server optimise l'opération d'importation en bloc en fonction de la valeur spécifiée.

Remarque :
Lorsque vous importez en bloc un fichier de données volumineux sans spécifier la taille du lot ni définir d'optimisations de journalisation en bloc, le journal des transactions peut saturer avant la fin de l'opération d'importation en bloc. Pour éviter cette situation, vous pouvez augmenter la taille du journal des transactions ou lui permettre de croître automatiquement.

Voir aussi

Concepts

Optimisation de l'importation en bloc

Autres ressources

BACKUP (Transact-SQL)
Utilitaire bcp
BULK INSERT (Transact-SQL)
ImportRowsPerBatch Property
OPENROWSET (Transact-SQL)
sp_dboption (Transact-SQL)

Aide et Informations

Assistance sur SQL Server 2005