SQL Abruf Performance Problem

  • VB.NET
  • .NET 4.5

Es gibt 38 Antworten in diesem Thema. Der letzte Beitrag () ist von Haudruferzappeltnoch.

    SQL Abruf Performance Problem

    Hallo,

    ich habe eine Abfrage, die mir jeweils mit einem anderen variablen Parameter genau eine Zeile ausspuckt. Dieser muss jedoch für viele dieser Parameter ausgeführt werden
    Jetzt dachte ich mir sammel ich doch erstmal alle Daten ein ohne Parameter und mache den Rest dann z.B mit LINQ.
    Jedoch sind die Rohdaten scheinbar so gewaltig, dass hier der eine Abruf auch bereits seine Zeit braucht.
    Der Anstieg verläuft linear mit Anzahl der resultierenden Spalten. Daher kann ich mir vorstellen das beide Methoden ähnlich viele Zeit verlangen.

    Kann man da was machen oder ist das einfach ein Problem beim Datenabrufen?

    So rufe ich ab:
    Spoiler anzeigen

    VB.NET-Quellcode

    1. Public Class Form1
    2. Public Sub tabelleladen()
    3. Dim strsql As String = "string"
    4. Dim DBZ1 As New DBZugriff(strsql)
    5. DBZ1.RecentAdapter.Fill(DataSet1.dtRecent)
    6. End Sub
    7. End Class
    8. Friend Class DBZugriff
    9. Private SqlString As String
    10. Friend Sub New(sql As String)
    11. SqlString = sql
    12. End Sub
    13. Friend Sub New()
    14. End Sub
    15. Friend Function SqlConn(timeout As Integer) As String
    16. Dim sqlBuilder As New SqlClient.SqlConnectionStringBuilder()
    17. sqlBuilder.DataSource = "Source"
    18. sqlBuilder.InitialCatalog = "Catalog"
    19. sqlBuilder.IntegratedSecurity = False
    20. sqlBuilder.MultipleActiveResultSets = True
    21. sqlBuilder.UserID = "user"
    22. sqlBuilder.Password = "pw"
    23. If timeout > 0 Then
    24. sqlBuilder.ConnectTimeout = timeout
    25. End If
    26. Return sqlBuilder.ToString
    27. End Function
    28. Friend Function SqlConn() As String
    29. Return SqlConn(0)
    30. End Function
    31. Friend Function RecentAdapter() As SqlDataAdapter
    32. Dim cn As New SqlConnection(SqlConn())
    33. Dim da As New SqlDataAdapter(SqlString, cn)
    34. Return da
    35. End Function
    36. End Class



    Viele Grüße

    *Topic verschoben*

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „Marcus Gräfe“ ()

    Zeig mal das SQL Statement.

    Hast mal dein SQL Statement mit dem
    SQL Server Management Studio analysiert? Stichwort „Ausführungsplan“.

    docs.microsoft.com/de-de/sql/r…lan?view=sql-server-ver15
    "Gib einem Mann einen Fisch und du ernährst ihn für einen Tag. Lehre einen Mann zu fischen und du ernährst ihn für sein Leben."

    Wie debugge ich richtig? => Debuggen, Fehler finden und beseitigen
    Wie man VisualStudio nutzt? => VisualStudio richtig nutzen
    meist ists ein datengebundenes DGV, was die Befüllung-Performance der DataTable versaut.

    sehr selten, aber auch möglich: grosse Blob-Felder - sodass eben wirklich enorm viele Bytes rumgeschaufelt werden.
    Man sollte deshalb Blobs immer in eine Extra-Tabellen legen, auf die verwiesen wird.
    @mrMo Oh nun ja das Sql-Statement, das hat es natürlich in sich. ich weiß nicht ob dir das weiterhilft ohne eine Datenbasis.
    Spoiler anzeigen

    Quellcode

    1. Select basetable.id3, basetable.id4, basetable.col1, basetable.id5, basetable.id1, basetable.id2,
    2. bonustable1.col1, bonustable1.col2, bonustable1.col3, '1' as col4, bonustable1.col5, //21 weitere bonustable1-Spalten//,
    3. bonustable2.col21, Convert(Decimal(10, 1), bonustable2.col22 / bonustable2.col23) as colwert21, Convert(Decimal(10, 1), bonustable2.col4) as col24,
    4. Convert(Decimal(10, 1), bonustable2.col5) as col25, bonustable2.col26, bonustable2.col27, Convert(Decimal(10, 1), bonustable2.col28 / bonustable2.col29) as colwert22,
    5. bonustable2.col210, (CASE When bonustable2.col211 Like 'abc' Then 'mark ' + bonustable2.col211 Else bonustable2.col211 END) as col211, //9 weitere bonustable2-Spalten//
    6. bonustable3.col31, bonustable3.col32, bonustable3.col33, //14 weitere bonustable3-Spalten//,
    7. bonustable4.col41, bonustable4.col42,
    8. bonustable5.col51, bonustable5.col52, Convert(Decimal(10, 1), bonustable5.col53) as col53, Convert(Decimal(10, 1), bonustable5.col54) as col54,
    9. (SELECT cast(t2.col55 as varchar) + '' From bonustable5 t2 Where t2.id1 = basetable.id1 And t2.id2 = basetable.id2 and t2.col55 > 0 Order By t2.id3 asc For Xml path('')) As col5,
    10. bonustable5.col56, bonustable5.col57, //6 weitere bonustable5-Spalten//
    11. bonustable6.col61, bonustable7.col71, bonustable7.col72, bonustable7.col73,
    12. bonustable8.col81, bonustable8.col82, bonustable8.col83, bonustable8.col84, bonustable8.col85,
    13. bonustable9.col91, bonustable9.col92, bonustable9.col93, bonustable9.col94,
    14. bonustable10.col101, bonustable10.col102, bonustable11.col111
    15. (SELECT t2.col1+'# 'FROM bonus12table t2 WHERE (t2.id1=basetable.id1) AND (t2.id2=basetable.id2) AND (t2.id3=basetable.id5) GROUP BY t2.col1, t2.col2, t2.col3 ORDER BY t2.col1, t2.col2 for xml path('') ) as aenderung,
    16. (SELECT bonus12table.coltext from bonus12table where (bonus12table.id1 = basetable.id1 And bonus12table.id2 = basetable.id2 And bonus12table.id3 = basetable.id5 and bonus12table.coltext <> '' and bonus12table.col3 = 500) Group by bonus12table.coltext) as coltext,
    17. From basetable
    18. Left Join bonustable1 On bonustable1.id = basetable.id1
    19. Left Join bonustable2 on bonustable2.id1 = basetable.id1 And bonustable2.id2 = basetable.id2
    20. Left Join bonustable3 on bonustable3.id1 = basetable.id1 And bonustable3.id2 = basetable.id2 And bonustable3.id3 = basetable.id5
    21. Left Join bonustable4 on bonustable4.id1 = basetable.id1 And bonustable4.id2 = basetable.id2 And bonustable4.id3 = basetable.id5
    22. Left Join bonustable5 on bonustable5.id1 = basetable.id1 And bonustable5.id2 = basetable.id2
    23. Left Join bonustable6 on bonustable6.id1 = basetable.id1 And bonustable6.id2 = basetable.id2
    24. Left Join bonustable7 on bonustable7.id1 = basetable.id1 And bonustable7.id2 = basetable.id2
    25. Left Join bonustable8 on bonustable8.id1 = basetable.id1 And bonustable8.id2 = basetable.id2 And bonustable8.id3 = basetable.id5
    26. Left Join bonustable9 on bonustable9.id1 = basetable.id1 And bonustable9.id2 = basetable.id2 And bonustable9.id3 = basetable.id5
    27. Left Join bonustable10 on bonustable10.id = basetable.id3
    28. Left Join bonustable11 on bonustable11.id1 = basetable.id1 And bonustable11.id2 = basetable.id2
    29. Where col101 > DateAdd(Hour, -2,GetDate()) and (basetable.id1 < 70 Or (basetable.id1 > 100 and basetable.id1 < 120) Or basetable.id1 > 320)
    30. Order by basetable.id3, basetable.id4, basetable.id5

    Ich habs mit Top 1, Top 10, Top 100, probiert um das zeitliche Verhalten festzustellen.
    Ich weiß nicht ob sich das unterscheidet, wenn ich stattdessen mithilfe von where-Befehlen die Resultatzeilen verringern würde.

    @ErfinderDesRades
    Ich habs nachgeprüft , der Fill-Befehl ist was die gesamte Zeit konsumiert. Das Dgv braucht auch n Moment aber das ist verhältnismäßig hätt ich gesagt.
    Ansonsten kann ich nicht ganz folgen, was du mit der verwiesenen Extra-Tabelle meinst, die muss ich ja auch erstmal füllen.

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

    Ok das SQL bringt mir nicht wirklich was… sieht wild aus.

    Was ist denn der Unterschied zwischen den BonusTables 1-11? Lässt sich das nicht in einer Tabelle abbilden?

    Manchmal ist es schneller „alles“ zu laden und dass dann von Hand in vb.net zusammen zu basteln.

    Lass dir bei der SQL Abfrage mal den Ausführungsplan mit ausgeben. Da steht drin was der/die Zeitfresser sind.
    "Gib einem Mann einen Fisch und du ernährst ihn für einen Tag. Lehre einen Mann zu fischen und du ernährst ihn für sein Leben."

    Wie debugge ich richtig? => Debuggen, Fehler finden und beseitigen
    Wie man VisualStudio nutzt? => VisualStudio richtig nutzen
    In MSSQL selbst geht die Ausführung deutlich schneller als das Laden in vb.

    Die Tabellen alle laden das ist um Gottes Willen viel zu viel Inhalt, das kann ich mir nicht vorstellen, dass das schneller geht, aber ich versuch mal plan nur die Basistabelle zu laden und schauen wie sich das verhält.
    Bonustabelllen lassen sich womöglich zusammenfassen, dafür brauch ich aber ja auch schon sql Statements in der Größenordnung.

    Ausführungsplan hab ich noch nicht gemacht, weiß ich auch nicht was das ist, aber mach ich mich morgen dran.

    Haudruferzappeltnoch schrieb:

    Die Tabellen alle laden das ist um Gottes Willen viel zu viel Inhalt, das kann ich mir nicht vorstellen, dass das schneller geht
    Daher sagte ich „manchmal“ …

    Performance Leaks zu finden und zu beheben ist nicht einfach. Vor allem wenn man die Daten nicht vor sich hat…

    Was ist denn der Unterschied zwischen den BonusTables 1-11?
    "Gib einem Mann einen Fisch und du ernährst ihn für einen Tag. Lehre einen Mann zu fischen und du ernährst ihn für sein Leben."

    Wie debugge ich richtig? => Debuggen, Fehler finden und beseitigen
    Wie man VisualStudio nutzt? => VisualStudio richtig nutzen

    Haudruferzappeltnoch schrieb:

    Ich habs nachgeprüft , der Fill-Befehl ist was die gesamte Zeit konsumiert. Das Dgv braucht auch n Moment aber das ist verhältnismäßig hätt ich gesagt.
    Wie hast du das nachgeprüft?
    Hast du wirklich eine new DataTable befüllt, an der auch kein DGV angebunden ist?

    Aber dein Sql ist tatsächlich auch möglicherweise performance-problematisch.
    Auf Arbeit hatte ich auch mal mit Left-Joins zu tun - das ging überhaupt nicht auf, und haben wir schlussendlich ganz anders lösen müssen.
    Aber mir sehen auch die BonusTables 1-11 komisch aus, meist ists ein Datenmodell-Fehler, wenn viele gleichartige Tabellen herumfahren, und nur an angehängten Nummern unterscheidbar sind.
    Aber danach frug MrMo ja auch schon.
    dein Problem ist hier...

    VB.NET-Quellcode

    1. Where col101 > DateAdd(Hour, -2,GetDate()) and (basetable.id1 < 70 Or (basetable.id1 > 100 and basetable.id1 < 120) Or basetable.id1 > 320)
    2. Order by basetable.id3, basetable.id4, basetable.id5

    sehe ich das richtig das du nur Daten sehen willst die nicht älter als 2 Stunden sind?
    warum dann denn ganzen Datenbestand abfragen?

    überlege immer was ist in der Tabelle(Datenbestand > 10 Jahre? ) und was möchte ich Abfragen.
    @ErfinderDesRades Nein so hab ichs nicht gemacht das war mir nicht klar, dass sich der Code hintendrein auf den Fill-Befehl auswirkt. Ich hab da ne Stopwatch um den Fill-Befehl gebastelt.
    Donnerwetter, jetzt hab ich das DGV abgeknipst und es läuft ruckizucki.
    Kannst du mir erklären wie ich das mit der Extra-Tabelle mache um die Daten anzuzeigen?

    Ja es geht nur um aktuellste Einträge. In der Spalte steht das Erstellungsdatum des Eintrags. Wie würde ich die sonst unterscheiden?
    Also die Nummern spezifizieren ein Produkt. Es ist quasi ein mehrspaltiger Schlüssel. Wenn mans genau nimmt sind die Tabellen an den Nummer nicht wirklich unterscheidbar, sondern an den restlichen Spalten.
    Das Produkt hat zig Eigenschaften, die sind über die Tabellen aufgeteilt.

    Ob das Datenmodell fehlerhaft ist, kann ich nun nicht sagen, das liegt aber auch nicht in meinem Interesse, wenn ich keine Möglichkeit auf bessere Performance habe, dann ist das auch in Ordnung.
    Ok, da hab ich mir gleich gedacht die Spalten müssen nicht autogesized werden, die Tabelle ist sowieso zu groß. Danke

    Jetzt möchte ich gerne wieder auf meinen Ursprung zurück. Ich will ja nur eine Zeile haben, kann ich in eine neue Tabelle eine einzelne Zeile aus der ersten Tabelle extrahieren?

    So hab ich es mir vorgestellt, allerdings ist mir noch nicht ganz klar wie ich mit den Datatable Typen umgehen muss. der Where Befehl meckert weil eine Zahl ja keine Spalte sein kann.
    Den Filter in eine andere Tabelle knüppeln geht natürlich auch nicht. Wahrscheinlich besser das händisch an ein DGV zu knüpfen anstatt an eine vorangelegt Tabelle?

    VB.NET-Quellcode

    1. Dim view = From k In DataSet1.dtbase Where DataSet1.dtbase.col3Column = 123 Select k
    2. DataSet1.dt2 = view


    Das steht auch was von dt2 ist ReadOnly, wo kann ich das einstellen?


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

    Das gibt mir col3 ist kein Member von dtbaseDataTable.

    Das hatte ich schonmal, damals war es eine Frage von Typ und Objekt und ich habe das wohl missverstanden
    und konnte das nicht weiter zusammenkriegen, da ich mich auch mit LINQ noch nicht ganz angefreundet hatte.

    DataSet1 ist ein Objekt, zumindest schließe ich das daraus, dass ich bei

    VB.NET-Quellcode

    1. Dim test as New DataSet1
    gesagt bekomme DataSet1 ist kein Typ
    Das ist das DataSet das ich aufs Form gezogen habe. In dem DataSet befindet sich die Tabelle dtbase, und die Tabelle dt2
    dtbase enthält das abfrage ergebnis der gesamten Daten. dt2 habe ich in dem Dataset neu mit den gleichen Spaltennamen erstellt.

    Haudruferzappeltnoch schrieb:

    DataSet1 ist ein Objekt
    das solltest du genauer bestimmen können: Video-Tut: Welchen Datentyp hat das Objekt?
    Und dann natürlich auch bestimmen, welchen Typ der Ausdruck DataSet1.dtbase hat - wenns das überhaupt gibt.
    Ich nahm natürlich aufgrund deines Codes aus post #12 an, dass es das gebe.

    Andererseits, wenn ich mir dein Sql angucke - dazu kann es eiglich kein typisiertes Dataset geben - und damit wäre ordentliches Linq eh hinfällig - (man könnte höchstens noch was gruseliges basteln).
    Naja wie typisiere ich das Dataset denn noch weiter?
    Kann man denn Dinge bei VS vorgeschlagen bekommen, die es nicht gibt? DataSet1.dtbase zeigt er mir an und dtbase hatte ich die tabelle auch genannt, die ich im Dataset erstellt habe, wär ja komisch wenns die gerade nochmal gibt.

    dtbase, ist ein dtbaseDataTable vom Typ her. Das habe ich als typisiert verstanden im Gegensatz zum Typ DataTable der untypisiert ist.
    jo, scheinbar ist dein DataSet1 ein typDataset und hat eine typTable namens dtbase.
    Aber was ist denn das, was dein Sql abruft?
    Spoiler anzeigen

    SQL-Abfrage

    1. Select basetable.id3, basetable.id4, basetable.col1, basetable.id5, basetable.id1, basetable.id2,
    2. bonustable1.col1, bonustable1.col2, bonustable1.col3, '1' as col4, bonustable1.col5, //21 weitere bonustable1-Spalten//,
    3. bonustable2.col21, Convert(Decimal(10, 1), bonustable2.col22 / bonustable2.col23) as colwert21, Convert(Decimal(10, 1), bonustable2.col4) as col24,
    4. Convert(Decimal(10, 1), bonustable2.col5) as col25, bonustable2.col26, bonustable2.col27, Convert(Decimal(10, 1), bonustable2.col28 / bonustable2.col29) as colwert22,
    5. bonustable2.col210, (CASE When bonustable2.col211 Like 'abc' Then 'mark ' + bonustable2.col211 Else bonustable2.col211 END) as col211, //9 weitere bonustable2-Spalten//
    6. bonustable3.col31, bonustable3.col32, bonustable3.col33, //14 weitere bonustable3-Spalten//,
    7. bonustable4.col41, bonustable4.col42,
    8. bonustable5.col51, bonustable5.col52, Convert(Decimal(10, 1), bonustable5.col53) as col53, Convert(Decimal(10, 1), bonustable5.col54) as col54,
    9. (SELECT cast(t2.col55 as varchar) + '' From bonustable5 t2 Where t2.id1 = basetable.id1 And t2.id2 = basetable.id2 and t2.col55 > 0 Order By t2.id3 asc For Xml path('')) As col5,
    10. bonustable5.col56, bonustable5.col57, //6 weitere bonustable5-Spalten//
    11. bonustable6.col61, bonustable7.col71, bonustable7.col72, bonustable7.col73,
    12. bonustable8.col81, bonustable8.col82, bonustable8.col83, bonustable8.col84, bonustable8.col85,
    13. bonustable9.col91, bonustable9.col92, bonustable9.col93, bonustable9.col94,
    14. bonustable10.col101, bonustable10.col102, bonustable11.col111
    15. (SELECT t2.col1+'# 'FROM bonus12table t2 WHERE (t2.id1=basetable.id1) AND (t2.id2=basetable.id2) AND (t2.id3=basetable.id5) GROUP BY t2.col1, t2.col2, t2.col3 ORDER BY t2.col1, t2.col2 for xml path('') ) as aenderung,
    16. (SELECT bonus12table.coltext from bonus12table where (bonus12table.id1 = basetable.id1 And bonus12table.id2 = basetable.id2 And bonus12table.id3 = basetable.id5 and bonus12table.coltext <> '' and bonus12table.col3 = 500) Group by bonus12table.coltext) as coltext,
    17. From basetable
    18. Left Join bonustable1 On bonustable1.id = basetable.id1
    19. Left Join bonustable2 on bonustable2.id1 = basetable.id1 And bonustable2.id2 = basetable.id2
    20. Left Join bonustable3 on bonustable3.id1 = basetable.id1 And bonustable3.id2 = basetable.id2 And bonustable3.id3 = basetable.id5
    21. Left Join bonustable4 on bonustable4.id1 = basetable.id1 And bonustable4.id2 = basetable.id2 And bonustable4.id3 = basetable.id5
    22. Left Join bonustable5 on bonustable5.id1 = basetable.id1 And bonustable5.id2 = basetable.id2
    23. Left Join bonustable6 on bonustable6.id1 = basetable.id1 And bonustable6.id2 = basetable.id2
    24. Left Join bonustable7 on bonustable7.id1 = basetable.id1 And bonustable7.id2 = basetable.id2
    25. Left Join bonustable8 on bonustable8.id1 = basetable.id1 And bonustable8.id2 = basetable.id2 And bonustable8.id3 = basetable.id5
    26. Left Join bonustable9 on bonustable9.id1 = basetable.id1 And bonustable9.id2 = basetable.id2 And bonustable9.id3 = basetable.id5
    27. Left Join bonustable10 on bonustable10.id = basetable.id3
    28. Left Join bonustable11 on bonustable11.id1 = basetable.id1 And bonustable11.id2 = basetable.id2
    29. Where col101 > DateAdd(Hour, -2,GetDate()) and (basetable.id1 < 70 Or (basetable.id1 > 100 and basetable.id1 < 120) Or basetable.id1 > 320)
    30. Order by basetable.id3, basetable.id4, basetable.id5
    Damit kannst du Dataset1.dtbase doch wohl kaum befüllen, oder?
    Also SqlDataAdapter.Fill(DataSet1.dtbase) meckert zumindest nicht, und ich hab die Daten so wie sie sein sollen in der Tabelle. Die Performance ist jetzt auch wunderbar, wenn man das Verhalten von AutosizeColumnMode kennt ist das super zu händeln.

    Wenn ich den SQL ändere in

    Quellcode

    1. Select testcolumn From testtablewithoneRow
    läufts ja nicht anders, nur das ich Spaltenanzahl in der Tabelle auf 1 reduziere muss.

    Womöglich kann man die Tabelle noch anders befüllen und du willst darauf hinaus?
    nein, ich wundere mich nur bischen:

    SQL-Abfrage

    1. Select basetable.id3, basetable.id4, basetable.col1, basetable.id5, basetable.id1, basetable.id2, bonustable1.col1, bonustable1.col2, bonustable1.col3,...
    Nach meim verständnis kann diese Abfrage nur dann eine typDataTable befüllen, wenn dieselbe auch die entsprechenden Spalten aufweist.
    Hat deine dtbase tatsächlich die Spalten

    Quellcode

    1. id3, id4, col1, id5, id1, id2, col1, col2, col3
    und noch 21 weitere?
    Kann ja garnet sein - zB col1 kommt ja mehrfach vor - zum einen aus basetable.col1, zum andern aus bonustable1.col1.
    Oder ist das durch ieine Hexerei doch möglich?
    Poste dochmal Screenshot von deim typDataset - wie man Bilder in Forum-Posts einfügt ist dir bekannt? sonst
    Aso, ne klar das Schema ist irre, an sich sind die Name der Spalten unübersichtlich, die Abkürzung sind unschön zu lesen, werden also noch umbenannt im Sql selbst, also ​as Namefürcol1. Zur Vereinfachung hab ich das weggelassen so wie ich auch wenn mans genau nimmt noch 50 weitere Spalten weggelassen habe.

    Ich teste die Abzapf und Extraktions-Arbeit, die ich noch vor hab auch mit einem deutlich kleinere Sample. Den "großen" SQL habe ich für MrMo ausformuliert als es noch um die generelle Performance der Abfrage ging, es wäre ja möglich gewesen, dass die Formulierung nicht effizient ist.

    Die Spaltennamen passen aber am Ende überein. Ich mach dir morgen aber gerne noch ein Foto vom Teststand.