webentwicklung-frage-antwort-db.com.de

Der schnellste Weg zum Einfügen von 1 Million Zeilen in SQL Server

Ich schreibe eine gespeicherte Prozedur, um Zeilen in eine Tabelle einzufügen. Das Problem ist, dass wir in einigen Operationen mehr als 1 Million Zeilen einfügen möchten, und wir wollen, dass es schnell geht. Eine andere Sache ist, dass es in einer der Spalten Nvarchar(MAX) ist. Vielleicht möchten Sie in dieser Spalte durchschnittlich 1000 Zeichen eingeben.

Zuerst habe ich ein PRC geschrieben, um Zeile für Zeile einzufügen. Dann generiere ich einige Zufallsdaten für die Einfügung mit der NVARCHAR(MAX)-Spalte, die aus einer Zeichenfolge von 1000 Zeichen besteht. Verwenden Sie dann eine Schleife, um das PRC aufzurufen, um die Zeilen einzufügen. Die Leistung ist sehr schlecht, was 48 Minuten dauert, wenn ich den SQL Server zum Anmelden auf dem Datenbankserver verwende. Wenn ich C # für die Verbindung zum Server auf meinem Desktop verwende (dies ist normalerweise das, was wir normalerweise tun möchten), dauert dies mehr als 90 Minuten.

Dann habe ich das PRC geändert, um einen Tabellentyp-Parameter als Eingabe zu übernehmen. Ich habe die Zeilen irgendwie vorbereitet und in den Tabellentyp-Parameter eingefügt und mit dem folgenden Befehl eingefügt:

INSERT INTO tableA SELECT * from @tableTypeParameterB

Ich habe die Stapelgröße als 1000 Zeilen und 3000 Zeilen versucht (Fügen Sie 1000-3000 Zeilen in @tableTypeParameterB ein, um einmal eingefügt zu werden). Die Leistung ist immer noch schlecht. Es dauert etwa 3 Minuten, um 1 Million Zeilen einzufügen, wenn ich es auf dem SQL-Server ausführen und etwa 10 Minuten, wenn ich mit dem C # -Programm eine Verbindung von meinem Desktop her herstelle.

Die Variable tableA verfügt über einen gruppierten Index mit 2 Spalten.

Mein Ziel ist es, die Einlage so schnell wie möglich zu machen (Mein Ideenziel ist innerhalb von 1 Minute). Gibt es eine Möglichkeit, es zu optimieren?


Nur ein Update:

Ich habe die Bulk Copy-Einlage ausprobiert, die von einigen Personen unten vorgeschlagen wurde. Ich habe versucht, die SQLBULKCOPY zu verwenden, um jeweils 1000 und 10000 Zeilen einzufügen. Die Leistung beträgt immer noch 10 Minuten, um 1 Million Zeilen einzufügen (Jede Zeile hat eine Spalte mit 1000 Zeichen). Es gibt keine Leistungsverbesserung. Gibt es noch andere Vorschläge?


Ein Update basierend auf den Kommentaren erfordert.

Die Daten stammen tatsächlich von der Benutzeroberfläche. Der Benutzer ändert die Benutzeroberfläche für die Massenauswahl, sagen wir, eine Million Zeilen und ändert eine Spalte vom alten Wert in einen neuen Wert. Diese Operation wird in einer separaten Prozedur ausgeführt. Hier müssen wir jedoch den Mid-Tier-Service so einrichten, dass der alte Wert und der neue Wert von der Benutzeroberfläche abgerufen und in die Tabelle eingefügt werden. Der alte Wert und der neue Wert können bis zu 4000 Zeichen umfassen und der Durchschnitt beträgt 1000 Zeichen. Ich denke, der alte/neue Wert der langen Zeichenfolge verlangsamt die Geschwindigkeit, weil, wenn ich die alten/neuen Werte der Testdaten in 20-50 Zeichen ändere und das Einfügen sehr schnell ist, egal ob SQLBulkCopy oder Tabellentypvariable verwendet wird

9
Mandy

Ich denke, was Sie suchen, ist Bulk Insert , wenn Sie SQL bevorzugen.

Oder es gibt auch die Option ADO.NET for Batch Operations , damit Sie die Logik in Ihrer C # -Anwendung beibehalten. Dieser Artikel ist auch sehr vollständig.

Update

Ja, ich fürchte, Bulk Insert funktioniert nur mit importierten Dateien (aus der Datenbank).

Ich habe Erfahrung in einem Java-Projekt, bei dem wir Millionen von Zeilen einfügen mussten (Daten kamen außerhalb der Anwendung btw).

Die Datenbank war Oracle, daher haben wir natürlich die mehrzeilige Einfügung von Oracle verwendet. Es stellte sich heraus, dass das Java-Batch-Update viel schneller war als die mehrwertige Einfügung von Oracle (so genannte "Bulk-Updates").

Mein Vorschlag ist:

  • Vergleichen Sie die Leistung zwischen dem Multi-Value-Insert von SQL Server-Code (dann können Sie aus Ihrer Datenbank lesen, eine Prozedur, wenn Sie möchten) mit dem ADO.NET Batch Insert .

Wenn die zu manipulierenden Daten von außerhalb Ihrer Anwendung stammen (falls sie nicht bereits in der Datenbank vorhanden sind), würde ich sagen, dass Sie sich für die ADO.NET Batch Inserts entscheiden. Ich denke, das ist dein Fall.

Hinweis: Beachten Sie, dass Batch-Inserts normalerweise mit derselben Abfrage arbeiten. Das macht sie so schnell.

7
BonanzaOne

Das Aufrufen eines PRC in einer Schleife erfordert viele Roundtrips zu SQL. 

Sie sind sich nicht sicher, welche Methode Sie für die Stapelverarbeitung verwenden, Sie sollten sich jedoch die Parameterwerte für Tabellen ansehen: Dokumente sind hier . Du willst immer noch Batch schreiben. 

Sie sollten auch den Speicher Ihres Servers berücksichtigen. Die Stapelverarbeitung (z. B. jeweils 10 KB) ist möglicherweise etwas langsamer, kann jedoch den Speicherdruck auf Ihrem Server verringern, da Sie einen Satz auf einmal puffern und verarbeiten.

Tabellenwertparameter bieten eine einfache Möglichkeit zum Marshallen mehrerer Zeilen von Daten von einer Clientanwendung an SQL Server, ohne dass mehrere Roundtrips oder spezielle serverseitige Logik für die Verarbeitung der Daten. Sie können Tabellenwertparameter verwenden, um Datenzeilen einzukapseln in einer Clientanwendung und senden Sie die Daten in einer einzigen .__ an den Server. parametrierter Befehl. Die eingehenden Datenzeilen werden in einer Tabelle gespeichert Variable, die dann mit Transact-SQL bearbeitet werden kann.

Eine weitere Option ist bulk insert . TVPs profitieren von der Wiederverwendung, sie hängt jedoch von Ihrem Nutzungsverhalten ab. Der erste Link enthält eine Anmerkung zum Vergleich:

Die Verwendung von Tabellenwertparametern ist mit anderen Verwendungsmöglichkeiten von .__ vergleichbar. Mengenbasierte Variablen; Verwenden Sie jedoch häufig Tabellenwertparameter kann für große Datensätze schneller sein. Im Vergleich zu Massenvorgängen, die haben höhere Startkosten als Tabellenwertparameter, Tabellenwert Parameter funktionieren gut für das Einfügen von weniger als 1000 Zeilen.

Tabellenwertparameter, die wiederverwendet werden, profitieren von der temporären Tabelle Caching Diese Zwischenspeicherung von Tabellen ermöglicht eine bessere Skalierbarkeit als die entsprechende BULK INSERT-Operationen.

Ein weiterer Vergleich hier: Leistung von bcp/BULK INSERT vs. Tabellenwertparameter

2
bryanmac

Hier ist ein Beispiel, was ich zuvor mit SqlBulkCopy verwendet habe. Ich habe es nur mit ungefähr 10.000 Datensätzen zu tun, aber es wurden einige Sekunden nach der Abfrage eingefügt. Meine Feldnamen waren die gleichen, also war es ziemlich einfach. Möglicherweise müssen Sie die DataTable-Feldnamen ändern. Hoffe das hilft.

private void UpdateMemberRecords(Int32 memberId)
    {

    string sql = string.Format("select * from Member where mem_id > {0}", memberId);
    try {
        DataTable dt = new DataTable();
        using (SqlDataAdapter da = new SqlDataAdapter(new SqlCommand(sql, _sourceDb))) {
            da.Fill(dt);
        }

        Console.WriteLine("Member Count: {0}", dt.Rows.Count);

        using (SqlBulkCopy sqlBulk = new SqlBulkCopy(ConfigurationManager.AppSettings("DestDb"), SqlBulkCopyOptions.KeepIdentity)) {
            sqlBulk.BulkCopyTimeout = 600;
            sqlBulk.DestinationTableName = "Member";
            sqlBulk.WriteToServer(dt);
        }
    } catch (Exception ex) {
        throw;
    }
}
0
Randy R