none
effizientes Übertragen großer Datenmengen auf den SQL-Server

    Frage

  • Hallo,

    ich habe die Notwendigkeit, eine große Anzahl an Datensätzen effizient und in möglichst kurzer Zeit von einer VB.NET Anwendung auf den SQLServer zu schaufeln. Die Anzahl der Datensätze geht in die zig Millionen. Mir ist nur bekannt, für jeden einzelnen Datensatz ein "INSERT INTO ..." abzusetzen. Geht das irgendwie "besser"?

    Nach knapp 9Mio Datensätzen ging der Server (2014 Express) in die Knie. Das Management Studio mit dem ich den Vorgang beobachtete, konnte nur noch mit Mühe an die Tabellen ran. Soll heißen, Abfragen nach der Anzahl an übertragenen Datensätzen mit "SELECT COUNT(*) ... " konnten nicht mehr beendet werden, dauerten Ewigkeiten. Ich habe diese Anfragen dann abgebrochen.

    Die Datensätze selbst sind nicht umfangreich, enthalten nur eine long-Zahl und ein Byte.

    Die Datensätze nur langsam mit Pausen oder ähnlichem rüberzuschaufeln, ist keine echte Option, denn die Daten ergeben sich aus Berechnungen und werden nur in der DB gespeichert, um sie später strukturiert zugänglich zu machen. Die Anwendung die rechnet ist noch nicht fertig und befindet sich in der Testphase.

    Wie kriege ich effizient Datensätze in einer Anzahl von 10-20 Mio mehrfach aus einer VB.NET-Anwendung auf den SQL-Server?

    Gruß
    H.
    Montag, 5. Februar 2018 08:40

Alle Antworten

  • Hallo!

    Ich würde die Zieltabelle erst mal als HEAP anlegen und also keine Indizes definieren. Falls hier schon UNIQUE-Constraints notwendig sein sollten oder Foreign Keys, geht es natürlich nicht ganz so einfach. Dann eben die Minimalversion verwenden!

    Anschließend kannst Du dann alles andere anlegen!


    Einen schönen Tag noch, Christoph -- Data Platform MVP - http://www.insidesql.org/blogs/cmu

    Montag, 5. Februar 2018 10:08
    Beantworter
  • Hallo,

    Wenn es aus einer .NET Applikation heraus erfolgen soll, dann sie Dir mal die SqlBulkCopy Class an und das Beispiel unter SqlBulkCopy.WriteToServer Method (DataTable)


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Montag, 5. Februar 2018 10:12
  • Hi,

    wie hoch ist der RAM Bedarf der Instanz bei diesem Vorgang?

    Da die Express Varianten nur ca. 1 GB RAM verwenden, wird das Überschreiten dieser Grenzen naturgemäß zu einem doch sehr extremen Performanceeinbruch führen.

    Falls es daran liegen könnte, probier mal die Developer Edition aus (und genügend RAM im Rechner). Wenn es damit dann erheblich besser geht, wirst Du um eine Reduzierung der Datenmengen oder der Verwendung einer Edition mit mehr möglichem Ressoucenverbrauch nicht drumrum kommen.


    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET
    http://www.asp-solutions.de/ - Consulting, Development
    http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

    Montag, 5. Februar 2018 10:13
    Moderator
  • Danke für diesen Hinweis. Das werde ich mir mal anschauen.

    Leider habe ich schon das Problem, dass auf dem Rechner auf dem die Berechnungen laufen der Arbeitsspeicher knapp wird. Sind (nur) 8 GB. Zunächst hatte ich alle Ergebnisse in eine Collection geknallt, doch das flog mir recht früh um die Ohren. Der Overhead scheint bei Collections enorm. Jetzt bin ich bei simplen Arrays und das klappt soweit.

    Mit dieser Erfahrung muss ich sehen, ob die Daten im Arbeitsspeicher so organisiert werden könne, dass die BulkCopy-Routinen damit umgehen können, sprich diese DataTables nicht ebenfalls einen großen Speicher-Overhead mitbringen und es dann daran scheitert.

    Montag, 5. Februar 2018 13:35
  • ich schau nochmal nach. Ich denke, es läuft die Developer bereits, bin mir aber grad nicht sicher.

    Die 1GB Grenze hört sich aber plausibel als Ursache für meine Performanceprobleme an.

    Montag, 5. Februar 2018 13:37
  • keine UNIQUE-Constraints, keine Fremdschlüssel, nix dergleichen.

    Einfach einen Datensatz nach dem anderen unten angehängt.

    Montag, 5. Februar 2018 13:38
  • ich schau nochmal nach. Ich denke, es läuft die Developer bereits, bin mir aber grad nicht sicher.

    Die 1GB Grenze hört sich aber plausibel als Ursache für meine Performanceprobleme an.

    Hallo Hannebambel,

    das glaube ich nicht! Es hängt IMHO davon ab, ob Du versuchst, die Datensätze in einer "Gesamttransaktion" oder in Einzeltransaktionen einträgst. Ist es die erstere Variante, liegt es eher daran, dass ein TABLOCK auf der Tabelle liegt, da Microsoft SQL Server durch LOCK ESCALATION eine zu hohe Zahl von Sperren verhindert.


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)

    Mittwoch, 7. Februar 2018 06:40
  • Moin,

    es läuft die Developer Edition, das ist jetzt sicher.

    Und es war genau die Motivation für die Frage hier. Gibt es Möglichkeiten, die Daten in Blöcken zum Server zu schieben. Imo mach ich das Datensatz für Datensatz, einer nach dem anderen und irgendwann fängst halt an zu kneifen. 

    Auffällig war, dass der Server auch dann noch klemmte, als der Task zum Schaufeln der Daten schon längst abgeschossen war. Da hätte sich der Server eigentlich schon wieder von alleine berappelt haben müssen. 

    Gruß

    H.

    Mittwoch, 7. Februar 2018 08:42
  • Moin,

    es läuft die Developer Edition, das ist jetzt sicher.

    Und es war genau die Motivation für die Frage hier. Gibt es Möglichkeiten, die Daten in Blöcken zum Server zu schieben. Imo mach ich das Datensatz für Datensatz, einer nach dem anderen und irgendwann fängst halt an zu kneifen. 

    Auffällig war, dass der Server auch dann noch klemmte, als der Task zum Schaufeln der Daten schon längst abgeschossen war. Da hätte sich der Server eigentlich schon wieder von alleine berappelt haben müssen. 

    Gruß

    H.

    In diesem Thread kam bereits der Hinweis auf BulkCopy. Dort gibt es eine Batch-Size. zw 100.000 und 500.000 sollte das recht effizient laufen.
    Beachte auch die anderen Hinweise auf die Tabellen-Struktur.

    Nach Beendigung des Prozesses liegen die Daten natürlich immer noch im RAM. Das mag die Ursache für das "klemmen" sein. Ansonsten müsste man das mal etwas genauer spezifizieren.

    Ansonsten hat Uwe auch recht: 1 GB an sich ist nicht das Problem, wenn man mit kleineren Batches arbeitet. Die Tabellensperre ist gewollt, das hilft für ein "Minimal Logging".


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
    MCM SQL Server 2008
    MVP Data Platform MCSE Data Platform
    MCSM Charter Member, MCITP Charter Member etc.
    www.SarpedonQualityLab.com
    (Founder)

    Mittwoch, 7. Februar 2018 12:45
  • Wichtig beim Masseninsert ist der Typ der Transaktion!
    Hier solltest du auf keinen Fall SnapShot-Isolation wählen, hier wäre SERIALIZABLE die bessere Wahl, da die Tabelle automatisch gesperrt wird.
    Ob nun Bulkcopy oder DataTable mit DataAdapter, das Verfahren ist letztlich immer das selbe, es wird Insert für Insert in den Server geschoben.
    Wenn du keine Transaktion definiert hast, ist das der schlimmste Fall überhaupt, denn dann wird automatisch je Insert  ein StartTransaction, Insert, CommitTransaction ausgeführt.

    Eine DataTable sollte generell für so etwas vermieden werden, da diese
    a) nur 16 Mio. Sätze zulässt
    b) selber unheimlich lange Ladezeiten hat, da die internen Events bei jedem AddRow nicht abschaltbar sind.

    Man sollte Command-Objekte mit dem Insert-Befehl und auf jeden Fall Parametermarkern verwenden.
    Je nach Datenbank, Satzlänge, Kommunikationswegen o.ä. kann man dadurch mehrere 1000 Inserts je Sekunde erreichen. Bei der Firebird z.B. komme ich da durchaus auf 15.000, der SQL-Server sollte da nicht schlechter sein, das wären ca. 1Mio Sätze je Minute.

    Wenn also die Daten verarbeitet werden, direkt per Insert abschicken, das ist die schnellste Methode.

    Insert into MyTyble (F1, F2) values(@F1, @F2)

    Die Parameterobjekte kann man sich auch in einzelnen variablen speichern, so dass man nicht über die Parameters-Auflistung zugreifen muss.

    Mittwoch, 7. Februar 2018 13:16
  • Hallo,

    ich muss aufpassen, nicht abgehängt zu werden. Ich bin kein Profi auf diesem Gebiet und viele Details bzgl. SQL und Servern ist mir noch nicht geläufig.

    Die Antwort von bfuerchau widerspricht wohl etwas den Posts vorher, die BulkCopy empfehlen. Ich möchte mal zusammenfassen so wie ich es verstanden habe:

    Mit BulkCopy und/oder Datatable kann man eine "größere" Anzahl an Datensätzen am Stück übertragen. Das soll heißen, dass man selber nicht Datensatz für Datensatz schaufeln muss und das im Hintergrund aber auf ähnliche Weise abläuft. Der Overhead (Speicherbedarf/Performance) bei Datatables ist erheblich, so dass sich daraus kein Vorteil ergibt.

    Am besten/schnellsten ist immer noch das übertragen der Daten Satz für Satz  über Command-Objekte (Ich interpretiere das als die SQLCommand-Objekte von VB.NET?) worauf zu achten ist, dass die Transaktion als Serializable zu deklarieren ist.

    Habe ich das so richtig verstanden?

    Gruß

    H. 

    Donnerstag, 8. Februar 2018 13:05
  • Korrekt.
    Fangen wir von hinten an:

    Transaktion Serializable führt automatisch zu einem Table-Lock.
    Macht man eine Transaktion mittels ReadCommitted, wird jeder Insert separat gesperrt und erst nach einer Weile (angeblich nach ca. 5000 Inserts) entscheidet der SQL-Server dann doch, die Tabelle zu sperren.
    Warum also nicht gleich so.

    Overhead DataTable:
    Zuerst muss man nun mal die Daten ja in eine DataTable laden.
    Aktuell habe ich da durchaus Tests, dass ich ca. 20.000 Zeilen / Sekunde bei ca. 15 Spalten schaffe (I7, 2.4 GHz).
    Da dies nur in eienm Thread passiert, hilft da Multicore gar nichts. Multithread wird hier u.U. kontraproduktiv, da DataTable's nicht threadsave sind und somit jeder insert per Lock gegen andere Threads gesperrt werden müsste.

    Jetzt kann man sich also ausrechnen, wie lange man für 1Mio Sätze benötigt, diese in eine DataTable zu laden.

    Eine DataTable führt nebenbei für alle Zeilen (DataRow's) auch noch Versionen um ggf. später diverse Aktionen zu erlauben. Hier kommt ein DataAdapter ins Spiel.
    Dieser kann an Hand eines Select's die benötigten Insert/Update/Deletes generieren.
    Verbindet man den DataAdapter mit der DataTable, reicht ein DataAdapter.Update() um für jede Veränderung der DataTable einen einzelnen SQL Richtung Server abzuschicken:
    Für jede neue Zeile einen Insert, für Update eben den Update und für Löschungen den Delete.

    Wenn man sich dann den BulkCopy ansieht, so macht der intern nichts anderes, nur dass der DataAdapter automatisch erstellt wird.
    Man muss halt nur mal den Prozess im Debugger anhalten und den Callstack prüfen.

    Dies liegt halt in der Natur der Sache, dass es einen Bulk-SQL so nicht gibt.
    Es gibt im SQL-Server Möglichkeiten, ber Bulk-Methoden aus z.B. CSV oder anderen Quellen Daten schnell zu laden, intern wird trotzdem ein Insert je Zeile benötigt.

    Allerdings könntest du hier ebenso 2.stufig ansetzen.
    Du gibst per TextWriter deine Daten einfach als CSV aus, schiebst die CSV auf den Server und machst dort einen nativen BulkCopy, der auch bzgl. Transaktionshändling anders verarbeitet wird.

    Aber du bist erstmal in .NET:

    Den Overhead DataTable kannst du lassen, da du die Daten ja aus einer anderen Quelle Zeile für Zeile beziehst/erzeugst und somit direkt per Command-Objekt die Inserts abgeben kannst.
    Per parametriertem Command spart sich der SQL-Server die Analyse des SQL's, dies wird nur 1 mal gemacht,  sondern kann direkt den Insert ausführen.

    Machst du das Ganze lokal, sollte es sehr schnell gehen.

    Remote hast du ggf. noch eine andere Möglichkeit:
    Man kann durchaus mehrere SQL's in einem Rutsch abgeben, was allerdings nicht oder nur mühsam mit Parametern geht:

    insert into table (f1, f2) values(<f1>, <f2>);
    insert into table (f1, f2) values(<f1>, <f2>);
    insert into table (f1, f2) values(<f1>, <f2>);
    :
    :
    insert into table (f1, f2) values(<f1>, <f2>);

    Der Gesamtsql darf ca. 32K nicht übersteigen.
    Bei nur wenigen Feldern, wie in deinem Fall, könnte es die effektivste Methode sein, da vielleicht die Interpretationszeit der SQL's vernachlässigbar ist.


    • Bearbeitet bfuerchau Donnerstag, 8. Februar 2018 13:41
    Donnerstag, 8. Februar 2018 13:37
  • OK, soweit scheine ich zu verstehen.

    Es bleiben im Moment zwei Fragen:

    1) Wie stelle ich eine Transaktion auf Serializable um?

    2) Parametrisierte Command-Objekte erwarten die Parameter als Collection. Ich müsste somit meine Daten erstmal in diese Collection schaufeln, oder? Dann habe ich wieder das Problem, dass die Collection den Arbeitsspeicher sprengt. Das Array (s.u.) hat erheblich weniger Speicherbedarf. 

    Im Moment liegen die Daten als einfaches Array (Dim _Daten() As Int64) vor. Das Array habe ich in einer Klasse (SpeicherTmp2) mit diversen Methoden verpackt und die eigentliche Routine zum Datentransfer sieht so aus:

       Public Sub SchaufelTMP(Stufe As Byte)
            Dim SQLCmd As New SqlCommand
            Database.Open()
            SQLCmd.Connection = Database
            Try
                SpeicherTmp2.MoveFirst()
                While Not SpeicherTmp2.EOF
                    SQLCmd.CommandText = "INSERT INTO " & sTableFelderTmp & "(Feld,Stufe) VALUES(" & SpeicherTmp2.Read.ToString & "," & Stufe.ToString & ");"
                    SQLCmd.ExecuteNonQuery()
                    SpeicherTmp2.MoveNext()
                End While
            Catch ex As Exception
                frmMain.LogBox.AppendText("Fehler:  SQL Anfrage " & SQLCmd.CommandText & " konnte nicht ausgeführt werden!" & vbCrLf)
                frmMain.LogBox.AppendText("Fehlermeldung: " & ex.Message.ToString & vbCrLf)
            End Try
            closeDB()
        End Sub

    Der  Server liegt lokal

    Donnerstag, 8. Februar 2018 18:01
  • Lassen wir doch einmal diese seltsame Empfehlung "Serializable" Transaction Isolation zu verwenden weg. (Sorry, wenn ich da wieder mal jemandem den Wind aus den Segeln nehme, aber man muss ja nicht alles stehenlassen was nicht richtig ist.)

    Tablock ist eine Option für BulkInsert. Und lässt sich auch sonst auf anderen Wegen viel klarer erreichen (bzw SQL Server macht das auch grn automatisch. Siehe "Lock Esclation"), ohne das man in Gefahr läuft seine "Serializable" Transaction zu vergessen. Denn diese schaffen sehr oft Probleme. Mehr dazu gern mehr mal anderswo.

    Hier geht es ja mehr um Performance, als "seriellen" Zugriff. Und da ist "Minimal Logging" per se viel wichtiger. Was so dazu gehört, das zu erreichen, steht hier: https://docs.microsoft.com/en-us/sql/relational-databases/import-export/prerequisites-for-minimal-logging-in-bulk-import

    - dazu kann dann ein Tablock gehören, und natürlich das Arbeiten mit Batches. Das Isolation Level hat da nur indirekten Einfluss. Read Committed genügt völlig.

    viel ErfolgPS: Unter SQL Server 2008 wurde mal ein Rekord für das Laden von 1TB in 30 Min aufgestellt. Da wurde natürlich auf alls geachtet, vernünftige Partitionierung etc. (Isolation Level aber Standard)

    Am Ende zählt dann nur noch die Hardware...


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
    MCM SQL Server 2008
    MVP Data Platform MCSE Data Platform
    MCSM Charter Member, MCITP Charter Member etc.
    www.SarpedonQualityLab.com
    (Founder)

    Donnerstag, 8. Februar 2018 19:02
  • Das Command bereitet man vor der Ausführung vor (schematisch):

    dim MyCmd as DBCommand = MyConnection.CreateCommand()

    MyCmd.CommandText = "Insert into MyTable (F1, F2) values(@F1, @F2)"
    Dim MyF1Parm = MyCmd.CreateParameter()
    MyF1Parm.Name = "@F1"
    MyF1Parm.DBType = DBType.Bla

    Dim MyF2Parm = MyCmd.CreateParameter()
    MyF2Parm.Name = "@F2"
    MyF2Parm.DBType = DBType.Bla

    while Read()

    MyF1Parm.Value = F1
    MyF2Parm.Value = F2
    MyCmd.ExecuteNonQuery

    endwhile

    Dies wird i.d.R. mit Autocommit-Transaktion durchgeführt.
    Du musst also vor der Schleife

    Dim DbTransaction trans = MyConn.BeginTransaction() ' Default = ReadCommited

    und nach der Schleife dann

    trans.CommitTrans

    durchführen.
    Im Fehlerfall (Catchblock), dann einen RollbackTrans, so dass es ist als ob nie was passiert ist.


    Aber, wie mein Vorredner schon sagte, kann der SQL-Server native natürlich Daten durchaus schneller laden, da er hier intern andere Verfahren verwenden kann und wird.
    • Bearbeitet bfuerchau Freitag, 9. Februar 2018 09:33
    Freitag, 9. Februar 2018 09:31
  • Hallo Baldur,

    "Transaktion Serializable führt automatisch zu einem Table-Lock."

    Sorry, aber das ist totaler Blödsinn! Die Isolationsstufe SERIALIZABLE führt nicht zu einem Table-Lock. Ein Table-Lock ist auch nicht davon abhängig, in welcher Isolationsstufe man sich bewegt.

    Der INSERT in einen gruppierten Index führt zu einem ganz normalen X-Lock auf der Zeile (oder Page, wenn ROWLOCK deaktiviert ist). Die von Microsoft SQL Server durchgeführte LOCK-Eskalation tritt bei zu vielen Sperren ein; dieser Wert wird vom Microsoft als Standard mit ~5.000 Sperren pro Objekt angegeben. Dieser Wert variiert und ist abhängig von der Datenstruktur.

    Die Isolationsstufe SERIALIZABLE soll u. a. PHANTOM READS verhindern und verwendet z. B. bei einem SELECT immer ein RANGE-Lock. Das folgende Beispiel ist eine Tabelle, in der zwischen existierenden Datensätzen (1-999 und 2001 - 75000) ein neuer Datensatz eingetragen wird:

    SET TRAN ISOLATION LEVEL SERIALIZABLE;
    GO
    
    BEGIN TRAN;
    GO
    	
    
    	INSERT INTO dbo.Customer (Id, Name, InsertUser, InsertDate)
    	VALUES (1000, 'ich', 'auch ich', GETDATE());
    
    	SELECT	* FROM sys.dm_tran_locks
    	WHERE	request_session_id = @@SPID;
    	GO

    Wie Du zweifelsfrei feststellen kannst, wird die ganz normale Reihenfolge (IX auf Objekt, IX auf Page und X auf dem Schlüssel) eingehalten. Ursächlich für dieses Verhalten ist natürlich, dass der Datensatz von anderen Prozessen während der Transaktion nicht gelesen werden kann.

    Anders hingegen sieht es bei einem SELECT aus. Hier muss in der Isolationsstufe SERIALIZABLE sichergesteltl sein, dass während der Transaktion zweilfelsfrei nur die Datensätze gelesen werden, die zu Beginn der Transaktion committed waren:

    SET TRAN ISOLATION LEVEL SERIALIZABLE;
    GO
    
    BEGIN TRAN;
    GO
    	SELECT * FROM dbo.Customer WITH (HOLDLOCK)
    	WHERE	Id = 1000;

    Du bekommst zwar nur einen Datensatz (ID = 1000) zurück, aber tatsächlich sperrt Microsoft SQL Server nicht nur den Datensatz ID = 1000 sondern den Bereich von 1000 bis zur nächsten Id (2001)

    Führt man eine Abfrage in der Tabelle basierend auf den beiden Lock-Ressourcen aus, wird das ganze System auch in einer Ausgabe sichtbar:

    SELECT * FROM dbo.Customer
    WHERE %%lockres%% IN (N'(e94ab454cbf2)', N'(9525e5799041)');

    So sehr ich einige Deiner Ratschläge schätze, sind es gerade diese Art von Ratschlägen, die einen Fragensteller eher verwirren als das sie ihm helfen. Ich habe mir mittlerweile angewöhnt, erst einmal die Szenarien zu testen und dann eine Antwort zu posten.

    Zum einen hat es mir geholfen, das System besser zu verstehen und zum Anderen hilft es dem Fragensteller, da die Aussagen durch Beispiele belegbar sind.

    In diesem Sinne :)


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)

    Samstag, 10. Februar 2018 08:33
  • Ja gut, da habe ich HOLDLOCK mit TABLELOCK verwechselt.

    Aber in diesem Zusammenhang hätte ich noch eine andere Idee:

    Per GLOBAL TEMPORARY TABLE habe ich eine sitzungsbezogene eigene Tabelle, an die niemand anders drankommt. Sie muss nicht journalisiert werden und erzeugt daher weniger IO's und/oder Locks.

    Also zuerst die Daten in die temporäre Tabelle schieben und anschließend mit "insert into ... select * from ..." in das Original schaufeln. Dadurch werden bestimmt interne Mechanismen des SQL-Servers effektiver mit den Inserts umgehen können.

    Die temporäre Tabelle wird nach Commit respective Close der Connection freigegeben (je nach Definition).
    Ich weiß nur nicht, wie das Verhalten in der TempDB bzgl. max. Größe o.ä. zu berücksichtigen bzw. für größere Datenmenge zu konfigurieren wäre (ggf. reicht ja der Plattenplatz?).

    Aber auch das bliebe mal auszuprobieren.

    Samstag, 10. Februar 2018 11:03
  • ...

    Aber in diesem Zusammenhang hätte ich noch eine andere Idee:

    Per GLOBAL TEMPORARY TABLE habe ich eine sitzungsbezogene eigene Tabelle, an die niemand anders drankommt. Sie muss nicht journalisiert werden und erzeugt daher weniger IO's und/oder Locks.

    Also zuerst die Daten in die temporäre Tabelle schieben und anschließend mit "insert into ... select * from ..." in das Original schaufeln. Dadurch werden bestimmt interne Mechanismen des SQL-Servers effektiver mit den Inserts umgehen können.

    ...

    Frage: Was ist "journalisieren"? Da bin ich mir jetzt auch bei Oracle nicht sicher. Beim SQL Server gibt es den Begriff nicht.

    Ansonsten zu der Idee:

    Die Essenz daraus ist "Minimal Logging" für de Initialen Insert (da diese Temptable ja eine leere Tabelle ist, ist das genau der Grund für den schellen Insert), und dann ein hoffentlich 2. Minimal Logging-Verhalten gegenüber dem eigentlichen Ziel.

    Du siehst daraus, dass es eigentlich nicht schneller sein kann, als, wie ich oben bereits darauf hinwies, sich direkt um "Minimal Logging" gegen das eigentliche Ziel zu kümmern. Einfach mal dem Link folgen und sich die Matrix vor Augen führen. Das ist eine sehr essentielle, die man durchaus kennen sollte.
    Alles Andere ist nur Rauschen und Raten, was von der eigentlichen Lösung ablenkt.

    in dem Sinne, sonnige Grüße,

    Andreas


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
    MCM SQL Server 2008
    MVP Data Platform MCSE Data Platform
    MCSM Charter Member, MCITP Charter Member etc.
    www.SarpedonQualityLab.com
    (Founder)

    Samstag, 10. Februar 2018 11:12
  • Ja gut, da habe ich HOLDLOCK mit TABLELOCK verwechselt.

    ...

    Respekt (ohne Ironie). Das ist das erste Mal dass Sie einen Fehler (nach über 30 fehlerhaften "Empfehlungen" in diesem Forum) hier zugeben. Das ist ein guter Trend. Bitte dabeibleiben. Perfekt wäre natürlich, das in den Original-Messages zu markieren. Das hilft nachfolgenden Lesern ungemein, da sich dann Fehler gar nicht erst "reinlesen".

    Danke und weiter so


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
    MCM SQL Server 2008
    MVP Data Platform MCSE Data Platform
    MCSM Charter Member, MCITP Charter Member etc.
    www.SarpedonQualityLab.com
    (Founder)

    Samstag, 10. Februar 2018 11:22
  • Also von den 30 fehlerhaften lasse ich allenfalls 2 stehen:-).
    Der Rest sind auch durchaus Diskussionsgrundlagen.

    Außerdem dachte ich, im Forum ist man per Du?

    Journalisierung ist der andere Begriff (aus SQL-Sicht) des Loggings beim SQL-Server.
    Auch hier habe ich schon erlebt, dass man die Loggingdatei klein halten sollte.
    Es ist zwar schon länger her, daher weiß ich nicht ob das heute auch noch so ist, dass bei einer Datenbank der Update eines Flagfeldes von ca. 100.000 Sätzen ca. 10 Minuten benötigte.
    Da sich keiner um die Datenbank gekümmert hatte, war die Loggingdatei mittlerweise auf über 20G angewachsen.
    Nach dem man aufgeräumt hatte, war der Update wieder gewohnt in wenigen Sekunden durch.

    Also kann man gerade bei Masseninserts wohl ebenso Beschleunigungen erziehlen, wenn man das Logging (Transaction log) aufräumt.

    • Bearbeitet bfuerchau Samstag, 10. Februar 2018 16:36
    Samstag, 10. Februar 2018 16:20
  • Also von den 30 fehlerhaften lasse ich allenfalls 2 stehen:-).
    Der Rest sind auch durchaus Diskussionsgrundlagen.

    Außerdem dachte ich, im Forum ist man per Du?

    ...

    Man sollte den Tag nicht vor dem Abend loben:

    Sorry, aber was ich hier von "Dir" seit Deinem Erscheinen lesen musste, wahr hauptsächlich ein Haufen wirrer (weil unzusammenhängender) Vermutungen, steiler Thesen, undeklarierter falscher Ableitungen aus Oracle, und was am schlimmsten ist: allesamt als Tatsachen dargestellt. Bitte nicht persönlich nehmen, es ist schlicht die Wahrheit. Gern kann man darüber mal gemeinsam im Seminar lachen.

    Man merke: Aus Fehlern lernen die Menschen nur, wenn Fehler als solche auch deklariert sind. Deshalb wirst Du finden, das ich, wenn ich mal etwas falsch darstell, das markiere un in-place korrigiere.

    So, mehr sollte zu diesem Thema in diesem Thread nicht gesagt werden müssen.

    Dein Andreas ;-)


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
    MCM SQL Server 2008
    MVP Data Platform MCSE Data Platform
    MCSM Charter Member, MCITP Charter Member etc.
    www.SarpedonQualityLab.com
    (Founder)

    Samstag, 10. Februar 2018 17:51
  • ...

    Journalisierung ist der andere Begriff (aus SQL-Sicht) des Loggings beim SQL-Server.
    Auch hier habe ich schon erlebt, dass man die Loggingdatei klein halten sollte.
    Es ist zwar schon länger her, daher weiß ich nicht ob das heute auch noch so ist, dass bei einer Datenbank der Update eines Flagfeldes von ca. 100.000 Sätzen ca. 10 Minuten benötigte.
    Da sich keiner um die Datenbank gekümmert hatte, war die Loggingdatei mittlerweise auf über 20G angewachsen.
    Nach dem man aufgeräumt hatte, war der Update wieder gewohnt in wenigen Sekunden durch.

    Also kann man gerade bei Masseninserts wohl ebenso Beschleunigungen erziehlen, wenn man das Logging (Transaction log) aufräumt.

    Wie gesagt, "journalisierung" ist kein Begriff beim SQL Server. Wir sprechen hier ja nicht französischen "Journal des Transactions". Die Ähnlichkeit macht es weder im englischen noch Deutschen zum Fachbegriff.

    "Klein" ist immer relativ. Der beobachtete Effekt lag mit Sicherheit an einer internen Fragmentierung der "Virtual Log Files". Die können generell zu Verzögerungen führen. Die Größe an sich hängt damit nur indirekt zusammen. Auch ein kleines Log kann stark fragmentiert sein, und auch ein großes dafür wenig.

    Bevor wir jetzt alle noch denkbaren Schrauben eines SQL Server Systems in diesem Thread beleuchten, warten wir doch mal, ob es nach dem Nachgehen unserer Hinweise überhaupt noch mögliche bedeutende Bottlenecks gibt.


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
    MCM SQL Server 2008
    MVP Data Platform MCSE Data Platform
    MCSM Charter Member, MCITP Charter Member etc.
    www.SarpedonQualityLab.com
    (Founder)

    Samstag, 10. Februar 2018 17:57
  • Moin,

    ich muss leider sagen, dass die letzten Posts dann doch meinen Erkenntnislevel erheblich überstiegen haben.

    Zum Problem zurück: Ich habe eine für mich brauchbare Lösung gefunden und rund 828Mio Einträge in erträglicher Zeit in die DB bekommen. Zwei Punkte sind für den Erfolg ursächlich:

    1) Ich hatte einen Programmierfehler/Denkfehler begangen. In einem Catch-Zweig hatte ich das Programm nicht angehalten, so dass es nach aufgetretener Ausnahme weiterlief.

    Es trat ein Timeout bei der Kommunikation mit den SQLServer auf. Abgeschickt war ein DELETE FROM tblTMP was bei Mio von Datensätzen länger als die standardmäßig eingestellten 30sec dauert. Da das Programm weiter lief kam es zu der Situation, dass ein Task auf dem Server versuchte, die Tabelle zu löschen während meine Anwendung wieder begonnen hatte, Mio von Datensätzen in eben diese Tabelle reinzuschaufeln. Dass das nicht gutgeht sollte nicht erstaunen. Das konnte einfach behoben werden, timeout auf 0 gesetzt! Dennoch hätte das auf die Art angestoßene transferieren, etwa 2 Tage gedauert.

    2)Nach den Hinweisen hier, war der nächste Ansatz die 828Mio Datensätze auf den Server zu schaufeln, mehr oder minder der Gang des geringsten Aufwandes, der meinem Unexpertenlevel entsprach. Die Daten erstmal lokal ins Filesystem (CSV) gehauen und danach per BULK INSERT vom Server aus eingelesen. Das Schreiben ins Filesystem hat den Task der Berechnung um ganze 4 Minuten (1:19h anstatt 1:15h) verlängert. Also kein Ding. Das Einlesen dauerte dann etwas um die 2h. Ebenfalls kein Ding. Das war erstmal als Versuch gedacht, der aber gleich sein Potential zeigte.

    Mich wundert als Laie jetzt nur ein bisschen, dass das direkte Schaufeln von meiner Anwendung aus (drücken) wahrscheinlich um die 2 Tage gedauert hätte (soweit ließe sich das von den bereits erhaltenen Daten abschätzen) und die gleiche Datenmenge (saugen) vom Server aus einen Faktor 24 weniger. Das kann doch nicht nur am Locken irgendwelcher Tabellen liegen, oder?

    Auch wurden beim ersten Ansatz die Daten stufenweise übertragen. Das minimal locking habe ich da intuitiv angewendet, da die Daten ohne Constraints in eine temporäre Tabelle vom Client aus in die DB geschrieben werden sollten und dann intern mit UNIQUE-Beschränkung bzw. SELECT DISTINCT umgeschaufelt und Doubletten aussortiert werden sollten. Auch hier wurde deutlich, dass das Übertragen aus VB.NET heraus erheblich länger dauert als das interne Umschaufeln direkt auf dem Server. Das Locking schließe ich somit als Bremse aus. Ob nu Remote vom Client aus oder intern von einer Tabelle in die andere sollte beim Locking doch keine Unterschiede machen.

    Jetzt muss ich die Daten noch nachbehandeln, d.h. "schlechte" Datensätze aussortieren. Auch das wird an die Performancegrenze gehen, doch das werde ich in einem anderen Thread behandeln.

    Ich danke allen

    Gruß

    H.


    • Bearbeitet Hannebambel Montag, 12. Februar 2018 09:28
    Montag, 12. Februar 2018 09:20
  • Wie ich oben schon schrieb, hat der SQL-Server intern für das Laden von Inhalten sicherlich einige Methoden, die an dem ganzen SQL-Overhead vorbeigehen. Dies kenne ich auch von anderen Datenbanken, die beim Laden erheblich schneller agieren als bei 1000den von Inserts.

    Machst du das native über SQL (egal wie), bist du eben auf SQL angewiesen.
    Hinzu kommt der Kommunikationsaufwand zwischen Anwendung->Treiber->Kommunikation->Server und wieder Retour für das OK bzw. den Fehler.
    Bei 800Mio Sätzen macht der Syntaxcheck für parameterlose Inserts mit Sicherheit ebenso einiges aus und wenn es nur 10 Nanosekunden je Insert wären, sind das ja auch schon alleine 2 Stunden.
    Zusätzlich stellt sich hier auch Autocommit als Bremse dar (zumindest konnte ich in deinem Code keine Transaktion erkennen), da auf jeden Fall je Insert automatisch ein BeginTransaction + CommitTransaction durchgeführt wird, was ebenso beim Logging zusätzlich zu Buche schlägt.

    Rein rechnerisch, da hast du recht, bei angenommenen 25.000 Inserts / Sekunde hätte das Verfahren auch 9 Stunden benötigt.

    Montag, 12. Februar 2018 10:01