Comment accélérer les insertions groupées dans MS SQL Server à l'aide de Pyodbc ?

Accélération de l'insertion en masse dans MS SQL Server à l'aide de Pyodbc

Les opérations d'insertion en masse peuvent améliorer considérablement les performances d'insertion de grands ensembles de données dans Microsoft SQL Server. Cet article explore des approches alternatives pour optimiser de telles insertions, en abordant les défis spécifiques rencontrés par le code fourni dans la question.

Approches alternatives

1. Fast Executemany (Pyodbc 4.0.19) : les versions récentes de Pyodbc (4.0.19) offrent la fonctionnalité Cursor#fast_executemany, conçue pour accélérer l'exécution des insertions sur plusieurs lignes. En définissant crsr.fast_executemany sur True, vous pouvez potentiellement obtenir une amélioration significative des performances par rapport à la méthode d'exécution par défaut.

   <code class="python"># Connect to the database and create a cursor with fast_executemany enabled
   cnxn = pyodbc.connect(conn_str, autocommit=True)
   crsr = cnxn.cursor()
   crsr.fast_executemany = True
   
   # Execute the bulk insert operation with parameters
   sql = "INSERT INTO table_name (column1, column2) VALUES (?, ?)"
   params = [(data1, data2) for (record_id, data1, data2) in data]
   crsr.executemany(sql, params)</code>

2. Itération à l'aide de Pandas DataFrame : Alternativement , vous pouvez utiliser Pandas pour lire vos données CSV dans un DataFrame et tirer parti de sa méthode to_sql() optimisée. Cette approche rationalise l'insertion de données et prend en charge diverses optimisations, telles que le regroupement et les conversions de types.

   <code class="python">import pandas as pd
   
   # Read CSV data into a DataFrame
   df = pd.read_csv(csv_file)
   
   # Establish a database connection
   engine = sqlalchemy.create_engine(conn_str)
   
   # Insert DataFrame into the database using `to_sql()`
   df.to_sql('table_name', con=engine, if_exists='append', index=False)</code>

3. Interface de copie en masse (BCP) : l'interface de copie en masse ( BCP) est un utilitaire natif de SQL Server qui permet un transfert de données à grande vitesse entre des fichiers et des tables de base de données. BCP offre plusieurs avantages en termes de performances par rapport aux instructions SQL INSERT standard.

   bcp {table_name} in {csv_file} -S {server} -d {database} -E

Comparaison des performances

L'approche optimale pour votre scénario spécifique dépend de facteurs tels que la taille des données, le serveur configuration et les ressources disponibles. Généralement, fast_executemany offre une amélioration significative des performances par rapport à l'itération via un curseur, tandis que BCP surpasse souvent les deux dans les scénarios d'insertion en masse.

Considérations supplémentaires

• Profilage des données : Assurez-vous que vos données sont correctement formatées et saisies pour éviter les erreurs de conversion SQL susceptibles de ralentir le processus d'insertion.
• Matériel du serveur : vérifiez que votre instance SQL Server dispose de suffisamment de mémoire, de processeur et ressources de stockage pour gérer efficacement l'opération d'insertion en masse.
• Emplacement du fichier : pour la commande T-SQL BULK INSERT, le fichier CSV doit être situé sur le même serveur ou sur un partage réseau accessible. Fast_executemany et Pandas to_sql(), en revanche, sont plus flexibles en termes d'emplacement de fichiers.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!