verwendetes Datenbanksystem: SQLServer, postgressql, oledb
Guten Abend an die community,
ich bin ziemlich neu in der .NET Entwicklung und versuche im Augenblick ein Tool zu entwickeln, dass Daten aus einem json File in eine vorhandene Datenbank reinschreibt (providerunabhängig).
Ich habe es nun so weit dass es eigentlich funktioniert, jedoch werden die INSERT-Befehle alle einzeln abgesetzt und bei vielen tausenden von Einträgen innerhalb der JSON-Dateien ist es doch spürbar.
Mein Code sieht im Augenblick folgendermaßen aus:
using (IDbConnection connection = dbFactory.CreateConnection())
{
foreach (TableDTO table in dbTables)
{
//build the insert statement
StringBuilder insertSQLBuilder = new StringBuilder();
insertSQLBuilder.Append("INSERT INTO " + table.Name + "(");
foreach (ColumnDTO column in table.Columns)
{
insertSQLBuilder.Append(column.Name + ", ");
}
insertSQLBuilder.Length = insertSQLBuilder.Length - 2;
insertSQLBuilder.Append(") VALUES (");
for (int i = 0; i < table.Columns.Length; i++) {
insertSQLBuilder.Append("@param" + i + ", ");
}
insertSQLBuilder.Length = insertSQLBuilder.Length - 2;
insertSQLBuilder.Append(")");
//prepare the insert command
using (IDbCommand dbCommand = connection.CreateCommand()) {
dbCommand.CommandText = insertSQLBuilder.ToString();
IDbDataParameter[] dbParameters = new DbParameter[table.Columns.Length];
for (int i = 0; i < table.Columns.Length; i++)
{
IDbDataParameter dbParameter = dbCommand.CreateParameter();
dbParameter.DbType = typeArray[i]; //DbType Array, which holds the types for each column
dbParameter.ParameterName = "param" + i;
dbParameters[i] = dbParameter;
}
while (dataDeserializer.MoveNext())
{
// get new row from json file, each element of ColumnData holds the value and extra information which is not needed here
ColumnData[] columnData = dataDeserializer.Current;
for (int i = 0; i < dbParameters.Length; i++)
{
bool isNotGuid = typeArray[i] != DbType.Guid;
object value = null;
//TODO stupid conversion workaround
if (isNotGuid)
{
value = columnData[i].Value;
}
else
{
value = Guid.Parse(columnData[i].Value);
}
dbParameters[i].Value = value ?? DBNull.Value;
dbCommand.Parameters.Add(dbParameters[i]);
}
//execute statement and close connection
dbCommand.Connection.Open();
dbCommand.ExecuteNonQuery();
dbCommand.Connection.Close();
dbCommand.Parameters.Clear();
}
}
}
}
Innerhalb des JSON-Files stehen alle notwendigen Informationen (Tabellennamen, Spaltennamen- und typen sowie die Daten). Die Typen brauche ich, da ich mir innerhalb des JSON alles als String abspeichere und dementsprechend die Daten später wieder umkonvertieren muss.
Nun zu meiner Frage. Gibt es eine Library, die einen Bulk-Insert oder Batch-Insert für alle gängigen Provider absetzen kann? Im besten Falle kann ich selbst angeben, wie viele Zeilen in Memory gespeichert werden, da ich es vermeiden möchte alle Daten in einer Liste speichern zu müssen bevor ich sie in die Datenbank reinschreiben kann.
Falls ihr eine Idee habt wäre ich unheimlich dankbar.
Es gibt einen BULK INSERT, allerdings kenne ich mich damit nicht aus.
Ich meine, das arbeitet mit CSV, sicher weiß ich das aber nicht, da ich das noch nie genutzt habe.
Es geht aber auch (meiner Meinung nach) viel einfacher mit dem klassischen Insert:
INSERT INTO Table(Column1, Column2, Column3)
VALUES (@Value11, @Value12, @Value13)
,(@Value21, @Value22, @Value23)
,(@Value31, @Value32, @Value33)
oder:
INSERT INTO Table(Column1, Column2, Column3)
SELECT @Value11, @Value12, @Value13
UNION SELECT @Value21, @Value22, @Value23
UNION SELECT @Value31, @Value32, @Value33
Das ganz normal mit dem StringBuilder zusammen bauen und die Parameter hinzufügen und dann abschicken.
Achte aber darauf, dass es eine maximale Anzahl an Parametern (beim SQL-Server sind es 2100), dass heißt Du musst voraus rechnen, wie viele Einträge Du pro Command zusammen legen kannst. Bei 3 Spalten wären das z.B. 700 Einträge pro Insert.
PS:
Für die zweite Option: SQL UNION and UNION ALL Keywords
Musst Du abwägen, ob Du UNION oder UNION ALL nutzen willst.
NuGet Packages im Code auslesen
lock Alternative für async/await
Beim CleanCode zählen nicht die Regeln, sondern dass wir uns mit diesen Regeln befassen, selbst wenn wir sie nicht befolgen - hoffentlich nach reiflichen Überlegungen.
Das was du dort bestelst klingt nach einen einen OR Mapper.
Du wirst aber selten eine absolut neutralen DB Provider umsetzen können.
Es gibt einfach bei bestimmten Szenarien einfach unterschiedliche Syntax.
Auch wenn es bereits viele Schnittmengen und auch einen SQL Standard gibt, setzen aber nicht alle Provider diese auch einheitlih um.
Ich würde mich an deiner Stelle auf eine DB festlegen und dann Fähigkeiten dieser DB voll nutzen.
Wenn es freie DB sein kann würde ich die PostgreSQL ans Herz legen.
Bei kommerziellen dann den SQL Server von Micrsoft.
Damit fährt man in der Regel ganz gut.
Beide bieten hier auch über ihre Provider Bulk Copy Operationen um dein Problem effizient zu lösen.
Sowohl SQL Server als auch PostgreSQL können hier problemlos mit Bulk Copy einige 100.000 Zeilen locker wegschreiben in wenigen Sekunden, wenn man die richtige DB Hardware hat sogar noch schneller.
Nachtrag:
@Palladin007
Soweit ich dies beurteilen kann, verwendest du aber SQL Server Syntax.
Meines Wissens nach ist dies aber kein neutraler Syntax bei SQL.
Ebenfalls bietet der spezifische Provider mit SqlBulkCopy eine richtige BulkInsert variante, die auch um längen schneller ist als die Batch Lösung mit der Values Liste.
T-Virus
Developer, Developer, Developer, Developer....
99 little bugs in the code, 99 little bugs. Take one down, patch it around, 117 little bugs in the code.
Stimmt - ein OR-Mapper würde das natürlich alles auf einen Schlag machen, aber einen performanten Insert von Massendaten würde ich mir da nicht erhoffen.
Solange es aber bei dem einen Insert bleibt und danach nicht mehr mit Massendaten gearbeitet werden muss, würde ich einen OR-Mapper nutzen, da der sehr viel Arbeit erspart.
Wegen des Nachtrags:
Stimmt, der INSERT mit mehreren Zeilen scheint kein SQL-Standard zu sein, die verschiedenen Implementierungen bieten aber alternative Wege an.
Oracle bietet z.B. folgende Syntax an.
Das INSERT SELECT und auch UNION SELECT sind aber beides SQL-Standard (zumindest nach w3schools.com) und damit für den Insert von Massendaten auf jeden Fall eine Option.
Mit verschiedenen "nativen" Bulk-Inserts kenne ich mich noch nicht aus, bisher haben alle meine Lösungen mit dem INSERT gearbeitet, allerdings musste ich bisher auch nie sehr große Datenmengen auf einmal speichern.
NuGet Packages im Code auslesen
lock Alternative für async/await
Beim CleanCode zählen nicht die Regeln, sondern dass wir uns mit diesen Regeln befassen, selbst wenn wir sie nicht befolgen - hoffentlich nach reiflichen Überlegungen.
@Palladin007
Hatte mich auch nur auf das Insert mit der Values Liste bezogen.
Die nativen Bulk Insert bzw. Bulk Copy Varianten sind bei großen Datenmengen, einige 1.000 Einträge und aufwärts immer vorzuziehen.
Bei einigen 100 bis 1.000 Einträge kann man auch alles in eine Transaktion packen, dann muss die DB nur einen großen Block abarbeiten.
Die genaue Menge hängt dann auch von der Hardware und Software Konstellation ab.
Solche Feinheiten sollte man dann in Ruhe austesten.
Nachtrag:
@alma
Wäre es nicht ggf. direkt sinnvoller mit einer Dokumentenorientierte Datenbank zu arbeiten.
Diese sind gerade für JSON Objekte ausgelegt, was die Speicherung und das laden auch einfacher machen dürfte.
Oder gibt es eine fixe Struktur die du als Relationales Modell speichern musst/sollst?
Ansonsten würde ich für reines JSON eine Dokumentenorientierte Datenbank gerade anbieten.
T-Virus
Developer, Developer, Developer, Developer....
99 little bugs in the code, 99 little bugs. Take one down, patch it around, 117 little bugs in the code.
Könntet ihr mir einen OR-Mapper empfehlen, der mir einen schnelleren Insert für mehrere Provider garantiert? Ich hatte mich vor allem bei Dapper schon ein wenig eingelesen, jedoch sehe ich dort kein insert, dass schneller arbeitet als die gewöhnlichen getrennten Statements (siehe https://stackoverflow.com/questions/17150542/how-to-insert-a-c-sharp-list-to-database-using-dapper-net). Das einzige was ich gefunden hab, dass wohl providerunabhängig einen Bulk Insert absetzen kann wäre Dapper Plus. Aber dafür zu bezahlen kommt für das Projekt nicht in Frage.
Welchen Zweck hat den dein Vorhaben Provider unabhängig zu sein?
Du könntest hier z.B. mit Entity Framework Core arbeiten.
Hier wirst du aber auf das Problem stoßen, dass du pro DbContext Implementierung nur eine Verbindungsart, also Datenbankprovider, festlegen musst.
Du kannst meines Wissens nach nicht ohne Umwege mit einem OR Mapper oder anderen neutralen Lösungen arbeiten um in X Datenbanken unterschiedlichen Types Daten zu speichern.
Eine Lösung wäre, wenn du z.B. bei EF Core für jede DB einen DbContext anlegst, der dann seine Verbindung zur spezifischen Datenbank mit seinem Provider hält.
Dann haben diese alle das gleiche Datenmodell aber durch die unterschiedlichen DbContext Varianten auch unterschiedliche Datenbanken Typen.
Ob sich der Aufwand aber auch lohnt, hängt von deinem vorhaben ab.
Wenn dies ein Test-/Lernprojekt ist, dann wäre dies ein netter Zeitvertreib.
Bei einem richtigen Projekt wäre dies vom Aufwand her aber nicht sinnvoll.
Hier würde ich zu einer Dokumentenorientierten Datenbank greifen und die JSON Daten dort direkt ablegen und auslesen.
T-Virus
Developer, Developer, Developer, Developer....
99 little bugs in the code, 99 little bugs. Take one down, patch it around, 117 little bugs in the code.
LINQ to DB unterstützt Bulk Copy (Bulk Insert).
Für .NETStandard gibt es wohl auch noch Kros.KORM.
Vielen Dank für die Vorschläge. Das Tool, dass ich entwickle soll ein Backup und Restore auf einer Datenbank auf Basis von JSON oder XML Dateien durchführen. Es liegt also am Benutzer auf welcher Art von Provider der Restore durchgeführt wird. Ich habe mir linq2db mal etwas genauer geschaut, jedoch sehe ich hier noch keinen Weg wie ich ohne die POCOs auskommen soll. Ich müsste mir ja sozusagen zur Laufzeit die POCOS aus dem JSON generieren, was glaube ich nicht so leicht möglich ist, oder verstehe ich da etwas falsch?
Für BulkInsert via Dapper
verwende ich ausschließlich SqlBulkCopy
mit dem ObjectReader
von FastMember
.
// C# 8-Syntax
public void BulkInsert<TEntity>(string tableName, IList<TEntity> entities, params string[] columns)
{
using SqlBulkCopy sqlCopy = new SqlBulkCopy(Connectionstring, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.KeepNulls);
using ObjectReader reader = ObjectReader.Create(entities, columns);
sqlCopy.DestinationTableName = tableName;
sqlCopy.WriteToServer(reader);
}
Habe bisher keine schnelleren Ergebnisse gesehen.
Ich müsste mir ja sozusagen zur Laufzeit die POCOS aus dem JSON generieren
Diese Differenz musst eben überbrücken.
- performance is a feature -
Microsoft MVP - @Website - @AzureStuttgart - github.com/BenjaminAbt - Sustainable Code
So wie ich das verstanden habe, will der Threadersteller eine Datenbank-Backup-Applikation schreiben. Sein Verfahren ist dabei die Daten in einer Json-Datei zu speichern und die dann wieder auszulesen um daraus die Insert-Statements zu generien und diese auf eine Datenbank abzufeuern, auf welcher das Backup eingespielt werden soll. So hab ich das verstanden.
Über denn Sinn und zweck das in JSON oder XML zu speichern möchte ich jetzt nicht diskutieren. Auch nicht über die Notwendigkeit für die Applikation, den selbst ist der Mann.
Da du wie ich raus lese das du unabhängig von einem verwendeten Datenbanksystem arbeiten willst, kommt OLEDB infrage, was du ja schon verwendest.
Ich würde dir aber empfehlen ein Enum für die entsprechenden Datenbanksysteme anzulegen. Die liegt schon allein daran das Connectionstrings für jedes DB-System unterschiedlich sind. Dann kannst du über eine Switch-Case Anweisung immer die entsprechenden Parts im Code ablaufen, du könntest auch dann beispielsweise für SQLServer den SQLCLIENT als Provider nehmen, der ist für SQLSERVER und bietet dir BULK-Insert. Wenn du aber weiter mit OLEDB arbeiten möchtest, könntest du deine Abläufe parallelisieren. .NET bietet dafür die Klasse parallel. Somit könntest du deine foreach schleife parallel laufen lassen, du musst nur achten ob du evtl. im connectionstring einen Parameter aktivieren musst. Bei SQLSERVER musst du beispielsweise den "MARS" Parameter auf true setzen, sonst bekommst du die Meldung das deine Verbindung beschäftigt ist mit dem ausführen eines anderen Command ist.
Also probieres ruhig mal aus mit dem parallelisieren du darfst nur nicht vergessen ein Parallelloption-Objekt anzulegen dem du eine MaxdegreeofParalism mit gibst.
kannst dann mal deine Ergebnisse mitteilen
Parallelisieren bringt hier erfahrungsgemäß recht wenig.
Aber wie überall beschrieben eine Transaction drum machen bringt einiges.
Ansonsten, der schon erwähnte Linq2Db hat für viele ein BulkCopy eingebaut, und da das Open Source ist, kann man sich mal anschauen wie das geht.
Oder gleich mal auf github nach einer Backuplösung suchen.