Recommandations pour l'utilisation de l'importation en bloc
Cette rubrique fournit des recommandations pour optimiser les performances de plusieurs scénarios d'importation en bloc :
importation de données à partir d'un seul client (ou flux) dans une table vide ;
importation de données à partir d'un seul client (ou flux) dans une table non vide et partiellement remplie ;
[!REMARQUE]
L'importation de données dans une table non vide porte le nom d'importation incrémentielle en bloc. La question essentielle pour ce type d'importation est de savoir si les index doivent être supprimés au préalable.
importation de données en parallèle avec verrouillage au niveau de la table à partir de plusieurs clients (ou flux) ;
copie de données entre instances MicrosoftSQL Server.
Cette rubrique fournit également un résumé des opérations de verrouillage et de journalisation de table au cours de l'importation en bloc.
Importation de données à partir d'un seul client (ou flux) dans une table vide
Lorsque vous importez des données dans une table vide à partir d'un client (ou flux), Microsoft recommande les opérations suivantes :
spécifier le qualificateur TABLOCK (disponible comme indicateur ou option dans les trois méthodes d'importation en bloc). Il provoque la pose d'un verrou au niveau de la table pour la durée de l'importation en bloc et élimine la surcharge due au verrouillage de chaque ligne. Pour plus d'informations, consultez Contrôle du mécanisme de verrouillage pour l'importation en bloc.
minimiser la journalisation. Pour plus d'informations, consultez Conditions requises pour une journalisation minimale dans l'importation en bloc.
gérer les index comme suit.
Lorsque vous utilisez la commande bcp ou les instructions BULK INSERT et INSERT ... SELECT * FROM OPENROWSET(BULK...), si la table est vide et comporte un index cluster et si les données du fichier sont ordonnées pour correspondre aux colonnes clés de cet index, effectuez les deux actions suivantes :
Importez les données en bloc avec l'index cluster déjà en place.
Spécifiez les indicateurs ORDER et TABLOCK. Pour plus d'informations, consultez Contrôle de l'ordre de tri lors de l'importation de données en bloc.
Pour une table vide, cette méthode est nettement plus rapide que la création de l'index cluster après l'importation des données. Elle élimine en outre l'opération de tri.
[!REMARQUE]
Si une table non vide comporte des index, les importations en bloc sont complètement journalisées, même dans le mode de récupération utilisant les journaux de transactions. Pour décider si les index doivent être supprimés, examinez l'avantage de l'importation en bloc dans une table sans index par rapport au coût lié à leur suppression et recréation.
Si vous importez des données en bloc dans une table vide comportant des index et si vous spécifiez la taille des traitements, la table n'est plus vide après le premier traitement. Dès le second traitement, la table est complètement journalisée. Pour des tables indexées vides, envisagez d'effectuer l'importation en bloc en un seul traitement.
[!REMARQUE]
Lorsque la taille du traitement n'est pas spécifiée, par défaut, l'optimiseur de requête SQL Server affecte une taille par défaut au fichier de données. Pour améliorer les performances, vous pouvez utiliser le qualificateur ROWS_PER_BATCH ou KILOBYTES_PER_BATCH comme indicateur de l'optimiseur à propos du nombre approximatif de lignes dans le fichier de données. Pour plus d'informations, consultez Gestion des tailles de lot de copie en bloc.
Il est généralement plus rapide d'importer en bloc les données dans une table sans index que dans une table avec index. Par conséquent, si une table vide comporte des index, vous devez les supprimer avant d'importer les données dans la table et les recréer ensuite. Si les données ne sont pas triées dans une colonne clé de clusters et si la table est vide, supprimez tous les index, importez les données et créez ensuite de nouveaux index.
Importation de données à partir d'un seul client (ou flux) dans une table non vide
L'importation de données dans une table qui contient déjà des données (table non vide) porte le nom d'importation incrémentielle en bloc. La question essentielle pour ce type d'importation est de savoir si les index doivent être supprimés au préalable. Vous avez le choix entre deux options. Soit vous conservez les index, soit vous les supprimez et les recréez ensuite.
Lorsque vous importez des données à partir d'un seul client (ou flux) dans une table non vide, la conservation des index dépend de la quantité des nouvelles données importées par rapport à la quantité de données existantes dans la table :
Si vous importez peu de nouvelles données par rapport à la quantité des données existantes, la suppression et la recréation des index peut s'avérer contre productive. Le temps nécessaire pour recréer les index sera probablement plus long que le temps gagné pendant l'importation en bloc.
En revanche, si vous importez une quantité relativement importante de nouvelles données, la suppression des index dans la table avant l'importation en bloc peut améliorer les performances sans augmenter notablement le temps d'indexation.
Le tableau ci-dessous indique la quantité minimale de nouvelles données qui doivent figurer dans une table avant de supprimer les index. La quantité minimale est proportionnelle à la quantité totale de données présentes dans la table. Elle dépend des types d'index et de leurs combinaisons. Si les nouvelles données sont supérieures au pourcentage conseillé pour un type ou un groupe d'index donné, envisagez de supprimer les index avant l'importation en bloc et de les recréer ensuite. Ces nombres sont sensibles au modèle de données des données existantes et des données à charger. Ainsi, ils ne doivent être considérés qu'à titre d'instruction générale.
Index |
Pourcentage de nouvelles données |
|---|---|
Index cluster uniquement |
30 % |
Index cluster et un index non-cluster |
25 % |
Index cluster et deux index non-cluster |
25 % |
Index non-cluster uniquement |
100 % |
Deux index non-cluster |
60 % |
Importation de données en parallèle avec verrouillage au niveau de la table à partir de plusieurs clients (ou flux)
Si SQL Server s'exécute sur un ordinateur équipé de plusieurs processeurs et s'il est possible de séparer en plusieurs fichiers de données les données à importer en bloc dans une table, vous pouvez améliorer les performances en important les données dans la table en parallèle à partir de plusieurs clients. Lors de l'importation en bloc à partir de plusieurs clients dans une table, chaque client doit posséder son propre fichier de données d'entrée.
Lors de l'importation de données dans une table à partir de plusieurs clients, tenez compte des points suivants :
Plusieurs flux importés en bloc peuvent se bloquer mutuellement.
Pour éviter cela, SQL Server fournit un verrouillage interne spécial baptisé verrou Bulk Update (BU). Pour activer ce verrou, vous devez spécifier l'option TABLOCK pour chaque flux d'importation en bloc sans bloquer les autres flux d'importation en bloc. Cela évite les conflits d'accès à la table parmi les clients. Cependant, un verrou BU est disponible uniquement sur une table (vide ou non vide) qui ne comporte pas d'index. Si vous spécifiez TABLOCK sur une table avec des index, l'importation parallèle en bloc n'est pas possible. Pour plus d'informations, consultez Contrôle du mécanisme de verrouillage pour l'importation en bloc.
Si la table comporte des index, vous pouvez tirer parti du verrou BU en supprimant tous les index avant d'importer les données en bloc. Vous pouvez ensuite importer en parallèle les données en bloc en utilisant l'indicateur TABLOCK, puis recréer le ou les index. Notez également que, si une table non vide comporte des index, les importations en bloc sont complètement journalisées, même dans le mode de récupération utilisant les journaux de transactions. Pour décider si les index doivent être supprimés, examinez l'avantage de l'importation en bloc dans une table sans index par rapport au coût lié à leur suppression et recréation.
[!REMARQUE]
Si vous supprimez des index secondaires, envisagez de les recréer en parallèle en créant chaque index secondaire à partir d'un client séparé.
Pour éviter de supprimer et de recréer des index, vous pouvez effectuer une importation en parallèle sans spécifier l'indicateur TABLOCK. Cependant, dans ce cas, les divers flux d'importation en bloc peuvent éventuellement se bloquer mutuellement. En outre, les optimisations de journalisations en bloc ne sont pas disponibles. Pour minimiser les blocages, vous pouvez spécifier une taille inférieure des traitements et utiliser l'indicateur ORDER pour éliminer l'opération de tri pendant l'importation en bloc.
Les données doivent être réparties entre plusieurs fichiers d'entrée, à raison d'un par client. Pour utiliser plus efficacement le processeur, la taille des fichiers de données doit être similaire.
Pour plus d'informations, consultez Importation de données en parallèle avec un verrouillage de niveau table.
Verrouillage et journalisation de table lors de l'importation en bloc
Le tableau suivant est un résumé de la façon dont, au cours de l'importation en bloc, les types de blocs sont déterminés par le schéma de la table. Ce tableau indique également si la table est vide, si l'indicateur TABLOCK est défini pour l'opération et spécifie quel type de journalisation se produit si la base de données utilise le mode de récupération utilisant les journaux de transactions.
[!REMARQUE]
Après un premier traitement réussi, la table n'est plus vide.
Table cible d'importation en bloc |
La table est-elle vide ? |
L'indicateur TABLOCK est-il défini ? |
Verrous |
Journalisation avec le mode de récupération utilisant les journaux de transactions et le mode de récupération simple |
|---|---|---|---|---|
Segment de mémoire (heap) |
Oui |
Oui |
BU-Tab |
Journalisation en bloc |
Segment de mémoire (heap) |
Oui |
Non |
IX-Tab |
Journalisation complète |
Segment de mémoire (heap) |
Non |
Oui |
BU-tab |
Journalisation en bloc |
Segment de mémoire (heap) |
Non |
Non |
IX-Tab |
Journalisation complète |
Segment de mémoire avec un index non-cluster |
Oui |
Oui |
SCH-M |
Journalisation en bloc |
Segment de mémoire avec un index non-cluster |
Oui |
Non |
IX-Tab |
Journalisation complète |
Segment de mémoire avec un index non-cluster |
Non |
Oui |
SCH-M |
|
Segment de mémoire avec un index non-cluster |
Non |
Non |
IX-Tab |
Journalisation complète |
Index cluster |
Oui |
Oui |
SCH-M |
Journalisation en bloc |
Index cluster |
Oui |
Non |
IX-Tab |
Journalisation complète |
Index cluster |
Non |
Oui |
X-TAB |
Journalisation complète |
Index cluster |
Non |
Non |
IX-Tab |
Journalisation complète |
Copie de données entre instances SQL Server
Pour copier en bloc des données d'une instance SQL Server vers une autre, utilisez bcp pour exporter les données de la table dans un fichier de données. Utilisez ensuite une des méthodes d'importation en bloc pour importer les données du fichier dans une table. Effectuez les deux opérations d'exportation et d'importation en utilisant le format natif ou Unicode natif.
[!REMARQUE]
Pour plus d'informations sur ces formats, consultez Utilisation du format natif pour importer ou exporter des données et Utilisation du format natif Unicode pour importer ou exporter des données.
Si la table source possède un index cluster, ou si vous avez l'intention d'importer les données en bloc dans une table comportant un index cluster :
Exportez les données en bloc hors de la table source en utilisant la commande bcp avec l'option query dans une instruction SELECT et une clause ORDER BY appropriée pour créer un fichier de données classées. Pour plus d'informations, consultez Utilitaire bcp.
Lorsque vous importez des données en bloc dans SQL Server, utilisez le qualificateur ORDER, pris en charge uniquement par bcp et BULK INSERT. Pour plus d'informations, consultez Contrôle de l'ordre de tri lors de l'importation de données en bloc.
Pour plus d'informations, consultez Copie de données entre serveurs.