SQL-Abfrage blockiert Datenbank

  • VB.NET

Es gibt 13 Antworten in diesem Thema. Der letzte Beitrag () ist von ISliceUrPanties.

    SQL-Abfrage blockiert Datenbank

    Hallo,

    ich habe ein wirklich fieses Problem und weiß nicht, wo der Haken dabei sein soll. Ich arbeite in einer Microsoft SQL-Datenbank und will die Datensätze einer sehr großen Tabelle auslesen - der Quellcode unten ist auf das Wesentliche reduziert und verursacht den 'Fehler'. Da können dann schon mal 500.000 Datensätze anfallen. Die While-Schleife verursacht dann nach ca. 10.000 gelesenen Datensätzen das Blockieren von Inserts anderer User. Hierbei steht fest, dass wohl alle Tabellen der Datenbank keinen Insert mehr durchführen und das Ganze in einen Wartemodus geht. Erst wenn mein Programm das Dataset und die DB schließt (was gefühlt lange dauert), laufen auch alle inserts wieder. Die gleiche Funktionalität hatte ich damals in VB6 programmiert, allerdings mit Recordsets etc.pp., ohne dass es zu diesem Problem kam - das alte Programm läuft auch noch, also hat sich die Datenbank nicht verändert.

    Das Ergebnis des SQL-Queries habe ich mir in einem Tool ausgeben lassen, d.h. die SQL-Anweisung macht genau, was sie soll. Auch ist die Verarbeitung in der Schleife völlig ok. Soll heißen: mein Programm läuft durch und ich könnte am Ende jubeln...wenn mir nicht zuvor die User die Tür eintreten würden :)

    MySQL hat PRIORITY als weiteren Parameter für den ConnectString - das kennt MSSQL leider nicht. Hat jemand eine Idee, wie dieses Programm laufen kann ohne dass es andere behindert?

    VB.NET-Quellcode

    1. ​ Dim sConnectTestDB As String
    2. Dim connTestDB As SqlConnection
    3. Dim commandTestDB As SqlCommand
    4. Dim DataTestDB As SqlClient.SqlDataReader
    6. sConnectTestDB = "Persist Security Info=False;User ID=xy;Password='xy';Initial Catalog=TestDB;Data Source='...Servername...'"
    7. connTestDB = New SqlConnection(sConnectTestDB)
    9. commandTestDB = connTestDB.CreateCommand()
    10. commandTestDB.CommandText = "SELECT * from Tabelle"
    11. DataTestDB = commandTestDB.ExecuteReader()
    12. If DataTestDB.HasRows Then
    13. While DataTestDB.Read()
    14. Wert = DataTestDB.Item("ID")
    15. 'u.s.w.
    16. End While
    17. End If
    19. DataTestDB.Close()
    20. connTestDB.Close()

    Ein SQL-Anfänger meldet sich zu Wort:

    Zenker schrieb:

    Erst wenn mein Programm das Dataset und die DB schließt (was gefühlt lange dauert), laufen auch alle inserts wieder.
    Du solltest connTestDB, commandTestDB und DataTestDB disposen, wenn sie nicht mehr benötigt werden, am besten durch Verwendung von Using-Blöcken. Vielleicht liegt es schon daran.

    Wenn nicht, dann such erstmal durch Anpassung des SQL-CommandTextes nur wenige Einträge (< 100) und mach mal in Zeile#15 ein Exit While und schau, ob die DB dann immer noch blockiert wird. Denn dann liegt irgendwo anders der Wolf verbuddelt.

    btw: Ist Zeile#12 überhaupt nötig? Die While-Schleife reagiert doch eh nur, wenn es was zu lesen gibt.
    Dieser Beitrag wurde bereits 5 mal editiert, zuletzt von „VaporiZed“, mal wieder aus Grammatikgründen.

    Aufgrund spontaner Selbsteintrübung sind all meine Glaskugeln beim Hersteller. Lasst mich daher bitte nicht den Spekulatiusbackmodus wechseln.

    Hallo, danke für die Antwort :)

    connTestDB, commandTestDB und DataTestDB werden 'frisch' benutzt nach Programmstart, warum dann disposen?

    Solange nicht mehr als ca. 10.000 Sätze gelesen werden, ist alles okay.

    Zeile 12: das prüfe ich mal...aber ob es daran liegt?

    Hallo,
    was mir so auffällt: SELECT * ist nicht optimal. Besser ist es, die Felder explizit zu selektieren, die auch benötigt werden. Dazu gehört auch die Datenmenge. Müssen wirklich 500k Datensätze auf einmal gelesen werden? Kann man die Ergebnismenge nicht reduzieren?
    Ein SELECT setzt normalerweise keinen Lock auf die Tabelle. Das Problem kommt sicherlich anderswo her. Du könntest mit dem SQL Profiler schauen, was da genau passiert. Aber vorsicht, der zieht richtig Performance auf der Datenbank.
    Der SQL-Server weiß eigentlich am besten, wie er die Daten selektieren muss, ABER man kann dem Query einen Hint mitgeben, wie man die Daten gelesen haben möchte. Du könntest also explizit ein READUNCOMMITTED mitgeben. Siehe dazu auch die Doku.

    Quellcode

    1. ​SELECT Field1, Field2 FROM Table WITH(READUNCOMMITTED)

    Die Empfehlung von VaporiZed bzgl. des Disposen solltest du umsetzen. Nicht managed Objekte können nur vom Garbage Collector verarbeitet werden, wenn Dispose aufgerufen wird. Wird das nicht gemacht, bleiben ggf. Reste im Speicher zurück. Deshalb gilt als generelle Regel, alles, was IDisposable implementiert, sollte auch disposed werden. Am einfachsten geht das mit den Using-Blöcken.

    Hallo,

    Leider benötige ich beinahe alle Felder, deswegen das Sternchen. Klar würde ein häppchenweises Einlesen der großen Datenmenge funktionieren, aber das ist eher eine Notlösung. Zumal mich interessiert, warum das so passiert.

    Using kann man offenbar nur explizit, also mit einem New, verwenden, was das DataTestDB ausschließt. connTestDB habe ich mit Using verwendet, ebenso habe ich WITH(READUNCOMMITTED) ausprobiert. Das Problem existiert immer noch.

    Ich habe gerade bemerkt, dass der Insert dann blockiert, wenn jmd. in die von mir gelesene Tabelle schreiben will.

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „Zenker“ ()

    Zenker schrieb:

    Using kann man offenbar nur explizit, also mit einem New, verwenden, was das DataTestDB ausschließt.
    Nee.

    VB.NET-Quellcode

    1. Using DataTestDB = commandTestDB.ExecuteReader()
    2. '…
    3. End Using
    Dieser Beitrag wurde bereits 5 mal editiert, zuletzt von „VaporiZed“, mal wieder aus Grammatikgründen.

    Aufgrund spontaner Selbsteintrübung sind all meine Glaskugeln beim Hersteller. Lasst mich daher bitte nicht den Spekulatiusbackmodus wechseln.

    Zenker schrieb:

    Leider benötige ich beinahe alle Felder, deswegen das Sternchen

    Auch das Angeben (fast) aller Felder ist besser, als mit * zu arbeiten.

    Hast du auch mal eine genaue Fehlermeldung aus deinem Programm?

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „ISliceUrPanties“ ()

    Zenker schrieb:



    connTestDB, commandTestDB und DataTestDB werden 'frisch' benutzt nach Programmstart, warum dann disposen?


    in VB6 hast du niemals dein Recordset offen gehalten, also ist Dispose der richtige weg oder end using

    aus Post#1

    Die gleiche Funktionalität hatte ich damals in VB6 programmiert, allerdings mit Recordsets etc.pp., ohne dass es zu diesem Problem kam - das alte Programm läuft auch noch, also hat sich die Datenbank nicht verändert.

    Hallo,

    @petaod: das Query benötigt im SQL-Studio 9 Sekunden

    Mein Quellcode sieht jetzt so aus:

    VB.NET-Quellcode

    1. ​ Dim sConnectTestDB As String
    2. Dim connTestDB As SqlConnection
    3. Dim commandTestDB As SqlCommand
    4. Dim DataTestDB As SqlClient.SqlDataReader
    6. sConnectTestDB = "Persist Security Info=False;User ID=xy;Password='xy';Initial Catalog=TestDB;Data Source='...Servername...'"
    7. using connTestDB = New SqlConnection(sConnectTestDB)
    9. commandTestDB = connTestDB.CreateCommand()
    10. commandTestDB.CommandText = "SELECT * from Tabelle" '(WITH(ReadUnCommitted) und alle anderen Varianten wurden getestet)
    11. using DataTestDB = commandTestDB.ExecuteReader()
    12. If DataTestDB.HasRows Then
    13. While DataTestDB.Read()
    14. Wert = DataTestDB.Item("ID")
    15. 'u.s.w.
    16. End While
    17. End If
    18. End Using
    19. End Using
    20. DataTestDB.Close()
    21. connTestDB.Close()


    Noch hat sich nichts verändert. Wohin müsste ich denn mit dem DataTestDB.Dispose? Dispose gibt frei, das kann ich ja nicht mitten in die Schleife setzen?!?

    Das Close und Dispose wird durch den Using-Block überflüssig. Daher können die letzten beiden Zeilen weg. Sonst passt Dein Aufbau jetzt - wie geschrieben. das mit dem HasRows ist noch für mich unklar/überflüssig.
    Dieser Beitrag wurde bereits 5 mal editiert, zuletzt von „VaporiZed“, mal wieder aus Grammatikgründen.

    Aufgrund spontaner Selbsteintrübung sind all meine Glaskugeln beim Hersteller. Lasst mich daher bitte nicht den Spekulatiusbackmodus wechseln.

    Hallo,

    okay. Der Effekt ist aber leider immer noch da. Irgendwo ab ca. 5.000 gelesener Datensätze blockiert die DB den Usern das Inserten. Das dürfte eher ein .Net-'Problem' sein, als eines der DB, weil ja VB6 in einem Fall sogar schon 8 Millionen Datensätze angefasst hat, ohne dass der Betrieb stand... Am ehesten aber hätte ich noch das 'select * from tabelle WITH(...)' als Lösung gesehen, leider hilft das nicht.
    Bleibt also nur, häppchenweise die Daten zu verarbeiten?!?

    Du zeigst leider nicht, was genau du in der Schleife machst, aber ich denke mal, dort wird Zeit verbraten, die die Ausführung letztendlich träge werden lässt.
    Eine weitere Möglichkeit bestünde darin, das Lesen und das Verarbeiten zu entkoppeln. Dazu könntest du, wenn nicht eh schon vorhanden, eine Klasse anlegen, die Eigenschaften enthält, die du zur Verarbeitung benötigst und du in der While-Schleife eine Liste von dieser Klasse befüllst. Mit dieser Liste arbeitest du anschließend weiter und machst die Dinge, die aktuell in deiner Schleife passieren.
    Beispielhaft:

    VB.NET-Quellcode

    1. Dim dataList As New List(Of MyRecordClass)
    2. While DataTestDB.Read()
    3. dataList.Add(New MyRecordClass With { .Id = DataTestDb.GetInt32(0), .Name = DataTestDb.GetString(1) })
    4. End While
    5. ' Verbindung schließen usw.
    6. ' ...
    7. For Each item In dataList
    8. ' ... verarbeiten
    9. Next
    11. ' ...
    12. Public Class MyRecordClass
    13. Public Property Id As Integer
    14. Public Property Name As String
    15. End Class


    Du solltest evtl. auch davon absehen DataTestDb.Item aufzurufen und die Werte direkt mit entsprechenden Methoden aufzurufen, um Boxing/Unboxing zu verhindern. Ich habe mal einen Test gemacht, was der Unterschied bei 600.000 Datensätzen wäre, wenn man die Eigenschaften so einer Klasse mit .Item("...") oder mit den entsprechenden Methoden (wie z.B. GetString oder GetDecimal) füllt.

    Brainfuck-Quellcode

    1. | Method | Mean | Error | StdDev | Median | Gen0 | Gen1 | Gen2 | Allocated |
    2. |------------- |---------:|---------:|---------:|---------:|-----------:|----------:|----------:|----------:|
    3. | ReadWithItem | 705.6 ms | 13.98 ms | 37.32 ms | 699.2 ms | 14000.0000 | 6000.0000 | 2000.0000 | 93.06 MB |
    4. | ReadExplicit | 496.0 ms | 9.87 ms | 27.51 ms | 488.3 ms | 12000.0000 | 5000.0000 | 2000.0000 | 74.75 MB |

Facebook
Telefonspende