Bulk Insert mit ADO.NET

Schwerpunkt
Created for Ralf Zumbrunn ([email protected]) - 15322 on 30.07.2014 23:10:58
Please do not make illegal copies!
Bulk Insert mit ADO.NET
Kaffee? Keine Zeit!
Mal eben ein paar Millionen Datensätze in die SQL-Datenbank importieren – das kann in ein paar Sekunden erledigt
sein, aber auch stundenlang dauern. Wie man‘s richtig macht, lesen Sie hier.
Auf einen Blick
Christian Giesswein studierte
Wirtschaftsinformatik in Wien
und entwickelt heute Software
mit .NET und C#. In Wien hat
er sein Unternehmen Giesswein-Apps gegründet, das sich
auf Individualsoftware und
auf Schulungen im MicrosoftBereich spezialisiert hat.
Inhalt
▸ D
aten zu sieben Millionen
Airports importieren.
▸ Stärken und Schwächen des
Entity Frameworks.
▸ Flotter Import per ADO.NET.
▸ Kombi aus Bulk Insert
und Entity Framework.
dnpCode
A1408Massendaten
[Abb. 1] Datenmodell eines
Flughafens in der Beispieldatei.
10
N
icht selten steht man als Entwickler
vor dem Problem, Daten in eine Datenbank zu importieren. Mittlerweile
braucht man dafür nur noch sehr wenig Code.
Solange es sich nur um ein paar Datensätze
handelt, klappt alles ohne großes Nachdenken.
Sobald aber mehrere Tausend, Hunderttausend
oder gar mehrere Millionen Datensätze importiert werden sollen, dämmert dem einen oder
anderen Entwickler: Ups, das könnte problematisch werden. Diesem Problem will dieser Artikel auf den Grund gehen und erkunden, welche
Möglichkeiten es gibt, größere Datenmengen per
ADO.NET in eine MS-SQL-Datenbank zu importieren, und zwar richtig flott und ohne Zwangskaffeepause.
Sieben Millionen Airports
Listing 1
Die Klasse Airport.
public class Airport
{
public int Id { get; set; }
public FlughafenTyp Type { get; set; }
public string Name { get; set; }
public double Latitude { get; set; }
public double Longitude { get; set; }
public string Country { get; set; }
public string IATACode { get; set; }
public string HomeLink { get; set; }
public string WikipediaLink { get; set; }
}
public enum FlughafenTyp
{
Unknown,
Heliport,
Small_Airport,
Medium_Airport,
Large_Airport,
Seaplane_Base,
Balloonport,
Closed
}
Als Datengrundlage kommt eine etwas größere CSV-Datei zum Einsatz, die von der Website
Our­Airports.com bezogen werden kann. Es handelt sich dabei um eine Open-Data-Website, die
versucht, Daten zu allen Flughäfen weltweit zu
erfassen. Die knapp sieben MByte große Datei
kann unter [1] geladen werden und enthält rund
sieben Millionen Einträge. Das Modell der Daten
zeigt Abbildung 1. Die gute Frage lautet in diesem
Fall, wie man die Flughafendaten am schnellsten
importieren kann. Nach dem Import der CSVDatei wird die Datenbank eine Tabelle mit den
grundlegenden Daten der Flughäfen erhalten.
Diese umfasst die Informationen, wo die Flughäfen sich befinden, wie sie heißen, außerdem weitere Infos, etwa den IATA-Code und dazu =die
URLs der Flughafen-Websites beziehungsweise
die URLs der zugehörigen Wikipedia-Einträge.
Der erste Schritt besteht folglich darin, eine
Datenklasse für die Flughafentabelle anzulegen
(Listing 1). Für den Flughafentyp kommt hier eine kleine Enumeration zum Einsatz, welche die
Information bereithält, um welche Art von Flughafen es sich handelt. Damit ist die Grundklasse
schon erstellt, und der zweite Schritt kann folgen: das Einlesen der Daten.
Zum Einlesen der CSV-Datei und zum Verarbeiten bietet .NET schon seit langer Zeit eine
passende Klasse namens TextFieldParser. Die
Klasse versteckt sich allerdings recht gut in der
Assembly Microsoft.VisualBasic. Der Code aus
Listing 2 erlaubt es, sehr einfach auf die einzelnen Werte einer Zeile zuzugreifen.
Nachdem hier mit einem Stream von Zeilen
gearbeitet und mit yield jeweils nur ein Datensatz zurückgegeben wird, stehen nicht alle Daten
gleichzeitig im Speicher, sondern können elegant sequenziell zurückgegeben werden – Häppchen für Häppchen. Zum Wandeln der Daten ins
Format der typisierten Klasse Airport genügt bereits ein wenig LINQ (Listing 3). Damit steht das
Fundament, und wir können uns damit beschäftigen, die Daten zu importieren.
Daten einlesen und zerlegen
Ein Hoch auf das Entity Framework
Eine CSV-Datei ist eine primitive Art, Daten zu
speichern. Jede Zeile ist ein eigener Datensatz,
die einzelnen Werte werden per Komma (oder
ein anderes Zeichen) voneinander getrennt.
Mit dem Entity Framework ist es ein Leichtes,
einerseits die Datenbank „mal eben so“ zu erstellen, andererseits aber auch die Daten zu importieren. Mit dem Code-First-Ansatz genügt
8.2014 www.dotnetpro.de
Schwerpunkt
Created for Ralf Zumbrunn ([email protected]) - 15322 on 30.07.2014 23:10:58
Please do not make illegal copies!
eine eigene Ableitung von der Basisklasse
DbContext, um bereits vollautomatisch
eine Datenbanktabelle zu erhalten, mit
der auch gearbeitet werden kann. Damit
dies funktioniert, wird zum Projekt also
die Klasse AirportContext hinzugefügt,
die nur eine einzige Property namens Airports enthält.
class AirportContext : DbContext {
public DbSet<Airport> Airports
{ get; set; }
}
Anschließend könnte man diesen Kontext theoretisch bereits verwenden, um
alle Flughäfen auszugeben, die in der Datenbank vorhanden sind:
using (var db = new AirportContext()) {
foreach (var airport in db.Airports)
{ }
}
Wenn wir dieses Beispiel ausführen,
wird das Entity Framework bereits vollautomatisch auf der Standarddatenbank
(bei mir .\SQLEXPRESS) eine Datenbank
mit dem Namen DotNetPro.AirportContext anlegen, also den vollständigen Typnamen des eigenen Kontexts. Wer sich unsicher ist, wo das Entity Framework diese
Datenbank angelegt hat, der sollte einen
kurzen Blick auf die Eigenschaft Database.Connection.DataSource werfen, da sich
darin der Server beziehungsweise der gesamte ConnectionString befindet. Somit
ersparen Sie sich einiges an Arbeit in der
Vorbereitung. Doch wie schnell lässt sich
das Problem der sieben Millionen Datensätze denn nun lösen?
Beim Messen der Zeit hilft die Klasse Stopwatch. Die Einträge werden zum
Kontext hinzugefügt und anschließend
gespeichert:
using (var db = new AirportContext()) {
var watch = Stopwatch.StartNew();
foreach (var airport in airports)
db.Airports.Add(airport);
db.SaveChanges();
watch.Stop();
Console.WriteLine(watch.Elapsed);
}
Wird die Anwendung nun so gestartet,
bleibt erst mal Zeit für eine ausgedehnte
Kaffeepause, denn das Entity Framework
braucht für den Import reichlich viel Zeit.
Viel Zeit bedeutet hier, dass der Vorgang
womöglich erst nach rund einer Stunde
abgeschlossen ist – ich habe die nötige
Geduld nicht aufgebracht und den Import
abgebrochen.
Listing 2
Zugriff auf die Werte per TextFieldParser.
private static IEnumerable<string[]> ReadCsvFile(string filePath)
{
using (TextFieldParser parser = new TextFieldParser(filePath))
{
parser.SetDelimiters(",");
parser.ReadLine();
while (!parser.EndOfData)
{
var fields = parser.ReadFields();
yield return fields;
}
}
}
Listing 3
CSV-Daten in typisierte Daten wandeln.
var airports =
from line in ReadCsvFile(@"C:\Users\Christian\Desktop\airports.csv")
select new Airport
{
Type = (FlughafenTyp)Enum.Parse(typeof(FlughafenTyp), line[2], true),
Name = line[3],
Latitude = double.Parse(line[4], CultureInfo.InvariantCulture),
Longitude = double.Parse(line[5], CultureInfo.InvariantCulture),
Country = line[9],
IATACode = line[13],
HomeLink = line[15],
WikipediaLink = line[16]
};
Listing 4
Import per klassischem SqlCommand.
using (var connection = new SqlConnection())
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText =
@"INSERT INTO Airports VALUES (
@type, @name, @latitude, @longitude, @country, @iata, @home, @wiki)";
foreach (var airport in airports)
{
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("type", airport.Type);
cmd.Parameters.AddWithValue("name", airport.Name);
cmd.Parameters.AddWithValue("latitude", airport.Latitude);
cmd.Parameters.AddWithValue("longitude", airport.Longitude);
cmd.Parameters.AddWithValue("country", airport.Country);
cmd.Parameters.AddWithValue("iata", airport.IATACode);
cmd.Parameters.AddWithValue("home", airport.HomeLink);
cmd.Parameters.AddWithValue("wiki", airport.WikipediaLink);
cmd.ExecuteNonQuery();
}
}
}
www.dotnetpro.de 8.201411
Schwerpunkt Bulk Insert mit ADO.NET
Created for Ralf Zumbrunn ([email protected]) - 15322 on 30.07.2014 23:10:58
Please do not make illegal copies!
Listing 5
Import mit nur einer Transaktion.
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
using (var cmd = connection.CreateCommand())
{
cmd.Transaction = transaction;
cmd.CommandText = @"INSERT INTO Airports VALUES (
@type, @name, @latitude, @longitude, @country, @iata, @home, @wiki)";
foreach (var airport in airports)
{
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("type", airport.Type);
cmd.Parameters.AddWithValue("name", airport.Name);
cmd.Parameters.AddWithValue("latitude", airport.Latitude);
cmd.Parameters.AddWithValue("longitude", airport.Longitude);
cmd.Parameters.AddWithValue("country", airport.Country);
cmd.Parameters.AddWithValue("iata", airport.IATACode);
cmd.Parameters.AddWithValue("home", airport.HomeLink);
cmd.Parameters.AddWithValue("wiki", airport.WikipediaLink);
cmd.ExecuteNonQuery();
}
transaction.Commit();
}
}
}
Listing 6
Weniger Kommandos, höhere Performance.
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
using (var cmd = connection.CreateCommand())
{
cmd.Transaction = transaction;
StringBuilder sb = new StringBuilder();
foreach (var airport in airports)
{
//SQL-Injection
var values = string.Join("','", new object[]
{
(int)airport.Type,
airport.Name.Replace("'",""),
// Escaping
airport.Latitude.ToString(CultureInfo.InvariantCulture),
airport.Longitude.ToString(CultureInfo.InvariantCulture),
airport.Country,
airport.IATACode,
airport.HomeLink.Replace("'",""),
// Escaping
airport.WikipediaLink.Replace("'","") // Escaping
});
sb.AppendLine(@"INSERT INTO Airports VALUES ('"+ values +"')");
}
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();
transaction.Commit();
}
}
}
12
Das Problem
Das hier aufgetretene Problem liegt eigentlich nicht am Entity Framework
selbst, sondern vielmehr an den Voreinstellungen. Das Framework erkennt, dass
es sieben Millionen neue Instanzen gibt,
und beim Aufruf von SaveChanges sieht
es, dass es ein paar Einträge anlegen
muss. Anlegen heißt auf der Datenbankseite natürlich: ein INSERT-Kommando
ausführen, und da das Entity Framework
gründlich arbeitet, werden sieben Millionen einzelne INSERT-Kommandos abgesetzt. Zusätzlich gibt es den Overhead,
dass das Entity Framework alle sieben
Millionen Einträge im ChangeTracking
überwacht und sieben Millionen Instanzen der Klasse Airport im Speicher liegen.
Fazit: Das ist fernab von optimal!
Das Entity Framework optimieren
Kann man dies nun optimieren? Ja, natürlich gibt es auch hier die eine oder
andere Möglichkeit, den Vorgang zu beschleunigen. Die erste Optimierung wäre,
die Nachverfolgung der Änderungen der
Objekte abzuschalten. Dementsprechend
reicht folgende Zeile bereits für einen entscheidenden Zeitgewinn:
db.Configuration
.AutoDetectChangesEnabled = false;
Nach einer halben Minute sind nun bereits alle Objekte in der Datenbank, und
das Kaffeeholen entfällt diesmal. Eine
kleine Zeile, die extreme Auswirkungen
zeigt. Was man dadurch natürlich verliert,
ist die Information, wenn sich ein Objekt
nachträglich ändert. Also Vorsicht mit
dem nachträglichen Ändern der Daten.
Klassisches ADO.NET
Die erste Erkenntnis war bereits, dass das
Problem mit dem Entity Framework zwar
elegant gelöst werden kann, der Import
aber immer noch nicht ganz so flott erfolgt,
wie man es vielleicht erwarten würde. Wie
sieht es denn aus, wenn ADO.NET und die
Klassiker SqlConnection und SqlCommand
zum Einsatz kommen? Lässt sich damit der
gesamte Vorgang beschleunigen?
Der Code in Listing 4 zeigt einen SqlCommand, der jeweils mit Parametern
gefüllt wird und ein INSERT-Statement absetzt. In dieser Grundform ist der Import
via ADO.NET bereits einen Tick schneller
als das Entity Framework. Nach knapp 20
Sekunden sind die sieben Millionen Einträge importiert. Aber auch hier kann wieder ein wenig optimiert werden. Packt man
8.2014 www.dotnetpro.de
Schwerpunkt
Created for Ralf Zumbrunn ([email protected]) - 15322 on 30.07.2014 23:10:58
Please do not make illegal copies!
nämlich den gesamten Code in eine Transaktion (Listing 5), wird das Ganze noch mal
etwas schneller. Mit der Transaktion ist
man schon nach 13 Sekunden am Ziel.
In Anbetracht dessen, dass es sich um
sieben Millionen Einträge handelt, sind
13 Sekunden schon ein recht guter Wert.
Kann dieser Wert aber noch weiter verbessert werden? Genau dieser Punkt ist
sehr spannend. Wenn man nämlich dem
SQL Server statt mehrerer Millionen Einzelkommandos ein paar größere oder
ein richtig großes übermittelt, erreicht
man noch einmal einen größeren Performancesprung (Listing 6).
Hier wird im Grunde ein langer String
mit mehreren INSERT-Kommandos zusammengebaut und als eigenständiger
Befehl abgesetzt. Ergebnis: Der Import ist
nach knapp vier Sekunden abgeschlossen.
Listing 7
DataTable und Bulk Insert.
DataTable dt = new DataTable();
foreach (var property in typeof(Airport).GetProperties().Where(x => x.Name != "Id"))
dt.Columns.Add(property.Name, property.PropertyType);
foreach (var airport in airports)
{
dt.Rows.Add((int)airport.Type, airport.Name, airport.Latitude,
airport.Longitude, airport.Country, airport.IATACode,
airport.HomeLink, airport.WikipediaLink);
}
using (var bulkInsert = new SqlBulkCopy(connectionString))
{
bulkInsert.DestinationTableName = "Airports";
foreach (DataColumn col in dt.Columns)
bulkInsert.ColumnMappings.Add(col.ColumnName, col.ColumnName);
bulkInsert.WriteToServer(dt);
}
Noch schneller?
Möchte man nun noch mehr Performance rausholen, dann kann man auf eine etwas andere Technik umsteigen. Für
Massenimporte wird gern auf den Bulk
Insert zurückgegriffen. Diese Technik bietet die Möglichkeit, mit nur einem Befehl
mehrere Tausend Zeilen hinzuzufügen.
Die einzige Besonderheit ist, dass die
korrespondierende .NET-Klasse namens
SqlBulkCopy [2] nur mit DataTables, DataRows und IDataReader zusammenarbeitet. Dementsprechend ist eine kleine,
temporäre DataTable anzulegen, um diese dem SqlBulkCopy-Befehl übergeben zu
können. Um dies möglichst einfach und
kurz zu schreiben, kommt hier etwas Reflection zum Einsatz, um schnell an die
verschiedenen Properties zu kommen,
damit die Spalten in der DataTable zu definieren und auch die ColumnMappings
zu übernehmen, damit die Klasse SqlBulkCopy weiß, welche Spalte in der DataTable auf welche Spalte im SQL Server
gemappt wird (Listing 7).
Anschließend wird man feststellen, dass
nun alle Daten sogar in weniger als drei
Sekunden importiert wurden. Wobei allein das Füllen der DataTable im Speicher
bereits zwei Sekunden benötigt. Das Ausführen des reinen C#-Codes braucht hier
somit länger als das Hinzufügen der Daten.
SqlBulkCopy mit dem Entity
Framework
Nun haben Sie die Extreme bereits kennengelernt – von unglaublich (und inakzeptabel) langsam bis zu extrem schnell.
Wenn man den Bulk-Insert-Ansatz mit
dem Entity Framework kombinieren
möchte, so gibt es hierfür bereits eine Lösung. Per NuGet kann man das Paket EntityFramework.BulkInsert [3] installieren:
PM> Install-Package
EntityFramework.BulkInsert-ef6
Ist das Paket installiert, genügt der folgende Code für den Import der Flughafendaten:
using (var db = new AirportContext())
{
var watch = Stopwatch.StartNew();
db.BulkInsert(airports);
db.SaveChanges();
Console.WriteLine(watch.Elapsed);
}
Auch hier ist man nach drei Sekunden
bereits am Ziel, hat aber den gesamten
Komfort des Entity Frameworks zuzüglich der Power des Bulk Inserts. Wer sich
den Quellcode des Projektes ansieht, dem
wird ziemlich schnell bewusst, dass im
Hintergrund wieder der Ansatz mit DataTable und SqlBulkCopy verwendet wird.
Also nichts Neues, aber eine Erleichterung im Zusammenhang mit dem Entity
Framework.
Fazit
Tabelle 1 zeigt die Ergebnisse der einzelnen Messungen. Anzumerken ist, dass
hier absichtlich keine konkreten Werte
angegeben sind, sondern großzügig gerundet wurde. Die Messwerte würden
sich je nach System und auch Aufgabenstellung unterscheiden. Die Werte sollen
vielmehr deutlich machen, wie die Perfor-
mance sich im jeweiligen Anwendungsfall
unterscheidet und wo Probleme auftreten
beziehungsweise welche Möglichkeiten
bestehen, die Daten zu importieren. Und
es zeigt sich recht deutlich, dass das Entity Framework nicht immer die optimale
Antwort auf alle Fragen ist und man sich
je nach Anwendungsfall im Klaren sein
muss, wie das Entity Framework arbeitet
beziehungsweise wo seine Schwächen
liegen. Ab und an bietet es sich immer
noch an, ältere Konstrukte aus dem .NET
Framework auszugraben und mit klassischem ADO.NET zu arbeiten oder eben
auch mit DataTables. [bl]
[1] D
aten zu sieben Millionen Flughäfen,
http://ourairports.com/data/airports.csv
[2] SqlBulkCopy-Klasse,
www.dotnetpro.de/SL1408Massendaten1
[3] EntityFramework.BulkInsert,
http://efbulkinsert.codeplex.com
Tabelle 1
Zeitbedarf des Imports.
Art des Imports
Entity Framework
mit Change Tracking
Dauer
Abgebrochen
Entity Framework ohne
Change Tracking
rund 30 Sekunden
ADO.NET ohne Transaction
rund 20 Sekunden
ADO.NET mit Transaction
rund 13 Sekunden
ADO.NET mit StringBuilder
rund 4 Sekunden
SqlBulkCopy und DataTable
rund 3 Sekunden
EntityFramework.BulkInsert
rund 3 Sekunden
www.dotnetpro.de 8.201413