Laden...

JSON in Datenbank schreiben: Bei vielen Inserts leidet die Performance

Erstellt von alma vor 4 Jahren Letzter Beitrag vor 4 Jahren 2.175 Views
A
alma Themenstarter:in
4 Beiträge seit 2019
vor 4 Jahren
JSON in Datenbank schreiben: Bei vielen Inserts leidet die Performance

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.

2.078 Beiträge seit 2012
vor 4 Jahren

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.

T
2.219 Beiträge seit 2008
vor 4 Jahren

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.

2.078 Beiträge seit 2012
vor 4 Jahren

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.

T
2.219 Beiträge seit 2008
vor 4 Jahren

@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.

A
alma Themenstarter:in
4 Beiträge seit 2019
vor 4 Jahren

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.

T
2.219 Beiträge seit 2008
vor 4 Jahren

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.

A
alma Themenstarter:in
4 Beiträge seit 2019
vor 4 Jahren

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?

16.806 Beiträge seit 2008
vor 4 Jahren

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

  • C# is strict-typed
  • MSSQL ist strict-typed
  • Json ist dynamic-typed

Diese Differenz musst eben überbrücken.

S
25 Beiträge seit 2014
vor 4 Jahren

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

F
10.010 Beiträge seit 2004
vor 4 Jahren

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.